August 25, 2013

MYSQL IN Clause error

NewPHP’s Question:

I have used the below code in mysql query:

$all_PIDs=array();
foreach($pID as $p)
{
    $all_PIDs[]=$p->ID;
}
$AIDS=implode(',',$all_PIDs);
$table_tsk  = new Timesheets_Table_Tasks();
$select_tsk = $table_tsk->select()
            ->from($table_tsk, array
              (
                'Total'=>'SUM(timesheets_tasks.Time)',
                'Charged'=>'SUM(timesheets_tasks.Time_Charged)'
              ))
           ->where('timesheets_tasks.ProjectID IN ('.$AIDS.')')
            ;

But using the above code I am getting the following error:

“An error has occured
SQLSTATE[42000]: Syntax error or access violation: 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 ‘))’ at line 1”

I have added a quotation mark(") for IN clause. But the problem is the query only displays for the first $AIDS number. Could someone help me to clear the error?
Thanks!

It should be specified as:

->where('timesheets_tasks.ProjectID IN (?)', $all_PIDs)

so you’re passing an array of integers, not the comma-separated list of it

On your codes the quotes are not part of your MySQL query but only your PHP portion. DO this

$AIDS= "'".implode("','",$all_PIDs)."'";

And then

>where('timesheets_tasks.ProjectID IN ('.$AIDS.')'

Author: Nabin Nepal (Starx)

Hello, I am Nabin Nepal and you can call me Starx. This is my blog where write about my life and my involvements. I am a Software Developer, A Cyclist and a Realist. I hope you will find my blog interesting. Follow me on Google+

...

Please fill the form - I will response as fast as I can!