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"});
SEE ALSO
{{ include("includes/database.sn") }}
AUTHOR
Written by Pierre Laplante and Caroline Laplante, <laplante@sednove.com>
MODIFICATIONS
1.0 2014-09-09 21:24:14 laplante@sednove.com
1.1 2014-11-05 09:56:32 laplante@sednove.com Add single
Edit
