Problem parsing update statement

[bugzy]

When I tried to parse it, the code suddenly is gibing me an error

 

<?php

$query3 = "Update item_category ";

		for($i=0;$i <= $row;$i++)
		{
			$get_id = mysql_result($result2, $i, 'Item');

			if(mysql_result($result2,$i,'Total') == 1)
			{				
				$query3 .= "SET category_id=1 where item_id = {$get_id},";

			}

		}



		$result3 = mysql_query(trim($query3,','),$connection) or die (mysql_error());

?>

 

 

I believe that I'm parsing the update statement the wrong way..

 

Anyone?

[Adam]

The error might be useful?

[bugzy]

The error might be useful?

 

 

From this code

 

<?php for($i=0;$i <= $row;$i++)
		{
			$get_id = mysql_result($result2, $i, 'Item');

			if(mysql_result($result2,$i,'Total') == 1)
			{				
				$query3 = "Update item_category set category_id=1 where item_id = {$get_id}";
				$result3 = mysql_query($query3,$connection) or die (mysql_error());
			}

		} ?>

 

 

 

to this

 

 

 

<?php

$query3 = "Update item_category ";

for($i=0;$i <= $row;$i++)

{

$get_id = mysql_result($result2, $i, 'Item');

if(mysql_result($result2,$i,'Total') == 1)


{

$query3 .= "SET category_id=1 where item_id = {$get_id},";

}


}

$result3 = mysql_query(trim($query3,','),$connection) or die (mysql_error());

?>

 

 

 

I have change only the Update portion inside the for loop because I don't want to put a sql statement inside a loop, and I got an error already...

 

Maybe I'm parsing it the wrong way or I place it on top of the loop which interfere it?

 

 

Anyone?

[Adam]

The error might be useful?

 

By that I mean the actual error message PHP gives you.

[bugzy]

The error might be useful?

 

By that I mean the actual error message PHP gives you.

 

 

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 2 on MySQL result index 17 in C:\wamp\www\runa\admin\delete_category.php on line 91

 

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 2 on MySQL result index 17 in C:\wamp\www\runa\admin\delete_category.php on line 93

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 'SET category_id=1 where item_id = 160' at line 1

 

 

AGain, it only happened when I change the code from my above post.. Maybe I'm putting the first statement on the wrong way?

[kyle04]

 

for($i=0;$i <= $row;$i++)

{

$get_id[$i] = mysql_result($result2, $i, 'Item');

if(mysql_result($result2,$i,'Total') == 1)

{

 

$query3 = mysql_query("Update item_category SET category_id='1' where item_id = '$get_id[$i]'");

 

}// if

}// for

[bugzy]

 

for($i=0;$i <= $row;$i++)

{

$get_id[$i] = mysql_result($result2, $i, 'Item');

if(mysql_result($result2,$i,'Total') == 1)

{

 

$query3 = mysql_query("Update item_category SET category_id='1' where item_id = '$get_id[$i]'");

 

}// if

}// for

 

 

Again, as much as possible I don't want to put any sql query/statement inside a loop...

[bugzy]

Hello guys base on my research seemed like I can't update multiple rows with different condition in one mysql query..

 

 

Any idea guys how can I put this in just one single query?

 

Update item_category

 

SET category_id=1 where item_id = 166

SET category_id=1 where item_id = 167

 

 

I mean is there a work around?

[Jessica]

Update item_category SET category_id=1 where item_id IN(166, 167)

[bugzy]

Update item_category SET category_id=1 where item_id IN(166, 167)

 

Thanks  jesirose. It's working though I'm having problem with ",";

 

Here's the new code

 

<?php

		$query3 = "Update item_category SET category_id=1 where item_id IN (";

		for($i=0;$i <= $row;$i++)
		{
			$get_id = mysql_result($result2, $i, 'Item');

			if(mysql_result($result2,$i,'Total') == 1)
			{				
				$query3 .= "$get_id,";

			}

		}

		$query3 .= ")";

		$result3 = mysql_query(trim($query3,','),$connection);

?>

 

 

It's giving me

 

Update item_category SET category_id=1 where item_id IN (177,178,179,)

 

 

How would I able to remove the comma on the last item_id?

[bugzy]

Solved by myself using rtrim. Thanks guys!