Pull info from one mysql table based on another's result

[chaosuk]

Hi all,

 

I have a number of tables within a mysql database and I'm stuck on how to pull information from one based on the results being displayed by another.

 

The site shows items for sale where by all the product information is held in tablea, when an individual item is being displayed I need to be able to pull a manufacturer profile from tableb based on the manufacturer name stored in tablea column profile. The manufacturer name is already being called into the page by existing code as the site has always shown the manufacturer name, I just can't work out out to pull the new profile information in as well from the new table.

 

Any help appreciated

 

Iain

[Nodral]

http://www.tizag.com/mysqlTutorial/mysqljoins.php

[digibucc]

$query = 'SELECT from tableb WHERE manufacturer = $tablea_manuf';

 

JOINS would not be right nodral, as he is not simultaneously getting data from two dbs, he needs a value from the first to find the required record in the second.

[Nodral]

but why do 2 single queries when one will pull the information from both tables in one hit, rather than get the product in one query, then the manufacturer in another?

[digibucc]

i see your point, perform one query and then just compare the info?

 

i guess that would depend on how many results there will be. if a second query cuts the results that need to be parsed through by a large enough amount, then it's worth it. however you are right, JOINS does have a place here i just wasn't recognizing it

[Nodral]

but the initial query would be something along the lines of,

 

SELECT product, manufacturer FROM tablea WHERE............

 

Then you say to run

 

SELECT from tableb WHERE manufacturer = $tablea_manuf

 

for each time you need the manufacturer profile details.

 

Why not just do

 

SELECT a.product, b.profile FROM tablea a JOIN tableb b ON a.manufacturer = b.manufacturer WHERE.................

 

One hit to the DB instead of multiple.  If you were pulling 5000 products in the first query you would need 5001 queries to your DB using some sort of looping system.  With a JOINed query, you would only need 1 query which could return all the info into an array for display.  Thus greatly reducing server load.

[chaosuk]

Thanks for the feedback guys.

 

For reference, the manufacturer profile is only being displayed on a single item page that is chosen from an initial category page showing all items within the category.

 

Will try the options suggested and report back