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>