View Full Version : Combining or Associating Three Tables

05-18-2011, 04:17 PM

I hope someone can clue me in on how to associate (JOIN) three tables so I can call a product and it pull needed data for the output when needed...

I have three DB Tables (pr_base_info, pr_collections and pr_vendors) that I want to make sure are interconnected within the SQL Database.

Each one has a vendor_id column so can I interconnect, associate, relate or whatever the correct term is the three tables to be able to pull information in a query when needed, right?

Would I use the code below I generated from a DB Tool or something like it when pulling information to display on the queried PHP page or is there a query string I could enter into the PHP MyAdmin QUERY BOX to inerconnect (associate) the tables?

SELECT pr_collections.vendor_id
, pr_vendors.vendor_id
, pr_base_info.vendor_id
INNER JOIN pr_vendors
ON pr_collections.vendor_id = pr_vendors.vendor_id
INNER JOIN pr_base_info
ON pr_base_info.vendor_id = pr_vendors.vendor_id

Basically does the code above interconnect the tables and how can make sure the SQL DB knows..

Thanks in advance for your time and expert help,

05-18-2011, 05:08 PM
Yes, that join will work, but be careful-- you've written a Cartesian join, which will produce a huge amount of rows in your resultset. The number of rows will be #rows_in_table1 x #rows_in_table2 x #rows_in_table3. So if your three tables have 1000, 3000, and 4000 rows in them, that join will produce 12,000,000,000 (12 billion) rows.

You can easily avoid such a problem by filtering results with a WHERE clause and additional conditions on the joins.

05-18-2011, 11:31 PM
Thanks for the heads up....

All I want to do is make sure that the image I upload for a product is stored in the table pr_base_pic under a column named pic_name (first column is vendor_id) and associated with the other tables by the vendor_id syncing so that I can insert a product individually or include it when a certain vendor_id is called... Or better yet STORE JUST THE URL THAT WAS UPLOADED TO A FOLDER LABELED /prod_img

So here is a word question for you, hope you or someone likes them...

My objective:

Upload a an image that gets uploaded through a form with a attribute or name of 'file' and it saves into the /prod_img/ folder after uploading.

THAT WORKS PERFECTLY; HOWEVER I JUST NEED TO GET A CODE OR QUERY THAT WILL INSERT THAT IMAGE, uploaded as 'file' (or better yet a url link to that image) INTO THE SQL DB TABLE NAMED " pr_base_pics " under the column named " pic_name " and associate with the "base_id" of a product that was created. I am just adding pictures, but want o be able to have it associated with its manufacturer, style, and other query methods that follow over the three tables...

I think I can generate a frontside code that calls the query to display what I want within the query filters, but just need to be sure of how to make sure the 'file' uploaded is stored and associated correctly.

I have this below in the PHP page that uploads the product image and know it is wrong:

// These three lines now put everything into the table.
$dbQuery = "INSERT INTO pr_base_pics VALUES ";
$dbQuery .= "(0, '$strDesc', '$fileContent', '$fileUpload_type')";
mysql_query($dbQuery) or die("Couldn't add file to database");

So in essence I need to know how the image, or file to the folder with the image name, gets written into the DB TABLE of pr_base_pics under the second column labeled pic_name....

Can you guide me to a query string that will do that as I know the above is not correct as it was generated from a computer tool and does not even show the correct string or item 'file' that I see.. But I am a novice and that is why I am seeking GREATER MINDS!!

Thanks for the help and any extra advice,