Goto main content

select

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"});

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

© 2021 extenso Inc. All rights reserved.