php/mysql table primary key/linking question

[jericho]

I have problems with using some table fields in PHP scripts. I think this goes beyond PHP and tackles MySQL. I set up tables in MySQL database and defined their id's as primary key fields. But I have problems with linking the tables together.

Let me illustrate this with an example.

Suppose I have 2 tables in MySql database:

Table 1. Customers
Fields:
- Customer_id (primary key)
- Customer_name
- Customer_address

Table 2. Orders
Fields:
- Order_id (primary key)
- Custoer_id
- Order_value

I don't want (and don;t have to) include all the customer name and address info in Orders table, but I'd like to link Orders table to Customers table using Customer_id field. Later on, I want to reffer to full Customer info from Customer table through the Customer_id link.

The link would go like this:

Table: Customers ------- Table: Orders
Field: Customer_id -----> Field: Customer_id

How do I set this link?

[DaiWelsh]

Just join the tables in the query you use to get the order information back. There are several ways to perform the join, but assuming every order has an associated customer record you could use

SELECT * FROM Orders,Customers WHERE Orders.Customer_ID = Customers.Customer_ID
AND Order_ID = x

Then the result set will contain all the fields from the row in the orders table and all fields from the associated row in the customers table.

In a real world scenario you should really list the fields to return rather than SELECT * but that is for you to choose :)

Just be aware that where fields have the same name in both tables you will need to qualify the name by adding the table to the front as I did with Orders.Customer_ID and Customers.Customer_ID.

It is possible to join the tables such that the order is brought back even if there is no asociated customer record but you may not need that.

HTH,

Dai

[Daot Lagorille]

Bang on, DaiWelsh, although a convention I like to use is:

SELECT * FROM Orders a,Customers b WHERE a.Customer_ID = b.Customer_ID ...

I think it's probably personal preference though. Also, if you have a really big sql statement, it is easier to keep track using the actual table names.

So actually , the way I do it is pretty dumb.

Crap.

[DaiWelsh]

lol, yes I alias my tables sometime too, plus another habit is naming the fields in tables as far as possible uniquely (not having ID in every table, but ProductID, OrderID etc.) that way you dont have to qualify at all as all fieldnames are unique.

Of course some people hate that idea, particularly with the extra typing so to each their own.

[jericho]

Thanks, guys! :)

I got it... I did this, just needed to refresh my mind. *slaps his forehead*

Can't I use JOIN somehow to link the 2 tables?

In MsSql I create views and they make my life much easier, but I work with MySql, where I think you cannot create views, at least not in a direct way.

Do you guys know any good solution to that?

Again, thanks a lot for all your feedback.

[DaiWelsh]

The syntac we used

SELECT...FROM table1,table2 WHERE table1.x=table2.y

is actually a join, even though it does not use the SQL JOIN keyword. This format is an implied INNER JOIN. MySQL does also support explicit JOINs so you could say

SELECT...FROM table1 INNER JOIN table2 ON table1.x=table2.y

and similarly for other JOIN types like RIGHT JOIN.

As far as views are concerned I have never really used them, but as I understand it they are just pre-defined SQL queries that you can use later. There is no direct equivalent in MySQL afaik but you can test your data just by running queries in a MySQL gui tool.

HTH,

Dai

[jericho]

OK, that makes sense, thanks DaiWelsh. So the syntac is a JOIN. Can I use it with JOIN in PHP, will it work? So I could define if it is an INNER JOIN, LEFT JOIN etc...

Views work great in MsSql. Yes, these are queries, but it saves you a lot of work. A view works just as a table, but the columns are linked to columns from different tables.

Anyway, thanks for all the excplanation, this gives me a good picture of how to use MySql, thanks! :cool: :D :p