SitePoint Sponsor

User Tag List

View Poll Results: MySQL -> Table Query

Voters
2. You may not vote on this poll
  • 1 Sorry I hit the wrong button trying to post this question!

    1 50.00%
  • 2 Sorry I hit the wrong button trying to post htis quesiton!

    1 50.00%
Multiple Choice Poll.
Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    May 2002
    Location
    Canada
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile MySQL -> Table Query

    I have a question to ask. I have spent weeks trying to solve this issue. I know it is often the simplest things that take the longest to understand. Well here is one of them I need help on!

    I have three tables - names_tb, city_tb, zip_tb. How do I get for example, Mike Holmes from names_tb, New York from city_tb, and 13599 from zip_tb to display the information together properly if the have different primary key numbers?

    I assume that there is going to be several people from one the same city that join the database at different times. So my main struggle is learning how to query information from differnt tables to display the pieces of information that belong together.

    Table structure & fields;
    names_tb // lst_name, fst_name
    city_tb // city, town
    zip_tb // zip, postal_code
    email_tb // prim_email, second_email, third_email

    I have spent weeks trying to learn how to join tables, query and display the results in an html page. I am recently studying C++ at school. So am pretty quick at picking up on the technical side of things. Any help provide would much appreciated.


    Thanks,

    Mal

  2. #2
    SitePoint Guru
    Join Date
    Feb 2002
    Posts
    625
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello!

    Not that i am an expert in databasedesign, but this should help clear things up.

    One way of doing it would be as follows:

    Let`s go with your names_tb.
    You could do as follows

    ID ( INT primary key auto_increment)
    CID (INT)
    ZID (INT)
    FirstName
    LastName
    etc...

    Now, when you INSERT a new person into your database you do as follows
    You first make a query and insert the persons FirstName and LastName into the database.
    Then you make a query and insert the values for the city_tb and RIGHT after the query you get the last inserted id and then you update your names_tb and insert into your names_tb the last ID into the CID cell.

    Do the same with your zip table and voila.

    Your select query would look like this

    SELECT FirstName, LastName, City, Zip FROM names_tb, city_tb, zip_tb WHERE names_tb.CID=names_tb.ID AND names_tb.ZID=zip_tb.ID

    Get the idea?
    I wish i could explain it a little better with some graphics..
    Hmm...what you could do is read Kevin Yank's article on mysql.com
    Just skip the sections youre not interested in but read the part about databases, he explains very well the relational databasedesign basics.

    Hope i could help

  3. #3
    SitePoint Member
    Join Date
    May 2002
    Location
    Canada
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking Thank you!

    Datune!
    Thank you for you assistance. I am sorry for the delay in responding to your posting. It certainly helped and I am also reading Kevin Yank's book as well.

    Just about every week I have been studying what you have posted as time permitted. I have the four tables displaying the data in a template.

    My next attempt in to insert data from a html form into the four tables. If you have any suggestion that is much appreciated.

    Again - thanks for the help. It was a boost!!!

    Mal
    Canada


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
  •