SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    May 2001
    Location
    United Kingom
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how do I link database tables together??

    I read in the tutorials here at site point that each 'thing' needs it's own table and that this is the correct way to create true relational databases. This is what I have tried to do but I don't understand how to link them back together to get the info I want. At the moment it seems a hell of a lot easier and more logical to dump all the info in one table. I have been told this is wrong though so I would like some help with this if possible.

    What I'm trying to do is link data in two different tables and then retrieve only a certain amount. To put it bluntly failing big time, can somebody help me please?

    My first table look like this..

    news
    -----
    id
    title
    img
    intro
    content
    mid <- this contains an integer which links to my other table called 'manufacturer'

    second table like this...

    manufacturer
    ------------
    id
    name


    so at the end of my news table I may have the following...

    ----------------------
    ¦content ¦ mid ¦
    ----------------------
    ¦article 1 ¦ 1 ¦
    ----------------------
    ¦article 2 ¦ 1 ¦
    ----------------------
    ¦article 3 ¦ 1 ¦
    ----------------------
    ¦article 4 ¦ 2 ¦
    ----------------------
    ¦article 5 ¦ 1 ¦
    ----------------------
    ¦article 6 ¦ 3 ¦
    ----------------------

    and in my manufacturer table I would have the following...

    -----------------
    ¦ id ¦ name ¦
    -----------------
    ¦ 1 ¦ Technics ¦
    -----------------
    ¦ 2 ¦ Vestax ¦
    -----------------
    ¦ 3 ¦ Pioneer ¦
    -----------------

    what I’m trying to do is pull all the news articles from the first table that relate to an individual manufacturer in the second table. I know this is done through the mysql_query() function but I’m not quite sure how to do it. So far I have got the following but is this correct because it looks complete rubbish to me?


    $review = mysql_query("SELECT news.id, title, img, intro FROM news, manufacturer WHERE mid = manufacturer.id ORDER by news.id DESC");

    I don't see how the above line will enable me to pull all the article by the manufacturers Technics or Pioneer for example. Do I need to use the AND command with a variable like this...

    $review = mysql_query("SELECT news.id, title, img, intro FROM news, mid WHERE mid = manufacturer.id AND name = $manufacturer_name ORDER by news.id DESC");

    At the moment I can display each article individually but my goal is to have a link at the bottom of the page that says 'click here for more news from (insert manufacturer here)' which will then pull all the articles just by that manufacturer rather than the complete list of articles.

    I'm getting a little confused here, please help!

    Paul.
    Last edited by djsource; Jun 4, 2001 at 11:16.
    http://www.djsource.co.uk - the complete dj source.

  2. #2
    SitePoint Wizard
    Join Date
    Mar 2001
    Posts
    3,537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Put it all in one table. Why make things so difficult? If you need a relational database, then create one. If it is simpler to use one table, then use one table.
    I read in the tutorials here at site point that each 'thing' needs it's own table
    Really? Then why didn't you make separate tables for all your fields?

    Here is an example of linking tables:

    customers
    ----------
    id
    name
    products_ordered
    products_back_ordered

    contacts
    ----------------------
    id(same as id in table customers, this provides the link)
    name(not of company, but of contact)
    address
    tel
    misc.
    Code:
    $sql = "SELECT customers.id, customers.name, contacts.name,
     contacts.tel FROM customers, contacts WHERE 
    customers.id=contacts.id AND customers.name = 'ibm'";
    Last edited by 7stud; Jun 4, 2001 at 11:49.

  3. #3
    SitePoint Member
    Join Date
    May 2001
    Location
    United Kingom
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by 7stud

    Really? Then why didn't you make separate tables for all your fields?
    Yeah I read it here. I'm just learning PHP/mySQL and everywhere I look on the Net and every book I read they all say that each 'thing' should be put into a seperate table.

    In my case the first 'thing' is the news article. Even though I have defined several fields I'm still classing it is as 1 'thing'. By seperating my artcle into fields such as pic, intro, content it is just an easy way for me to stuff the contents into $variables and dislpay them on different pages at a later time.

    I've put manufacturer as a seperate 'thing' in it's own table for many reasons. Mainly because I will be using this table else where to link together Store Categories, Full Product Reviews, Related News stories etc etc. I thought by placing it in another table this would prevent me from entering in the same data time and time again, thus wasting valuable space, server resources and decreasing the chance of spelling mistakes in different tables.

    I was under the impression that it would be much easier to assign the news article with an integer value of 1 for example than write the words Stanton Magnetics several hundred or thousand times in different tables. Also if the manufacturer Stanton Magnetics changed it's name to just Stanton I would then have to edit hundereds or thousands of rows. By saving the category 'manufacturer' as it's own table I would only have to edit only 1 row!! seems logical to me.

    That's my reasons anyway. Thanks for posting up some code for me to try, i'll see if I can get something to work by using it.

    Paul
    Last edited by djsource; Jun 4, 2001 at 12:19.
    http://www.djsource.co.uk - the complete dj source.


Bookmarks

Posting Permissions

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