SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Join 2 select statements together

    Hi there at the moment i am using 2 select statements. a) to pull out all the product id's where a manufacturer id = $_GET..... and b) to pull out all the product names out of a language table where the product id = y (what ever i got out of the first statement). This is the only way i was able to get around the problem i had. The issue is that the second statement is in a while loop and i can't order the results alphabetically.

    Here is the code:

    HTML Code:
    <?php
    $r3 = mysql_query("SELECT `id_product` FROM `pl_product` WHERE id_manufacturer='".$_GET['id_manufacturer']."'");
                                
                                while ($row = mysql_fetch_object($r3))
                                {
                                	$prod_id = $row->id_product;
                                	
                                	$r4 = mysql_query("SELECT `name` FROM pl_product_lang WHERE id_product='".$prod_id."' AND id_lang = 1 ORDER BY `name` ASC");
                                    while ($row = mysql_fetch_object($r4))
                                        {
                                           $prod_name = $row->name;
                                           $manu_id = $_GET['id_manufacturer']
                                        ?>
                                            <option value="<?php echo $manu_id;?>"><?php echo $prod_name; ?></option>
                                        <?php
                                        }
                                        
                                }
                                ?>
    I know there is a single statement i can use, presumeably with a JOIN, i just can't seem to get it working... this is what i have:


    HTML Code:
    $r3 = mysql_query("SELECT `pl_product.id_product`, `pl_product_lang.name` FROM `pl_product` WHERE `pl_product.id_manufacturer`='".$_GET['id_manufacturer']."' INNER JOIN  `pl_product_lang` ON `pl_product.id_product` = `pl_product_lang.id_product`");  
                                
                                while ($row = mysql_fetch_object($r3))
                                {
                                	$prod_name = $row->pl_product_lang.name;
                                    $manu_id = $_GET['id_manufacturer'];
                                }
    Please point me in the right direction.

    Thanks in advance. :-)

  2. #2
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok to put is simpler how do i change these statements in to one statement:

    Query #1
    HTML Code:
    SELECT 
         `id_product` 
    FROM 
         `pl_product` 
    WHERE 
         `id_manufacturer` = '".$_GET['id_manufacturer']."'"
    Query #2
    HTML Code:
    SELECT 
         `name` 
    FROM 
         `pl_product_lang` 
    WHERE 
         `id_product` = '".$prod_id."'
    ($prod_id is the result of the query #1)

  3. #3
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    bump, please..

  4. #4
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    Just so you know, you don't really have to bump around here... we're pretty good at getting to posts even if they sink down a bit.

    For simplicity sake, this example changes your get to $manufacturer (be sure to clean this input before you run the query or there could be trouble).

    To join them with a LEFT JOIN (which is probably what you want), you'd do something like this:
    Code:
    SELECT p.id_product, l.name
    FROM pl_product p
    LEFT JOIN pl_product_lang l
    ON p.id_product = l.id_product
    WHERE id_manufacturer = '$manufacturer' AND id_product = '$prod_id'
    Wrap that in "" and you won't need to escape them and use string concatenator (.) and all that ("" process simple variable names in them automatically, very handy).

    So, basically to do a join you start with a normal query. Then you figure out what table(s) you want to join. You then specify them in the join (LEFT JOIN is the most common, though there are about a dozen different types which you can check out in MySQL documentation). After that, you then specify an ON condition, which specifies which value in table A matches a value in table B.

    After that everything else is pretty much the same.

  5. #5
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by samanime View Post
    Just so you know, you don't really have to bump around here.
    I agree with that. nzrobert, you ESPECIALLY should not be bumping your posts after 20 minutes.

  6. #6
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Excellent, thanks.. works a charm.

    Am i right in thinking that those letters can be anything you decide them to be?
    and you simply define which table each letter refers to after you mention it in your query?

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    They are called aliasses. You must use them when you join the same table twice in one query, and you may use them in all other cases (for example to avoid having to write long table names multiple times in 1 query).
    And yes, they can be almost anything you want.

  8. #8
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cheers mate, your a legend!

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    You must use an alias in this case as well
    Code:
    SELECT 
        table1.field1
      , b.field2
    FROM table1
    INNER JOIN
      (SELECT field2
       FROM table2
       WHERE ...
      ) AS b
    ON table1.id = b.id


Tags for this Thread

Bookmarks

Posting Permissions

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