function

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>