All in one query

[drisate]

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());

[laffin]

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.

 

[BlueSkyIS]

Hey guys i was wandering if there was a way to put all this into 1 query

 

no.

[Barand]

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.

[drisate]

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?

[pocobueno1388]

UPDATE quests SET ReqItemId1='$last', ReqItemId2='$last', ReqItemId3='$last' WHERE ReqItemId1='{$items['entry']}'

 

Just follow that pattern.

[drisate]

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]

[Psycho]

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");
}

?>

[drisate]

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>";

?>