$editor->db() WHERE array

obrienj

Allan,

Could you show me what the

'$editor->db()->select(....

statement would look like for the following 'select' statement.

SELECT * FROM mydb.mytbl WHERE flda = 'abcd' AND fldb <= 2000;

From what doc I can find, I can't figure out the structure of each element in the 'where' array.

Regards,
Jim

TooManyTables

Hey Jim,

Assuming you're using PHP, and the code you're interested in is intended to form the server-side component of an Editor instance, have you read through the Getting Started page? That, and the companion page on WHERE conditions are pretty much essential to understand if you're going to be working with Editor.

Once you've done so, the following example code should be fairly self-explanatory:

Editor::inst($db, 'mytbl', 'mytbl.fldprimarykey' )
        ->field(
            Field::inst( 'mytbl.flda' ),
            Field::inst( 'mytbl.fldb' ),
            Field::inst( 'mytbl.fldc' ) //And so on, for all of the fields
        )
    ->process($_POST)
     ->where(function($q) {
        $q->where('mytbl.flda', 'abcd', '=');
        $q->where('mytbl.fldb', 2000, '<=');
    })
    ->json();

allan

There is a slight deviation here - I think Jim is looking to run a select statement and get its results directly, rather than running it through the Editor instance. If it were to get data using the Editor class then @TooManyTables answer is spot on.

However, if we combine your two pieces of code together we can get:

$editor->db()->select( $table, $fields, function($q) {
        $q->where('mytbl.flda', 'abcd', '=');
        $q->where('mytbl.fldb', 2000, '<=');
} );

i.e. the where can be a function.

Allan

obrienj

Allan,

I don't want to be picky, but what would the would the WHERE array look like without the function($q)?

Might it be?:

$editor->db()->select( mydb.mytbl, '*',  {flda', 'abcd', '=',  fldb', 2000, '<='} );                                              

Regards,
Jim

obrienj

Allan,

I took you example and expanded it to use my column name and variables and it looks like this:

                    $countdata = $editor->db()
                        ->select('calendar.events', 'COUNT(*)', function($q) {
                                                                            $q->where('rfcid', $rowdata['rfcid'], '=' );
                                                                            $q->where('start', $testtime,         '<=');
                                                                             })

                        ->fetchAll();

Both $rowdata and $testtime show up with the error "Use of unassigned variable", but they are assigned in the function in which the statement is.

In other ->select statements I use:

->select( 'calendar.events', 'COUNT(*)', array( 'rfcid' => $origrow['rfcid'] ) )

Is there a way to build the ->select like this for multiple conditions and with different operators for the conditions?

I tried

$result = $editor->db()->raw()->exec("SELECT COUNT(*)  FROM .... WHERE cond-1 AND cond-2

but I couldn't "decode" $result even though the doc says it is a "result" class. With print_r it simple showed "1" (without quotes) which is not the correct value and if I changed the SELECT to get all columns instead of the count, the result was still "1"

I know this must be my limited knowledge of PHP, but how do I get the count of rows that satisfy the stated conditions, which is all I want to do.

Regards,
Jim

allan

what would the would the WHERE array look like without the function($q)?

If you want to use an operator other than =, there is no option to use an array. You have to use the closure function.

Both $rowdata and $testtime show up with the error "Use of unassigned variable", but they are assigned in the function in which the statement is.

See this section of the manual. That's how PHP closure functions work - you need to use use statements.

Allan

obrienj

Ignore, this post is in error