drisate Posted April 19, 2008 Share Posted April 19, 2008 Hey guys i was wandering if there was a way to put all this into 1 query $update = mysql_query("UPDATE quests SET ReqItemId1='$last' where ReqItemId1='$items[entry]'") or die (mysql_error()); $update = mysql_query("UPDATE quests SET ReqItemId2='$last' where ReqItemId2='$items[entry]'") or die (mysql_error()); $update = mysql_query("UPDATE quests SET ReqItemId3='$last' where ReqItemId3='$items[entry]'") or die (mysql_error()); $update = mysql_query("UPDATE quests SET ReqItemId4='$last' where ReqItemId4='$items[entry]'") or die (mysql_error()); $update = mysql_query("UPDATE quests SET RewChoiceItemId1='$last' where RewChoiceItemId1='$items[entry]'") or die (mysql_error()); $update = mysql_query("UPDATE quests SET RewChoiceItemId2='$last' where RewChoiceItemId2='$items[entry]'") or die (mysql_error()); $update = mysql_query("UPDATE quests SET RewChoiceItemId3='$last' where RewChoiceItemId3='$items[entry]'") or die (mysql_error()); $update = mysql_query("UPDATE quests SET RewChoiceItemId4='$last' where RewChoiceItemId4='$items[entry]'") or die (mysql_error()); $update = mysql_query("UPDATE quests SET RewChoiceItemId5='$last' where RewChoiceItemId5='$items[entry]'") or die (mysql_error()); $update = mysql_query("UPDATE quests SET RewChoiceItemId6='$last' where RewChoiceItemId6='$items[entry]'") or die (mysql_error()); $update = mysql_query("UPDATE quests SET RewItemId1='$last' where RewItemId1='$items[entry]'") or die (mysql_error()); $update = mysql_query("UPDATE quests SET RewItemId2='$last' where RewItemId2='$items[entry]'") or die (mysql_error()); $update = mysql_query("UPDATE quests SET RewItemId3='$last' where RewItemId3='$items[entry]'") or die (mysql_error()); $update = mysql_query("UPDATE quests SET RewItemId4='$last' where RewItemId4='$items[entry]'") or die (mysql_error()); Quote Link to comment https://forums.phpfreaks.com/topic/101903-all-in-one-query/ Share on other sites More sharing options...
laffin Posted April 19, 2008 Share Posted April 19, 2008 UPDATE tablename (field1,field2,field3) VALUES ('value1','value2','value3') WHERE Condition wud take too long to rewrite all those queries, but if u take the example above, shud help u make the query. Quote Link to comment https://forums.phpfreaks.com/topic/101903-all-in-one-query/#findComment-521519 Share on other sites More sharing options...
BlueSkyIS Posted April 19, 2008 Share Posted April 19, 2008 Hey guys i was wandering if there was a way to put all this into 1 query no. Quote Link to comment https://forums.phpfreaks.com/topic/101903-all-in-one-query/#findComment-521520 Share on other sites More sharing options...
Barand Posted April 19, 2008 Share Posted April 19, 2008 If you don't normalise your data then you get to do things the hard way. @laffin, that's the wrong syntax for an update query. Quote Link to comment https://forums.phpfreaks.com/topic/101903-all-in-one-query/#findComment-521522 Share on other sites More sharing options...
drisate Posted April 19, 2008 Author Share Posted April 19, 2008 I tryed this $update = mysql_query("UPDATE quests SET (ReqItemId1='$last' where ReqItemId1='$items[entry]') and (ReqItemId2='$last' where ReqItemId2='$items[entry]') and (ReqItemId3='$last' where ReqItemId3='$items[entry]') and (ReqItemId4='$last' where ReqItemId4='$items[entry]')and (RewChoiceItemId1='$last' where RewChoiceItemId1='$items[entry]')and (RewChoiceItemId2='$last' where RewChoiceItemId2='$items[entry]')and (RewChoiceItemId3='$last' where RewChoiceItemId3='$items[entry]')and (RewChoiceItemId4='$last' where RewChoiceItemId4='$items[entry]')and (RewChoiceItemId5='$last' where RewChoiceItemId5='$items[entry]')and (RewChoiceItemId6='$last' where RewChoiceItemId6='$items[entry]')and (RewItemId1='$last' where RewItemId1='$items[entry]')and (RewItemId2='$last' where RewItemId2='$items[entry]')and (RewItemId3='$last' where RewItemId3='$items[entry]')and (RewItemId4='$last' where RewItemId4='$items[entry]')") or die (mysql_error()); But obviously it's not working lol UPDATE tablename (field1,field2,field3) VALUES ('value1','value2','value3') WHERE Condition I don't think that will work because i only update the field if the value is equal to $items[entry] (are you sur that works anyway lol) no IDK ... php always has a way trick up his sleeves for this kind of situation. Any sugestions? Quote Link to comment https://forums.phpfreaks.com/topic/101903-all-in-one-query/#findComment-521524 Share on other sites More sharing options...
pocobueno1388 Posted April 19, 2008 Share Posted April 19, 2008 UPDATE quests SET ReqItemId1='$last', ReqItemId2='$last', ReqItemId3='$last' WHERE ReqItemId1='{$items['entry']}' Just follow that pattern. Quote Link to comment https://forums.phpfreaks.com/topic/101903-all-in-one-query/#findComment-521528 Share on other sites More sharing options...
drisate Posted April 19, 2008 Author Share Posted April 19, 2008 I can't because every row can have diffrent values ... i need to test each field to check if the value is equal to $items[entry]... Thats why i ended up by trying stuff like $update = mysql_query("UPDATE quests SET (ReqItemId1='$last' where ReqItemId1='$items[entry]'), (ReqItemId2='$last' where ReqItemId2='$items[entry]'), (ReqItemId3='$last' where ReqItemId3='$items[entry]'), (ReqItemId4='$last' where ReqItemId4='$items[entry]'), (RewChoiceItemId1='$last' where RewChoiceItemId1='$items[entry]'), (RewChoiceItemId2='$last' where RewChoiceItemId2='$items[entry]'), (RewChoiceItemId3='$last' where RewChoiceItemId3='$items[entry]'), (RewChoiceItemId4='$last' where RewChoiceItemId4='$items[entry]'), (RewChoiceItemId5='$last' where RewChoiceItemId5='$items[entry]'), (RewChoiceItemId6='$last' where RewChoiceItemId6='$items[entry]'), (RewItemId1='$last' where RewItemId1='$items[entry]'), (RewItemId2='$last' where RewItemId2='$items[entry]'), (RewItemId3='$last' where RewItemId3='$items[entry]'), (RewItemId4='$last' where RewItemId4='$items[entry]')") or die (mysql_error()); 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 '(ReqItemId1='190000' where ReqItemId1='190000'), (ReqItemId2='190000' where Re' at line 2 They all need to be tested like this some way ... RewItemId1='$last' where RewItemId1='$items[entry]' So they are only replaced by the new value if the current value is = to $items[entry] Quote Link to comment https://forums.phpfreaks.com/topic/101903-all-in-one-query/#findComment-521536 Share on other sites More sharing options...
Psycho Posted April 19, 2008 Share Posted April 19, 2008 As Barand stated, the fact that you are not normalising your data is the issue. When there are many-to-one associations you shoud have a secondary table. I.e. there shoudl be a "ReqItem" table with the records haing an id tying them to the record in the "quests" table. However, assuming you still want to do things the wrong way, you could at least create a loop to do the queries for you. <?php for ($i=1; $i<=6; $i++) { if ($i <=4) { $query = "UPDATE quests SET ReqItemId{$i}='$last' where ReqItemId{$i}='$items[entry]'" mysql_query($query) or die (mysql_error()."<br />on query:<br >$query"); $query = "UPDATE quests SET RewItemId{$i}='$last' where RewItemId{$i}='$items[entry]'" mysql_query($query) or die (mysql_error()."<br />on query:<br >$query"); } $query = "UPDATE quests SET RewChoiceItemId{$i}='$last' where RewChoiceItemId{$i}='$items[entry]'" mysql_query($query) or die (mysql_error()."<br />on query:<br >$query"); } ?> Quote Link to comment https://forums.phpfreaks.com/topic/101903-all-in-one-query/#findComment-521537 Share on other sites More sharing options...
drisate Posted April 19, 2008 Author Share Posted April 19, 2008 Yeah I understand what you’re telling me but there’s not much I can do about it. It's a world of warcraft database and the quest table can have 4 different required items, 6 different reward items to choose from or 4 reward items gave at the same time. I am remaking the item id's because custom items currently have a number with almost 7 numbers when it can actually have 6 and having them all one after an other would greatly help. I will do it this way then ... But i am facing an other problem ... the page times out before the script is done making the changes. <?php /** * @author Beaster * @Script Remake items id's */ $dbh2=mysql_connect("f-fr.servegame.com", "user", "pass") or die ('1 Erreur: '.mysql_error()); mysql_select_db("ascent"); $start='190000'; // Evrything above this will get new id's $loop=1; echo "<center><h1>Starting</h1></center><br><br>"; $check = mysql_query("SELECT * FROM `items` WHERE entry>='$start' order by entry asc") or die (mysql_error()); while ($items = mysql_fetch_array($check)) { if ($loop=="1"){$last=$start;}else{$last++;} //$update = mysql_query("UPDATE items SET old_entry='$items[entry]' where entry='$items[entry]'") or die (mysql_error()); $update = mysql_query("UPDATE items SET entry='$last' where entry='$items[entry]'") or die (mysql_error()); $update = mysql_query("UPDATE playeritems SET entry='$last' where entry='$items[entry]'") or die (mysql_error()); $update = mysql_query("UPDATE vendors SET item='$last' where item='$items[entry]'") or die (mysql_error()); $update = mysql_query("UPDATE quests SET ReqItemId1='$last' where ReqItemId1='$items[entry]'") or die (mysql_error()); $update = mysql_query("UPDATE quests SET ReqItemId2='$last' where ReqItemId2='$items[entry]'") or die (mysql_error()); $update = mysql_query("UPDATE quests SET ReqItemId3='$last' where ReqItemId3='$items[entry]'") or die (mysql_error()); $update = mysql_query("UPDATE quests SET ReqItemId4='$last' where ReqItemId4='$items[entry]'") or die (mysql_error()); $update = mysql_query("UPDATE quests SET RewChoiceItemId1='$last' where RewChoiceItemId1='$items[entry]'") or die (mysql_error()); $update = mysql_query("UPDATE quests SET RewChoiceItemId2='$last' where RewChoiceItemId2='$items[entry]'") or die (mysql_error()); $update = mysql_query("UPDATE quests SET RewChoiceItemId3='$last' where RewChoiceItemId3='$items[entry]'") or die (mysql_error()); $update = mysql_query("UPDATE quests SET RewChoiceItemId4='$last' where RewChoiceItemId4='$items[entry]'") or die (mysql_error()); $update = mysql_query("UPDATE quests SET RewChoiceItemId5='$last' where RewChoiceItemId5='$items[entry]'") or die (mysql_error()); $update = mysql_query("UPDATE quests SET RewChoiceItemId6='$last' where RewChoiceItemId6='$items[entry]'") or die (mysql_error()); $update = mysql_query("UPDATE quests SET RewItemId1='$last' where RewItemId1='$items[entry]'") or die (mysql_error()); $update = mysql_query("UPDATE quests SET RewItemId2='$last' where RewItemId2='$items[entry]'") or die (mysql_error()); $update = mysql_query("UPDATE quests SET RewItemId3='$last' where RewItemId3='$items[entry]'") or die (mysql_error()); $update = mysql_query("UPDATE quests SET RewItemId4='$last' where RewItemId4='$items[entry]'") or die (mysql_error()); echo "Item $items[entry] is now $last<br>"; $loop++; } echo "<br><br><center><h1>End</h1></center>"; ?> Quote Link to comment https://forums.phpfreaks.com/topic/101903-all-in-one-query/#findComment-521553 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.