Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    May 2007
    Posts
    93
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Merge 3 queries into one ?

    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
    Code:
    $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

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    $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
    Code:
    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.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,584
    Thanks
    78
    Thanked 4,385 Times in 4,350 Posts
    Code:
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •