Associating or JOINING three tables?

jaydeesmith8

Hi,

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
FROM
pr_collections
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,
JD

jaydeesmith8

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 in advance!!,
JD

lcook

From my limited knowledge, the primary keys are associated naturally if they are the same fieldname in two (or more) tables. If they are not the same, you can go into phpMyAdmin and set the association by clicking browse on a table and finding "Relational View" link at the bottom of the table.

In response to getting the path name saved to your database, read up on the global $_FILES array. This array saves several attributes of the file and can be easier placed into your query. It will depend on if you are using cookies or sessions to identify your user and place the file's path name to the correct ID.

I too am just beginning so this probably isn't of much help - will look back to see how greater minds respond. 🙂

bradgrafelman

lcook wrote:
From my limited knowledge, the primary keys are associated naturally if they are the same fieldname in two (or more) tables.

That depends what you mean by "associated". If you do a "NATURAL [noparse][LEFT][/noparse] JOIN" type of query, then yes, the definition of a 'NATURAL JOIN' states that attributes sharing the same name in the join will be used to eliminate unneeded rows from the Cartesian product.

If we're talking about foreign keys, however, then no, nothing is done automatically for you. Also note that foreign keys don't do anything in a SELECT query when JOIN'ing two tables together - you still have to list the JOIN condition.

Roger_Ramjet

JD, to answer your first question, yes that query is correct and will allow you to select the correct data across the 3 tables. As Brad says, you have to include the joins in every query to do this.