Jump to content

All in one query


drisate

Recommended Posts

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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]

Link to comment
Share on other sites

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

?>

Link to comment
Share on other sites

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

?>

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.