php or mysql question

I'm not sure how to do this so I don't know if I would do it with PHP or if
I can actually do it in a SQL statement but heres the scenario. I have 5
tables that I'm retrieving data from. The main table houses an "ownerID"
which is a number. Then number and associated owner are in a table called
owners. When I pull the results from the tables I of course get the number
of the owner. I would like to fill that in with the name of the owner upon
display. Can I modify my sql statement to take care of the association or do
I do it in PHP. Here is my current sql statement. Any help would be greatly
appreciated. Thank you

-------start sql-------

SELECT inventory.productID, inventory.price, inventory.ownerID,
minerals.speciesID, minerals.`size`
FROM inventory, minerals
WHERE minerals.productID = inventory.productID
AND inventory.productType = 'm'

-------end sql-------

Jason

Jason Cooke a écrit le 20/01/2004 :
> I'm not sure how to do this so I don't know if I would do it with PHP or if
> I can actually do it in a SQL statement but heres the scenario. I have 5
> tables that I'm retrieving data from. The main table houses an "ownerID"
> which is a number. Then number and associated owner are in a table called
> owners. When I pull the results from the tables I of course get the number
> of the owner. I would like to fill that in with the name of the owner upon
> display. Can I modify my sql statement to take care of the association or do
Yes you can
> I do it in PHP. Here is my current sql statement. Any help would be greatly
You can also do it in another query if it is what you meant by "do it
in PHP". It depends if you have a lot of rows returned by your first
query and if you'll need other other info of your "owner". Otherwise
you can use jointure to do the trick.
I think in your case I would choose to have a separate SQL query to
retreive all owners details. Something like
<?php
//considering $myVar contains the ownerID returned by your SQL command
$sql = "SELECT * FROM owner_table WHERE ownerID=$myVar";
//code to treat the SQL command
?>

On 20-Jan-2004, "Jason Cooke" <jc@jcimedia_JunK_.com> wrote:

> I'm not sure how to do this so I don't know if I would do it with PHP or
> if
> I can actually do it in a SQL statement but heres the scenario. I have 5
> tables that I'm retrieving data from. The main table houses an "ownerID"
> which is a number. Then number and associated owner are in a table called
> owners. When I pull the results from the tables I of course get the number
> of the owner. I would like to fill that in with the name of the owner upon
> display. Can I modify my sql statement to take care of the association or
> do
> I do it in PHP. Here is my current sql statement. Any help would be
> greatly
> appreciated. Thank you
>
> -------start sql-------
>
> SELECT inventory.productID, inventory.price, inventory.ownerID,
> minerals.speciesID, minerals.`size`
> FROM inventory, minerals
> WHERE minerals.productID = inventory.productID
> AND inventory.productType = 'm'
>
> -------end sql-------

That's what relational DBs are all about.

SELECT inventory.productID, inventory.price, inventory.ownerID,
minerals.speciesID, minerals.`size`, owner.name
FROM inventory, minerals, owner
WHERE minerals.productID = inventory.productID
AND inventory.ownerID=owner.ownerID
AND inventory.productType = 'm'

BTW you don't need the backticks around size, it's not a reserved word

--
Tom Thackrey
www.creative-light.com
tom (at) creative (dash) light (dot) com
do NOT send email to jamesbutler@willglen.net (it's reserved for spammers)