bugzy Posted July 23, 2012 Share Posted July 23, 2012 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? Link to comment https://forums.phpfreaks.com/topic/266142-problem-parsing-update-statement/ Share on other sites More sharing options...
Adam Posted July 23, 2012 Share Posted July 23, 2012 The error might be useful? Link to comment https://forums.phpfreaks.com/topic/266142-problem-parsing-update-statement/#findComment-1363858 Share on other sites More sharing options...
bugzy Posted July 23, 2012 Author Share Posted July 23, 2012 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? Link to comment https://forums.phpfreaks.com/topic/266142-problem-parsing-update-statement/#findComment-1363860 Share on other sites More sharing options...
Adam Posted July 23, 2012 Share Posted July 23, 2012 The error might be useful? By that I mean the actual error message PHP gives you. Link to comment https://forums.phpfreaks.com/topic/266142-problem-parsing-update-statement/#findComment-1363861 Share on other sites More sharing options...
bugzy Posted July 23, 2012 Author Share Posted July 23, 2012 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? Link to comment https://forums.phpfreaks.com/topic/266142-problem-parsing-update-statement/#findComment-1363863 Share on other sites More sharing options...
kyle04 Posted July 23, 2012 Share Posted July 23, 2012 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 Link to comment https://forums.phpfreaks.com/topic/266142-problem-parsing-update-statement/#findComment-1363870 Share on other sites More sharing options...
bugzy Posted July 23, 2012 Author Share Posted July 23, 2012 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... Link to comment https://forums.phpfreaks.com/topic/266142-problem-parsing-update-statement/#findComment-1363880 Share on other sites More sharing options...
bugzy Posted July 24, 2012 Author Share Posted July 24, 2012 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? Link to comment https://forums.phpfreaks.com/topic/266142-problem-parsing-update-statement/#findComment-1363898 Share on other sites More sharing options...
Jessica Posted July 24, 2012 Share Posted July 24, 2012 Update item_category SET category_id=1 where item_id IN(166, 167) Link to comment https://forums.phpfreaks.com/topic/266142-problem-parsing-update-statement/#findComment-1363900 Share on other sites More sharing options...
bugzy Posted July 24, 2012 Author Share Posted July 24, 2012 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? Link to comment https://forums.phpfreaks.com/topic/266142-problem-parsing-update-statement/#findComment-1363913 Share on other sites More sharing options...
bugzy Posted July 24, 2012 Author Share Posted July 24, 2012 Solved by myself using rtrim. Thanks guys! Link to comment https://forums.phpfreaks.com/topic/266142-problem-parsing-update-statement/#findComment-1363918 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.