如何使用php与mysql中的表进行交互。

I have two tables in my database. I use the first one for carrying out the login/registration process where I store the user info (Name , Email , Password). So , I created the second table where the registered users can add product image and name to but I found out that the 2 tables can't interact and there is no way for them to use the second table.

Please , how can I do this ?

If you need more info let me know.

You can do this with the help of the foreign key relationships.

Reference: http://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html

  • First you have to create the table with the relational ID(Foreign Key) from the First Table so that you can fetch the data.
  • After you provide the Foreign key to the Queries where you can use the Joins that are available and you can get the data.

Note: Unless you have the relational ID of the other table you are not advised to use the JOIN Query since you have to pass the Relational ID(Foreign KEY) Over to the join stmt.

SQL statement for Creating the Foreign Key ID From the First Table itself.

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

The following SQL creates a FOREIGN KEY on the "P_Id" column when the "Orders" table is created

After Creating the Foreign Key to both the tables that you create you can follow up the code over here and then you can join both the tables.

http://dev.mysql.com/doc/refman/5.7/en/join.html

You have to create the Table Structure like this:

User Info Table:

+------------------------------------------+
| id   |   name   |   Email   |  Password  |    
+------------------------------------------+

And the Product table like this

+------------------------------------------+
| id   |   user_id   |   product_name      |    
+------------------------------------------+
  • Here the user_id will act as the foreign for the user table when accessing the product table.

Hence the Code will be as follows.

To fetch all the data from two tables:

$query = "SELECT * FROM user JOIN product ON user.id = product.user_id ORDER BY user.id DESC"

To fetch the data based on particular user.

$query = "SELECT * FROM user JOIN product ON user.id = product.user_id WHERE user.id ='".$id."'"
  • Where $id is the ID of the Current User.

Hope so my explanations will be clear and it will help you a lot.

Happy Coding:)