select - Selects from the database.
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);
This function is used to retreive elements from tables. This function cas be used as a callback in a loop or as a function.
This function returns an has array with thw following elements:
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"});
{{ include("includes/database.sn") }}
Written by Pierre Laplante and Caroline Laplante, <laplante@sednove.com>
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© 2024 extenso Inc. All rights reserved.