Join 2 select statements together

[nzrobert]

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:

<?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:

$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. :-)

[nzrobert]

Ok to put is simpler how do i change these statements in to one statement:

Query #1

SELECT 
     `id_product` 
FROM 
     `pl_product` 
WHERE 
     `id_manufacturer` = '".$_GET['id_manufacturer']."'"

Query #2

SELECT 
     `name` 
FROM 
     `pl_product_lang` 
WHERE 
     `id_product` = '".$prod_id."'

($prod_id is the result of the query #1)

[nzrobert]

bump, please..

[samanime]

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:

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.

[guelphdad]

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.

[nzrobert]

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?

[guido2004]

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.

[nzrobert]

Cheers mate, your a legend!

[guido2004]

You must use an alias in this case as well

SELECT 
    table1.field1
  , b.field2
FROM table1
INNER JOIN
  (SELECT field2
   FROM table2
   WHERE ...
  ) AS b
ON table1.id = b.id