What Will I Learn?
In this tutorial you will learn:
- How to insert data in MySQL using PHPMyadmin
- How to query the multiple databases using the inner join
- How to retrieve data from multiple databases and display them in a single table
Requirements
Knowledge of the following is required in order to follow this tutorial.
- Basic Knowledge of MySQL Syntax
- Knowledge of PHPMyadmin SQL console
- Knowledge of PHP OOP (Object Oriented Programming)
- Basic understanding of MySQL Joins
Difficulty
- Intermediate
Tutorial Contents
In order to avoid redundancy, its very important to organize data into relevant tables. It's pretty easy to query a single table and retrieve results, but retrieving results from multiple tables, is a different ball game entirely.
In this tutorial I will be taking you through how data can be retrieved from multiple tables and displayed using PHP.
This tutorial is divided into 2 parts, the first part deals with creating some sample data. while the second part deals with retrieving the data.
PART 1
The first thing we need to do is to create our database, which we will call customerorder. To do that we will open PHPMyadmin, then click on SQL and type the following code:
CREATE DATABASE customerorder;
Now lets create our tables. So we will go back into MySQL and type the following lines of code. Please note that you have to select the database you just created customerorder, before you click
on SQL, that way MySQL knows the database you are working on. So let's create our table customer, by entering the following code :
CREATE TABLE customer (
customerid INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(40) NOT NULL,
email VARCHAR(40) NOT NULL,
phone VARCHAR(50),
city VARCHAR(50)
);
We now create our second table called orders by entering the following code:
CREATE TABLE orders (
orderid INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
customerid VARCHAR(40) NOT NULL,
email VARCHAR(40) NOT NULL,
orderdate date
);
For the third table let's enter the following lines of code into the SQL console and click go.
CREATE TABLE shipping (
shippig_id INT(40) NOT NULL AUTO_INCREMENT PRIMARY KEY,
customerid VARCHAR(40) NOT NULL,
location VARCHAR(60) NOT NULL,
shipping_date date
);
Now that we have created out tables, can start to enter some sample data for our application to work. So this time when we go into MySQL we will select the table we want to insert data into before clicking SQL. We will now insert data into customer table.
INSERT INTO customer (name, email, phone, city )
VALUES ('Tyson Beckfor', '[email protected]', '12345', 'Newyork'),
('Charlie Stone','[email protected]','22345', 'San Diego'),
('Harry ford','[email protected]','32345', 'Boston'),
('Toni Stone','[email protected]','42345', 'Texas'),
('Charlize page','[email protected]','52345', 'Los angeles'),
('Bill stone','[email protected]','62345', 'Alaska'),
('katie miller','[email protected]','72345', 'Boston');
When we run the code we will get something similar to the picture below:
Let's do the same for the orders table. Don't forget to select the orders table before clicking sql. Type in the following code to insert our sample data
INSERT INTO orders (customerid, orderdate )
VALUES ('1', '2017-12-3'),
('2','2017-4-3'),
('3','2017-8-1'),
('1','2017-8-5'),
('2','2017-6-1'),
('4','2017-6-3'),
('2','2017-5-1'),
('1','2017-5-3'),
('3','2017-4-1'),
('4','2017-4-5');
in order to conclude this part we would insert data into the shipping table. That can be done by typing the following code in MySQL
INSERT INTO shipping (customerid, location, shipping_date )
VALUES ('1', 'Kansas', '2017-10-10' ),
('2','Newyork', '2017-07-8'),
('3','Lagos', '2017-7-15'),
('1','Abidjan', '2017-7-18'),
('2','Texas', '2017-8-15'),
('4','Los Angeles', '2017-9-24' ),
('2','Washington', '2017-7-15' ),
('1','Abuja', '2017-5-15'),
('3','Dallas', '2017-9-18'),
('4','Florida', '2017-10-15' );
Now that we have concluded part one it's time to move straight to the second part.
PART 2
Having created our sample data it's time to start retrieving it using php. We will attempt to retrieve the data from 3 tables at the same time. So we begin by creating connections to our database.
$servername = 'localhost';
$username = 'root';
$password = '';
$dbase = 'customerorder';
$conn = new MySQLi ($servername, $username, $password, $dbase);
So lets check to make sure there is a connection and output an error message if otherwise
if ($conn->connect_error){
die ("Unable to connect: ". $dbconn->connect_error);
}
In the above code, the die() function kills the script when there is no connection.
Now for the fun part. We will carry out an SQL query using an inner join, this would query our tables and return results from the 3 tables.
$sql = "SELECT customer.customerid, customer.name, orders.orderid, shipping.location
FROM customer
INNER JOIN orders ON customer.customerid = orders.orderid
INNER JOIN shipping ON customer.customerid = shipping.shippig_id";
$result = $conn->query($sql);
echo ("
Customer ID
Name
Order ID
Location
"
);
if($result->num_rows > 0){
while ($row = $result->fetch_array()){
$dbcusid = $row['customerid'];
$dbcusname = $row['name'];
$dborderid = $row['orderid'];
$dblocation = $row['location'];
echo ("
$dbcusid
$dbcusname
$dborderid
$dblocation
");
}
}
When we view the results on the browser we get what is similar to the picture below.
To make the tables more appealing we would throw in a little bit of CSS but before then let's add some HTML.
<!DOCTYPE html>
<html>
<head>
<link href="style.css" rel="stylesheet">
</head>
<body>
</body>
</html>
Now let's create our style.css file and throw in our CSS
table {
font-family:"Trebuchet MS", Arial, Helvetica, sans-serif
border-collapse: collapse;
width: 80%;
}
th {
border: 1px solid #dddddd;
text-align: left;
padding: 8px;
}
td {
border: 1px solid #dddddd;
text-align: left;
padding: 8px;
}
tr:nth-child(even) {
background-color: #dddddd;
}
.button {
background-color: rgb(66, 133, 244);
border: none;
color: white;
padding: 16px 33px;
text-align: center;
text-decoration: none;
display: inline-block;
font-size: 17px;
margin: 4px 2px;
cursor: pointer;
border-radius: 3px;
letter-spacing: 1px;
}
You can view the changes below
source code available on github
Posted on Utopian.io - Rewarding Open Source Contributors