Merge 3 queries into one ?

[anarchoi]

This script is taking a lot of time to run and i was wondering if it would be possible to merge the queries into only one

$query = "SELECT * FROM shop_tshirts WHERE shop_id = '429942'";
$res = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($res)) {
$designid = $row["design_id"];
$articleid = $row["article_id"];
echo "<br>A$articleid = D$designid";
	$zquery = "SELECT * FROM shop_tshirts WHERE shop_id = '266497' AND design_id = '$designid'";
	$zres = mysql_query($zquery) or die(mysql_error());
	while($row = mysql_fetch_array($zres)) {
	$categorie = $row["categorie"];
	echo " = C$categorie";
		$xquery = "UPDATE shop_tshirts SET categorie='$categorie' WHERE article_id='$articleid' AND shop_id='429942'";
		$xres = mysql_query($xquery) or die(mysql_error());
		echo " .......... UPDATED!";
	}
}

The database is used to store a list of t-shirts
"article_id" is the unique identifier of the t-shirt
"design_id" is the ID of the design of the t-shirt
"categorie" is the category
"shop_id" is used for the product types. 429942 is sweatshirts and 266497 are t-shirts

The script is used to find the category ID of each sweatshirts based on the category of the t-shirt with the same design

1st query will find the list of the sweatshirts using shop_id 429942
2nd query will find the category ID by checking what category ID is being used by the t-shirt with the same design
3rd query will update the sweatshirt with the same category ID than the tshirt

[BubikolRamios]

$query = "SELECT * FROM shop_tshirts WHERE shop_id = '429942'";
$zquery = "SELECT * FROM shop_tshirts WHERE shop_id = '266497' AND design_id = '$designid'";

for first two

select * from shop_tshirts t left join shop_tshirts t1
on t.design_id = t1.design_id
where t.shop_id = 429942

If queries as it si now slow, you don't have indexes on key fields.

[Old Pedant]

UPDATE shop_tshirts AS T1, shop_tshirts AS T2
SET T1.categorie = T2.categorie
WHERE T1.article_id = T2.article_id
  AND T1.design_id = T2.design_id
  AND T1.shop_id = 429942
  AND T2.shop_id = 266497

"Update the categorie of all items with a shop_id of 429942 to match the categorie of items with shop_id of 266497 when the article_id and design_id also match."

No PHP needed. Do it in any MySQL query tool.