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:)