Sql Problem In Yii :: Sqlstate[42000]: Syntax Error Or Access Violation: 1064

Yii 1.1 General Discussion
1

I Wrote a sql code in yii and its giving this error:

CDbCommand failed to execute the SQL statement: 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. The SQL statement executed was: SELECT * FROM users where id in ()

I had no idea why this is happening…

The code is:

	$t = implode(",", $array12);


	echo $t;


	


	


	$sql2='SELECT * FROM users where id in ('. $t. ')';


	//echo $sql2;


	//die();


	$command=$connection->createCommand($sql2);


	$row5=$command->queryAll();


	


	echo "<pre>";


	print_r($row5);


	echo "</pre>";

When I echo the sql using echo $sql2 and die() to see the sql, it gives me this:

SELECT * FROM users where id in (44,45)

Now, I used above sql directly in the the as

$sql2=‘SELECT * FROM users where id in (44,45)’;

$command=$connection->createCommand($sql2);

$row5=$command->queryAll();

and its work perfectly, I do not know what can be the problem

2

Can you please past here complete code.

I think, there is no issue with current code.

Moreover, first check does $array12 is empty or not.

Here is some example of check and fix





if(!empty($array12)){

$t=implode(',',$array12);

$sql="SELECT * FROM users where id in(".$t.")";

$command=Yii::app()->db->createCommand($sql);

$data=$command->queryAll();


if($data){

print_r($data);

}

}else{

echo 'Array12 is empty';

}

Hope that will help you

3

Thanks for your quick reply, array12 is not empty. It is carrying data, please read that, when I print any of this, it works, even the generated query from sql2 I used directly and it works. the problem is not with the array. Still this is a little details of the code, sorry full code is above 1000 lines.

	$array12 = array();


	$test1 = $v['id'];


	$sql="SELECT * FROM share WHERE fileId='$test1'";


	$command=$connection->createCommand($sql);


	$row4=$command->queryAll();





	foreach($row4 as $k=>$v)


	{


		array_push($array12, $v['userId']);


	}


	


	$t = implode(",", $array12);


	echo $t;


	


	


	//$sql2='SELECT * FROM users where id in ('. $t. ')';


	$sql2='SELECT * FROM users where id in (44,45)';


	echo $sql2;


	//die();


	$command=$connection->createCommand($sql2);


	$row5=$command->queryall();


	


	echo "<pre>";


	print_r($row5);


	echo "</pre>";

AND thanks again for the reply.

4

Seems that code is correct.

5

check if

SELECT * FROM users where id in (’. $t. ‘)’ equals with ‘SELECT * FROM users where id in (44,45)’

$test= ‘SELECT * FROM users where id in (’. $t. ‘)’ === ‘SELECT * FROM users where id in (44,45)’;

var_dump ($test);

6

I tried it already, its the same. and when i used the generated sql, it works, but when I am using the original sql with the t variable, does not work. Plz help me and Thanks for your reply.

7

Never Mind friends, I got it. Thanks ALLLLLLLL for your effort.

REALLY REALLY THANKS TO ALL FOR YOUR EFFORT.

8

Do you want to post the fix of your problem?

Maybe is usefull for other members in the future

Thanks :)

9

Sorry for late replies…It works in many ways. I am just giving 2 examples. THANK YOU EVERYONE.

$array12 = array();

$test1 = $v[‘id’];

$sql=“SELECT * FROM share WHERE fileId=’$test1’”;

$command=$connection->createCommand($sql);

$row4=$command->queryAll();

foreach($row4 as $k=>$v)

{

array_push($array12, $v['userId']);

}

$t = implode(",", $array12);

//echo $t;

if(!empty($array12))

{

echo '<p> <img src="images/1.jpg" width="70" height="50" /> </p>';





$t=implode(',',$array12);


$sql="SELECT * FROM users where id in(".$t.")";


$command=Yii::app()->db->createCommand($sql);


$data=$command->queryAll();