MySQL -> Table Query

[malw]

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

[datune]

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

[malw]

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