Error in SQL query creation when using query() and Mysql

fabioberetta

Description of problem:
I am trying to upgrade to Editor 1.9 but I get an error when executing php script to collect data.

to collect options I use this

$attrList = $db->query('select', 'child')
                    ->get('child.id, child.first_name, child.last_name')
                    ->where('child.account_id', $_SESSION['account_id'] )
                    ->where('child.child_id', null)
                    ->order('child.last_name')
                    ->exec();

Now it returns an error since it does not find the proper column naming.

I have inspected the query that is built and the result.
The result is:

Array
(
    [id] => 2
    [first_name] => Eden
    [child.id, child.first_name, child.last_name] => ACCARIAS
)

And the query is this one:

[queryString] => SELECT  child.id, child.first_name, child.last_name as 'child.id, child.first_name, child.last_name' FROM  `child` WHERE `child`.`account_id` = :where_0 AND  `child`.`child_id` IS NULL  ORDER BY `child`.`last_name` 

The "AS" portion to rename the column syntax in Mysql is not supposed to work in this way. How can I fix it?

Thanks F.

rf1234

I would recommend to use an options instance to collect the options. The syntax seems to have changed significantly.

https://editor.datatables.net/manual/php/joins#Options

You'll find plenty of examples using the options instance as well.

Here for example looking at the server script:
https://editor.datatables.net/examples/advanced/joinArray.html

fabioberetta

Actually I have used this implementation since I have to add an extra option (the no-selection/empty option) and I did not find out how I could do it with options instance.

$attrList = $db->query('select', 'child')
                    ->get('child.id, child.first_name, child.last_name')
                    ->where('child.account_id', $_SESSION['account_id'] )
                    ->where('child.child_id', null)
                    ->order('child.last_name')
                    ->exec();

                $out = array();

                // Sets the first line to null
                $out[] = array(
                    "value" => '-1',
                    "label" => 'Aucun frére/seur.'
                );

                while ( $row = $attrList->fetch() ) {
                    $out[] = array(
                        "value" => $row['child.id'],
                        "label" => $row['child.last_name'] . ' ' . $row['child.first_name']
                    );
                }
                return $out;

Any suggestion?

fabioberetta

Actually the php syntax of the database class remained pretty much the same.

It seems that now it is just generating in this case the wrong SQL syntax with respect to the "AS" statement that overrides the column names.

rf1234

Looking at the documentation https://editor.datatables.net/docs/current/php/class-DataTables.Database.Query.html#_get

I found this:

Maybe you want to try with an array?

If that doesn't work you can also use the "raw" method which I use frequently myself. Apart from using Editor it gives you the most flexibility I think.

$statement = ('SELECT number FROM report_type  
                WHERE user_id = :user_id');  
$result =
$db ->raw()
    ->bind(':user_id', $userId)
    ->exec($statement);

$row = $result->fetchAll(PDO::FETCH_ASSOC);

foreach ($row as $key => $values) {
    ... do something
}

fabioberetta

Thanks,

I have redone the code using the raw() function. I have not tested the get() with an array since I believe that the result would be the same anyhow.

It is generating a different SQL dialect so the issue is in the routing that generates the SQL. Hope it will be fixed since I do not like to put directly SQL in the php code.

Thanks again.