select . database . sncode
VERSIONlatest
Select from the database.
SELECT
NAME
select - Selects from the database..
SYNOPSIS
select(( [db:pointer,] tables:string, wantarray:true|false, single:boolean,count:integer, fields:string,error:variable, join:string, having:string, selectby:string, orderby:string, groupby:string, limit:string, args:string, uid:integer, page:integer, prefix:true|false, countperpage:integer);
DESCRIPTION
This function is used to retreive elements from tables. This function cas be used as a callback in a loop or as a function.
PARAMETERS
- tables
- Specifies list of tables commads separated.
- error
- Puts errors in this variable. This is useful in a loop to check if an error occur after the loop in the SQL statement.
- single
- Returns a single row even if they are multiple results. The rows which is usually an array is a single context variable when single is true.
- wantarray
- By default, rows are returned as a context with the name of the column. If this parameter is true, it returns only the value.
- count
- If the parameter page is used, then the function must calculate the number of element. If you already have this number, you can use this parameter to initialize it.
- fields
- Specifies the list of fields commas separated.
- join
- Specifies join statement for select.
- having
- Specifies having statement for select.
- selectby
- Specifies condition for the WHERE statement. See args \? is replase with ?.
- noargs
- By default ? in selectby are replaced by values in args. If noargs is true, no replacement is done.
- args
- Specifies arguments wihin selecby to quote and replace value. All ? are replace with one arguments.
- orderby
- Specifies ORDER BY statement.
- groupby
- Specifies GROUP BY statement.
- limit
- Specifies LIMIT statement.
- uid
- Specifies UID for the WHERE statement.
- page
- Specifies page number based on count and countperpage.
- prefix
- Prefix name of field with table name followed by _.
- countperpage
- Number of elements in a page.
- db
- Specifies alternate database pointer as returned from connect function
- Starting from version 5.6.2 if db is undefined, it will fall back to the default database of the project.
RETURN
This function returns an has array with thw following elements:
- sql
- Build SQL statement.
- nbcols
- Number of columns in result.
- offset
- Offset calculated when page is specify. If not, the value is -1.
- sqlerr
- Error message if any.
- count
- Calculated count (or the one specify in parameter) if page is specify. -1 otherwise.
- error
- Specifies if we got an error or not
- sqlcode
- SQL error code. 0 if no error.
- nbrows
- Number of rows in result
- rows
- List if rows. The rows can be an array of hash or an array of array if wantarray is true.
EXAMPLES
Note: In the followings examples, the _ between the { should be removed to make it work.
res={_{ a=sql("drop table IF EXISTS ? ;","sn_test1"); a=sql("create table sn_test1 (uid int, firstname varchar(255), lastname varchar(255"); for(i=1;i<30;++i) do j = i % 5; k = i % 3; a=sql("insert into sn_test1 values('?', '?', '?');",i, "f" .+ j,"l" .+ k ); endfor a=select(tables:"sn_test1",groupby:"firstname",fields:"firstname,count(*) as c", having:"count(*) = 5"); a.rows; }}. return res=\[{"c":"5","firstname":"f0"}\].res={_{ a=sql("drop table IF EXISTS ? ;","sn_test1"); a=sql("drop table IF EXISTS ? ;","sn_test2"); a=sql("create table sn_test1 (uid int, firstname varchar(255), lastname varchar(255"); a=sql("create table sn_test2 (uid int, uid_sn_test_1 int, firstname varchar(255), lastname varchar(255))"); for(i=1;i<3;++i) do a=sql("insert into sn_test1 values('?', '?', '?');",i, "f" .+ i,"l" .+ i ); for(j=1;j<3;++j) do a=sql("insert into sn_test2 values('?', '?', '?', '?');",i,j,"g" .+ i .+ j,"h" .+ i .+ j); endfor endfor }}. return res=.res={_{ select(tables:"sn_test1",page:5,limit:"abc"); }}. return .*Page and limit are mutually exclusive.*res={_{ select(tables:"sn_test1",selectby:"uid = ?"); }}. return .*Missing args parameter.*res={_{ a=select(tables:"sn_test1",selectby:"uid = '?'",noargs:true); a.nbrows}}. return res=0.res={_{ a=select(tables:"sn_test1",prefix:true,fields:"uid"); a.rows[0]}}. return res={"sn_test1_uid":"1"}.res={_{ select(); }}. return .*tables must be specify.*res={_{ sql("select * from sbsusj"); }}. return .*Table 'v5.sbsusj' doesn't exist.*res={_{ select(tables:"sbsusj"); }}. return .*Table 'v5.sbsusj' doesn't exist.*res={_{ a=select(tables:"sbsusj"); a.error; a.sqlcode;}}. return res=true1.res={_{ a=select(wantarray:true,tables:"sn_test1"); if ! a.error then a.rows; endif}}. return res=\[\["1","f1","l1"\],\["2","f2","l2"\]\].res={_{ a=select(wantarray:true,tables:"sn_test1,sn_test2"); if ! a.error then a.nbrows; a.rows[0]; endif}}. return res=8\["1","f1","l1","1","1","g11","h11"\].res={_{ a=select(tables:"sn_test1,sn_test2",selectby:"uid = '?'"); if ! a.error then a.nbrows; a.rows[0]; endif}}. return .*Missing args parameters.*res={_{ a=select(wantarray:true,tables:"sn_test1,sn_test2",selectby:"sn_test1.uid = '?'",args:[1]); if ! a.error then a.nbrows; a.rows[0]; endif}}.], q(res=4\["1","f1","l1","1","1","g11","h11"\].res={_{ a=select(wantarray:true,tables:"sn_test1,sn_test2",selectby:"sn_test1.uid = '?' and sn_test2.uid = '?'",args:[1,2]); if ! a.error then a.nbrows; a.rows[0]; endif}}.], q(res=2\["1","f1","l1","2","1","g21","h21"\].res={_{ a=select(wantarray:true,tables:"sn_test1,sn_test2",uid:1); if ! a.error then a.nbrows; a.rows[0]; endif}}.], q(res=4\["1","f1","l1","1","1","g11","h11"\].res={_{ a=select(wantarray:true,tables:"sn_test1,sn_test2",uid:1,selectby:"sn_test2.uid = '?'", args:[1]); if ! a.error then a.nbrows; a.rows[0]; endif}}.], q(res=2\["1","f1","l1","1","1","g11","h11"\].res={_{ a=select(wantarray:true,tables:"sn_test1",uid:1); if ! a.error then a.nbrows; a.rows[0]; endif}}.], q(res=1\["1","f1","l1"\].res={_{ a=select(wantarray:true,fields:"firstname,lastname",tables:"sn_test1",uid:1); if ! a.error then a.nbrows; a.rows[0]; endif}}.], q(res=1\["f1","l1"\].res={_{ a=select(wantarray:true,tables:"sn_test1",uid:1,join:"LEFT JOIN sn_test2 on sn_test1.uid = sn_test2.uid_sn_test_1"); if ! a.error then a.nbrows; a.rows[0]; else a.sqlerr; endif}}.], q(res=2\["1","f1","l1","1","1","g11","h11"\].res={_{ a=select(wantarray:true,tables:"sn_test1",fields:"uid",orderby:"uid asc"); if ! a.error then a.nbrows; a.rows; else a.sqlerr; endif}}.], q(res=2\[\["1"\],\["2"\]\].res={_{ a=select(wantarray:true,tables:"sn_test1",fields:"uid",orderby:"uid desc"); if ! a.error then a.nbrows; a.rows; else a.sqlerr; endif}}.], q(res=2\[\["2"\],\["1"\]\].res={_{ a=select(tables:"sn_test1",fields:"count(uid) as c",orderby:"uid desc"); if ! a.error then a.nbrows; a.rows; else a.sqlerr; endif}}.], q(res=1\[{"c":"2"}\].res={_{ a=select(page:1,tables:"sn_test1",fields:"count(uid) as c",orderby:"uid desc"); if ! a.error then a.nbrows; a.rows; else a.sqlerr; endif}}.], q(res=1\[{"c":"2"}\].res={_{ a=sql("drop table IF EXISTS ? ;","sn_test1"); a=sql("create table sn_test1 (uid int, firstname varchar(255), lastname varchar(255"); for(i=1;i<30;++i) do a=sql("insert into sn_test1 values('?', '?', '?');",i, "f" .+ i % 5,"l" .+ i ); endfor a=select(wantarray:true,page:2,tables:"sn_test1",fields:"uid",countperpage:4,orderby:"uid asc"); a.rows; a=select(wantarray:true,count:29,page:2,tables:"sn_test1",fields:"uid",countperpage:4,orderby:"uid asc"); a.rows; }}. return res=\[\["5"\],\["6"\],\["7"\],\["8"\]\]\[\["5"\],\["6"\],\["7"\],\["8"\]\].res={_{ for i select(error:t,wantarray:true,page:2,tables:"ioijswjj",fields:"uid",countperpage:4,orderby:"uid asc") do i; endfor t.errmsg; }}. return res=.*Table 'v5.ioijswjj' doesn't exist.*res={_{ for i select(error:t,wantarray:true,page:2,tables:"sn_test1",fields:"uid",countperpage:4,orderby:"uid asc") do i.rows[0]; endfor t.errmsg; }}. return res=5678.res={_{ for i select(error:t,page:1,tables:"sn_test1",fields:"uid",countperpage:4,orderby:"uid asc") do i.rows; if i.rows.uid == 2 then break endif endfor t.errmsg; }}. return res={"uid":"1"}{"uid":"2"}.res={_{ a=sql("drop table IF EXISTS ? ;","sn_test1"); a=sql("drop table IF EXISTS ? ;","sn_test2"); a=sql("create table sn_test1 (uid int, firstname varchar(255), lastname varchar(255"); a=sql("create table sn_test2 (uid int, uid_sn_test_1 int, firstname varchar(255), lastname varchar(255))"); }}. return res=.res={_{ for(i=1;i<10;++i) do a=sql("insert into sn_test1 values('?', '?', '?');",i, "f1","l1"); for(j=1;j<10;++j) do a=sql("insert into sn_test2 values('?', '?', '?', '?');",i,j,"f11","l11"); endfor endfor a=sql(prefix:true,"select * from sn_test1,sn_test2 where sn_test1.uid = sn_test2.uid_sn_test_1"); a.nbrows; a=sql("drop table IF EXISTS ? ;","sn_test1"); a=sql("drop table IF EXISTS ? ;","sn_test2"); }}. return res=81. fait(q(res={_{a=select(page:1,tables:"wijdwoij");a;}}. return .*able 'v5.wijdwoij' doesn't exist.*res={_{ a=sql("drop table IF EXISTS ? ;","sn_test2"); a=sql("create table sn_test2 (uid int NOT NULL AUTO_INCREMENT, sn_cdate DATETIME, sn_mdate DATETIME,sn_cuid INTEGER,sn_muid INTEGER, firstname VARCHAR(255), lastname VARCHAR(255), email VARCHAR(255), PRIMARY KEY (uid));"); arr = [ "Pierre", "Laplante"]; a=sql("insert into sn_test2 set firstname = '?', lastname='?', email='?';", arr, "laplante@sednove.com" ); a=select(single:true,tables:"sn_test2"); a.rows.email; a.rows.firstname; a=select(single:true,wantarray:true,tables:"sn_test2"); a.rows;}}.) return res=laplante@sednove.comPierre\["1",null,null,null,null,"Pierre","Laplante","laplante@sednove.com"\].),{'asm' => "-a"});AUTHOR
Written by Pierre Laplante and Caroline Laplante, <laplante@sednove.com>