PDO driver problem with "table_name"

[zaherg]

hi, am using the new 3.0-dev version, i have enabled the pdo in my database connection .. am using ion_auth library and when i try to do this :

$this->ion_auth->user()->row();

i got this error :

Error Number: 42000/1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.*, "users"."id" as id, "users"."id" as user_id FROM "users" WHERE "users"."id" ' at line 1

SELECT "users".*, "users"."id" as id, "users"."id" as user_id FROM "users" WHERE "users"."id" = '5'

Filename: D:\xampp\htdocs\project\content\system\database\DB_driver.php

Line Number: 366

and when i try to login :

$this->ion_auth->logged_in();

i got this error

A Database Error Occurred

Error Number: 42000/1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"users" WHERE (email = 'soso@soso.com') LIMIT 1' at line 2

SELECT "email", "username", "email", "id", "password", "active", "last_login" FROM "users" WHERE (email = 'soso@soso.com') LIMIT 1

Filename: D:\xampp\htdocs\project\content\system\database\DB_driver.php

Line Number: 366

after some testing i have noticed this that this :

SELECT "users".*, "users"."id" as id, "users"."id" as user_id FROM "users" WHERE "users"."id" = '5'

will not work, but this :

SELECT `users`.*, `users`.`id` as id, `users`.`id` as user_id FROM `users` WHERE `users`.`id` = '5'

and this will not work :

SELECT "email", "username", "email", "id", "password", "active", "last_login" FROM "users" WHERE (email = 'soso@soso.com') LIMIT 1

but this will do work ( notice the change in "users" )

SELECT "email", "username", "email", "id", "password", "active", "last_login" FROM `users` WHERE (email = 'soso@soso.com') LIMIT 1

[narfbg]

The last example is indeed a valid query, but it won't actually work as it would return the field names literally as their values.

Otherwise - when did you checkout the code? That issue must've been fixed a couple of months ago.

[zaherg]

i have downloaded the code couple days ago 21/05 ..

[zaherg]

i have just downloaded the new code ( today ), but its look like there is a loader problem with MX since i got this

Message: Declaration of MX_Lang::load() should be compatible with that of CI_Lang::load()

so am checking it to see whats wrong .. i have just opened an issue with it http://s.zah.me/Kcxwfl

[zaherg]

i have downloaded the new code ( 24/05/2012) and i have confirm that this issue is not resolved yet ..

[narfbg]

Well, I can't see how it could be a CI issue. It's most likely one in that ion_auth library that you're using. Could you check on that?

P.S.: GitHub has pretty friendly URLs and adding a link to an issue is as easy as prefixing it's number with '#'. Please don't use URL shorteners. :)

[zaherg]

i dont think its something related to the ion_auth, since its working fine with mysql and mysqli and right now am using mysqli and its working fine ..

the function which used to return the last select statement is :

<?php
    public function user($id = NULL)
    {
        $this->trigger_events('user');

        //if no id was passed use the current users id
        $id || $id = $this->session->userdata('user_id');

        $this->limit(1);
        $this->where($this->tables['users'].'.id', $id);

        $this->users();

        return $this;
    }

which as you can use is a normal AR statement ..

PS : about the url , i will do it as you like :D

[narfbg]

This code only shows us the WHERE and LIMIT clauses. The possible issue is in either where SELECT and FROM are or that the PDO driver's constructor isn't executed. Most likely the second one.

[benedmunds]

Hey, I'll be glad to help clarify anything with Ion Auth. Here are the two methods that are executed with this:

public function users($groups = NULL)
{
    $this->trigger_events('users');

    //default selects
    $this->db->select(array(
        $this->tables['users'].'.*', 
        $this->tables['users'].'.id as id', 
        $this->tables['users'].'.id as user_id'
    ));

    if (isset($this->_ion_select))
    {
        foreach ($this->_ion_select as $select)
        {
            $this->db->select($select);
        }

        $this->_ion_select = array();
    }

    //filter by group id(s) if passed
    if (isset($groups))
    {
        //build an array if only one group was passed
        if (is_numeric($groups))
        {
            $groups = Array($groups);
        }

        //join and then run a where_in against the group ids
        if (isset($groups) && !empty($groups))
        {
            $this->db->join(
                $this->tables['users_groups'], 
                $this->tables['users_groups'].'.user_id = ' . $this->tables['users'].'.id', 
                'inner'
            );

            $this->db->where_in($this->tables['users_groups'].'.group_id', $groups);
        }
    }

    $this->trigger_events('extra_where');

    //run each where that was passed
    if (isset($this->_ion_where))
    {
        foreach ($this->_ion_where as $where)
        {
            $this->db->where($where);
        }

        $this->_ion_where = array();
    }

    if (isset($this->_ion_limit) && isset($this->_ion_offset))
    {
        $this->db->limit($this->_ion_limit, $this->_ion_offset);

        $this->_ion_limit  = NULL;
        $this->_ion_offset = NULL;
    }
    else if (isset($this->_ion_limit)) 
    {
        $this->db->limit($this->_ion_limit);

        $this->_ion_limit  = NULL;
    }

    //set the order
    if (isset($this->_ion_order_by) && isset($this->_ion_order))
    {
        $this->db->order_by($this->_ion_order_by, $this->_ion_order);

        $this->_ion_order    = NULL;
        $this->_ion_order_by = NULL;
    }

    $this->response = $this->db->get($this->tables['users']);

    return $this;
}

/**
 * user
 *
 * @return object
 * @author Ben Edmunds
 **/
public function user($id = NULL)
{
    $this->trigger_events('user');

    //if no id was passed use the current users id
    $id || $id = $this->session->userdata('user_id');

    $this->limit(1);
    $this->where($this->tables['users'].'.id', $id);

    $this->users();

    return $this;
}

[narfbg]

OK, this is getting us nowhere ... where can I download this library from?

[benedmunds]

The library is here: https://github.com/benedmunds/CodeIgniter-Ion-Auth

[narfbg]

On a clean CodeIgniter installation, with MySQL via PDO:

$this->load->library('ion_auth');
$this->ion_auth->user()->row();

... this is the query that Ion_Auth generates for me:

SELECT `users`.*, `users`.`id` as id, `users`.`id` as user_id FROM `users` WHERE `users`.`id` = 0 LIMIT 1

@linuxjuggler You've probably put the downloaded code in the wrong place.

[zaherg]

@narfbg am using CI with MX and the library is inside the libraries directory inside the main app folder .. so i dont think there is something wrong with my installation .. am on windows xampp ..

any way will check it again ..

[narfbg]

I don't know what MX is, but at least _system/database/drivers/pdo/pdodriver.php is not the latest version.

[zaherg]

MX is the modules library .. and yes pdo_driver.php is the latest version since i have deleted the system directory and copied the new one ..

any way i will make more research and get back to you ..