how to avoid query inside a loop ?

[Ujj]

Hi,

 

As I was suggested by forums members not to run a query inside a loop. I am a lot concerned about it  and trying to avoid running query inside loop.thanks for providing me a life saving knowledge.

 

But I have got one situation where I need the help again,

 

I make a request to a server which gives me a xml in this form

<ItemsResponse>
<Ack>Success</Ack>
<Item>
    <ItemID>1</ItemID>
<Price>20.00</Price>
    <Title>test title item A</Title>
  </Item>
  <Item>
    <ItemID>2</ItemID>
<Price>25.00</Price>
    <Title>buxton still water</Title>
  </Item>
  <Item>
    <ItemID>3</ItemID>
<Price>33.00</Price>
    <Title>external hard drive</Title>
  </Item>
</ItemsResponse>

 

then I execute a query based on each item id  to update a table

 

                  $request_result1 = strtolower($resp1->Ack);
						if (strcmp($request_result1, "success") == 0 ){							
							   foreach($resp1->Item as $item) {									 
								$title =$item->Title;
								$itemID = $item->ItemID;
								$currentPrice = $item->Price;

							   $sql = "UPDATE  product_detail SET 
							    `title` = '$title', 
							      `price`= '$currentPrice',
								 WHERE `user_id` = '$uid' AND `itemID` = '$itemID'";
                                                                    mysql_query($sql)
								 }//end for each
						}

 

how can i avoid running this code inside this loop?

[kicken]

You would have to build a query using some CASE statements to assign the right values to the right rows.  It is not usually worth the hassle.

 

Avoiding queries in a loop is something aimed more at SELECT and INSERT queries as it is generally easily avoidable and results in a much faster execution time.

 

[jazzman1]

With CASE operator, the sql is a little more complex, but it is possible to achieve the same result using only one query.

Check this out -> http://www.karlrixon.co.uk/writing/update-multiple-rows-with-different-values-and-a-single-sql-query/

[PFMaBiSmAd]

A multi-value REPLACE query is also an option -

 

REPLACE INTO your_table (your column list) VALUES (value list 1),(value list 2), ...

 

This would require that you have all the columns listed in the query since it completely replaces any existing row that it finds. If your table has more than just the title, price, user_id, and itemID columns, you would not do it this way since that would require you to first SELECT the existing data to get any other columns. It would also require that you have a composite unique key using your user_id and itemID columns.

 

This method also has the advantage of INSERTing new rows into your table along with replacing existing rows. If you currently have code that is testing if the user_id and itemID exist, then executing an INSERT query if they don't and an UPDATE query if they do, the REPLACE query will replace all that code with one single query.

[Ujj]

thank you very much for all your support and advice