Help with MySQL and linking 2 tables

[jbwebdesign]

Hello, i am trying to create a script that will have 2 different MySQL tables but I am not sure how to do this at all......

 

Can anyone please show me where to start my query??

 

the point of this is because I have 1 table that shows the users personal info and I want to make a seperate table for all the users sales and what dates he has sold things etc. This way it will track down the sales that the user has been making.

 

If anyone can show me how to connect 2 different tables I would really appreciate it. Thanks

[administrator]

Hi,

 

You have two options I can think of:

 

1. Use an sql view.

2. Use a PHP transaction.

 

I know you know what views are so I won't go into them here. Transactions allow you to fire off multiple sql statements one after another, where if one should fail, all the sql statements are canceled. This way, you could update many tables at the same time and insure that the database doesn't get corrupted if something happened half way through.

 

You can find the right syntax for php transactions if you do a search for it.

 

Stefan

[LSW]

MySQL is a derivative of SQL. W3Schools has a good SQL reference I use.

 

I saw in another post that you used a comma to join tables, that is workable but old school. The newer form is more writing but more clear to people.

SELECT *   -- That means select everything
FROM table1
  LEFT JOIN table2
  ON table1.shared data = table2.shared data

What you are doing is selecting all the data (or what you want) from Table1 which is joined with table2 on a field that both tables have in common. The name may be different, but the data Must be the same. We use SSNs here. For you it may be a user ID column that you have in both tables.

ON table1.userID = table2.userID

 

As for the left join, there are three really, left right and inner join.

 

Let me try to explain it this way.

 

Consider the most important info to be left then in importance order right the names down going right. This is how you join. If you are mostly after personal info, customer would be a left join to say sales.

 

Left join is the priority info + if their is info that matches the info on the left (if the ID left matches a sale, show it, if not, do not show it, show me only sales for what is left. This means there may be fields left empty)

 

Inner Join is equal on both sides, show me all info regardless of matches.

 

Right join is not used often, it gives priority to info on the later tables... but if that is the priority you likely put it left... Right join means something in the left table MUST match something in the right table. Left meant something right must match left. Right joins are usually used with connection tables.

 

Lets say you have two tables with nothing in common, no shared data like userID. How do you join them? Let us say the customer table has a column for customer Type, this you left join to a table made up of just 1 column "customerType" (private, retail, corporate, non-profit). Left join as the important info is the customer.

 

But you want sales info, but sales has no userID, but it does have customer type because some products are only for corporate customers for some reason. That makes this customerType table a connecting table between the other two.

 

Now you take customer - left join to customerType - and right join to sales as it is more important then type.

 

match the right table to the left table if their is a match, then take the info where the middle table matches the right table and connect the three.

 

SELECT *   -- That means select everything
FROM table1
  LEFT JOIN table2
  ON table1.shared data = table2.shared data
  RIGHT JOIN table3
  ON table3.shared data = table2.shared data

 

Clear as mud? Joins are tricky at first...

[jbwebdesign]

wow thanks sooo much man :cool: that was as clear as it can get............it really helped me out alot an i got my script working

Edited June 12, 2009 by jbwebdesign