Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    May 2011
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combining or Associating Three Tables

    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

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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.

  • #3
    New to the CF scene
    Join Date
    May 2011
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    What Would Work

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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •