How to Easily Generate Charts from a MySQL Database Using PHP

August Royalties Earned Per Day

Title	Author	Price	Date	Quantity	Earning
Learn SQL By Examples	Sergey Skudaev	2.99	08/01/2019	2	5.98
Learn SQL By Examples	Sergey Skudaev	2.99	08/02/2019	2	5.98
Learn SQL By Examples	Sergey Skudaev	2.99	08/03/2019	4	11.96
Learn SQL By Examples	Sergey Skudaev	2.99	08/04/2019	2	5.98
Learn SQL By Examples	Sergey Skudaev	2.99	08/05/2019	2	5.98
Learn SQL By Examples	Sergey Skudaev	2.99	08/06/2019	1	2.99
Learn SQL By Examples	Sergey Skudaev	2.99	08/07/2019	3	8.97
Learn SQL By Examples	Sergey Skudaev	2.99	08/08/2019	3	8.97
Learn SQL By Examples	Sergey Skudaev	2.99	08/09/2019	1	2.99
Learn SQL By Examples	Sergey Skudaev	2.99	08/10/2019	4	11.96

August Royalties Earned Per Day Chart

When I start a new project, first, I create a database and tables. In this demo project I use MySQL. The database name is mydb. The first table name is books. It contains data about a book: title, author, cost. The primary key that identifies each record is bookid. To create a table, use the following statement:

CREATE TABLE books (
bookid INT NOT NULL AUTO_INCREMENT,
title varchar (100),
author varchar (20),
cost DECIMAL (4,2),
PRIMARY KEY (bookid)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

The bookid data type is an integer. Auto increment means that when you insert a new record, the bookid will automatically be increased by one. (AUTO_INCREMENT=1). The title column has a string data type. The varchar (100) means that the string length is limited to 100 characters. The author column has a string data type limited to 20 characters. For currency, I use a decimal data type. It is a number with 2 decimal places. Insert data in the books table with the following SQL statements:

insert into books (bookid, title, author, cost) values (1, 'Learn SQL By Examples', 'Sergey Skudaev', 2.99);
insert into books (bookid, title, author, cost) values (2, 'PHP Programming for Beginners', 'Sergey Skudaev', 2.99);
insert into books (bookid, title, author, cost) values (3, 'PHP Programming for Beginners C++ Programming By Example', 'Sergey Skudaev', 2.99);

The second table stores data about books sales. This table includes bookid from the first table to link the book data with the book sales data. The saleid is the primary key for the books_sales table. The sold_date is the date when the book was sold and the book_num field stores number of books sold on that date. The bookid is a foreign key from the books table. It allows one to determine which book was sold.

CREATE TABLE books_sales (
saleid int (11) NOT NULL AUTO_INCREMENT,
sold_date date,
book_num TINYINT,
bookid TINYINT NOT NULL,
PRIMARY KEY (saleid)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

Insert data in the books_sales table:

insert into books_sales (saleid, sold_date, book_num, bookid) values (0, '2019-08-01', 2,1);
insert into books_sales (saleid, sold_date, book_num, bookid) values (0, '2019-08-02', 2,1);
insert into books_sales (saleid, sold_date, book_num, bookid) values (0, '2019-08-03', 4,1);
insert into books_sales (saleid, sold_date, book_num, bookid) values (0, '2019-08-04', 2,1);
insert into books_sales (saleid, sold_date, book_num, bookid) values (0, '2019-08-05', 2,1);
insert into books_sales (saleid, sold_date, book_num, bookid) values (0, '2019-08-06', 1,1);
insert into books_sales (saleid, sold_date, book_num, bookid) values (0, '2019-08-07', 3,1);
insert into books_sales (saleid, sold_date, book_num, bookid) values (0, '2019-08-08', 3,1);
insert into books_sales (saleid, sold_date, book_num, bookid) values (0, '2019-08-09', 1,1);
insert into books_sales (saleid, sold_date, book_num, bookid) values (0, '2019-08-10', 4,1);

To pull data from books and books_sales tables we need to create a join query:

select books.title, books.author, books_sales.sold_date, books.cost * books_sales.book_num as earning
from books inner join books_sales
where books.bookid= books_sales.bookid
and books.bookid=1 order by books_sales.sold_date;

The join query includes desired columns from the first table, the from keyword, the name of the first table, the inner join keyword, the name of the second table and where clause that includes criteria on how the record from the first table is related to the record of the second table and criteria which record to select. Then comes order by clause that tells the database how to sort records. In our case the records are sorted by sold date. The books.cost * books_sales.book_num as earning means that we want to know how much we earned in one day. For that we multiply the cost of a book by the number of books sold. The "as easrning" means that we want this result column titled "earning". If we did not write "as earning" then the column name would be left as "books.cost * books_sales.book_num"

We may make this query shorter by using a table alias. As an alias we can use any letter. For the books table, we use alias b and for the books_sales table we can use alias s:

select b.title, b.author, s.sold_date, b.cost * s.book_num as earning
from books b inner join books_sales s
where b.bookid=s.bookid and b.bookid=1
order by s.sold_date;

When we multiply cost 2.99 by the number of books sold, we will get a number with decimal places. To round the result to a whole number, we can use the CEIL SQL function: CEIL(b.cost * s.book_num).

The chart.php file PHP code pulls sales data from the MySQL database and draws a bar chart.
chart.php source code:

<?php

$host = 'localhost';
$db='mydb';           // your database name
$charset = 'utf8';
$userdb = 'root';        //you can use root or your user
$passdb = 'Aspirin7@';   // enter you password

$titlear=array();
$authorar=array();
$m=array();
$sdatear=array();
$c=0;
$pdo=null;

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";       //connection string

$options = array(
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES   => false,
);

try {

$pdo = new PDO($dsn, $userdb, $passdb, $options);

} catch(PDOException $e) {
echo "Could not connect to database!";
}

$sql="select
CEIL(b.cost * s.book_num) as earning
FROM
books b INNER JOIN books_sales s
ON b.bookid=s.bookid";

if(!$stmt=$pdo->query($sql))
{
echo mysql_errno() . ": ";
echo mysql_error() . "<br/>";
}
else
{
    while($row = $stmt->fetch(PDO::FETCH_NUM))
	{
	$m[$c]=$row[0];

$c++;
	}
}

$h=270; //height of the main rectangle
$w=600; //width of the main rectangle
$bw=30; // width of the chart bar
$sp=20; // space between bars

//creating a blank image
$img=imagecreatetruecolor($w, $h+3);

//creating white image color
$white=imagecolorallocate($img, 255, 255, 255);
//creating pink image color
$pink=imagecolorallocate($img, 255, 0, 255);
//creating red image color
$red=imagecolorallocate($img, 255, 0, 0);

//drawing x - axis
imagefilledrectangle ($img, 0, 0, 5, $h+3, $pink);
//drawing y - axis
imagefilledrectangle ($img, 0, 0, $w, $h+3, $pink);

//filling main rectangle with white color
imagefilledrectangle ($img, 5, 0, $w, $h-3, $white);

for($i=0;  $i<sizeof($m);  $i++)
{
$n=$i+1;
$m[$i]=$m[$i]*20;  //*20 just to make bars higher and more visible.

// drawing vertical bars with space in between
//for the first bar x1=$sp(space width), y1=$h-earning; the bar width set by x2=$bw(bar width)
//for the second bar x1=$sp(space width) + $bw(first bar width) + $sp(second space width)
//x2 for the second bar =$sp(space width) + $bw(first bar width) + $sp(second space width) + $bw(second bar width)
//for the third bar x1=$sp(space width)* 3 + $bw(bar width) * 2
//for the third bar x2=$sp(space width)* 3 + $bw(bar width) * 3
// and so on...
imagefilledrectangle ($img, ($i*$bw+$sp*$n), $h-$m[$i], ($n*$bw+$sp*$n), $h, $red);
}

//setting header type
header('Content-type: image/jpeg');

//display image
imagejpeg($img);

//destroying image
imagedestroy($img);
?>

The display_chart.php file PHP code displays sales data in the grid (table) and displays the chart image created by the chart.php file.

display_chart.php code:

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<style>
.page {
width:950px; margin-left:auto;margin-right:auto;
}
.cnt {
text-align:center;
}

.tab {
border:1px solid #aaa; width:900px;
}

.tab th {
border-left:1px solid #aaa;
background-color:#ffffcc;
}
.tab td {
border-top:1px solid #aaa;
border-left:1px solid #aaa;
}

.tab td.even {
background-color:#efefef;
}

.tab td.odd {
background-color:#fefefe;
}

h2, h3 {
color:RGB(200,0,255);
}

</style>
</head>
<body>
<div class="page">

<h2 class="cnt">August Royalties Earned Per Day</h2>
<?php

$host = 'localhost';
$db='mydb';           // your database name
$charset = 'utf8';
$userdb = 'root';        //you can use root or your user
$passdb = 'Aspirin7@';   // enter your password

$titlear=array();
$authorar=array();
$m=array();
$sdatear=array();
$costar=array();
$earningar=array();
$quantityar=array();

$c=0;
$pdo=null;

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";

$options = array(
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES   => false,
);

try {

$pdo = new PDO($dsn, $userdb, $passdb, $options);

} catch(PDOException $e) {
echo "Could not connect to database!";
}

$sql="select b.title, b.author, b.cost,  s.sold_date, s.book_num, (b.cost * s.book_num) as earning
FROM
books b INNER JOIN books_sales s
ON b.bookid=s.bookid";

if(!$stmt=$pdo->query($sql))
{
echo mysql_errno() . ": ";
echo mysql_error() . "<br/>";
}
else
{

while($row = $stmt->fetch(PDO::FETCH_NUM))
	{
	$titlear[$c]=$row[0];
	$authorar[$c]=$row[1];
	$costar[$c]=$row[2];
	$sdatear[$c]=$row[3];
	$quantityar[$c]=$row[4];
	$earningar[$c]=$row[5];

if($sdatear[$c] !="")
	$sdatear[$c]=date('m/d/Y', strtotime($sdatear[$c]));

$c++;
	}
}
if($c>0)
{

print('<table class="tab">');
print('<tr><th>Title</th><th>Author</th><th>Price</th><th>Date</th><th>Quantity</th><th>Earning</th></tr>');
	for($i=0; $i< $c; $i++)
	{

if($i % 2==0)
print('<tr><td class="even">'.$titlear[$i].'</td><td class="even">'.$authorar[$i].'</td><td class="even">'.$costar[$i].'</td><td class="even">'.$sdatear[$i].'</td><td class="even">'.$quantityar[$i].'</td><td class="even">'.$earningar[$i].'</td></tr>');
else
print('<tr><td class="odd">'.$titlear[$i].'</td><td class="odd">'.$authorar[$i].'</td><td class="odd">'.$costar[$i].'</td><td class="odd">'.$sdatear[$i].'</td><td class="odd">'.$quantityar[$i].'</td><td class="odd">'.$earningar[$i].'</td></tr>');

}
print('</table>');
print('<h3>August Royalties Earned Per Day Chart</h3>');
print('<img src="chart.php">');
}

?>
<br><br>
</div>
</body>
</html>

Computer Programming Web programming Tips