<?php
/*
Script_name : Northwind.php
Source_code : http://www.thaiall.com/perlphpasp/source.pl?9141
Version 1.2563-10-24
###########################
- Change comment that it was prepared for 1 line coding
- Use function_exists for mysql_connect
- Test mysqli() on PHP 7.1 and PHP 5.6
- Test mysql_connect() on PHP 5.6 because it failed on PHP 7.1
- Can use php=7 and php=5 to control testing with XAMPP from https://www.apachefriends.org
- Northwind.mdb for mysql in SQL format was shared in http://www.thaiall.com/mysql/northwindwithsqlfile.zip
- Use "mytable" from http://www.thaiall.com/web2/rsp62.css
- Can read about subquery at http://www.dofactory.com/sql/subquery
- Test SQL command - online on https://www.w3schools.com/sql/sql_join_inner.asp
- Test SQL command - online on https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all
- Test SQL command - offline on http://www.alexnolan.net/software/mdb_viewer_plus.htm (Copyright 2004 - 2013)
###########################
Requirement before start this script
1. Download : http://www.thaiall.com/mysql/northwindwithsqlfile.zip
2. ก่อน import ให้สร้าง Database : northwind เพื่อนำเข้าฐานข้อมูลที่กำหนด
3. Import northwindfromphpmyadmin.sql in to MySQL
4. Orders (830), Order Details (2155), Customers(91), Employees(9), Products (77), Categories (8), Suppliers (29), Shippers (3)
*/
/* Section 1 : Configuration */
$host = "localhost";
$uname = "root";
$upass = "";
$db = "northwind";
/* Section 2 : Variable */
$maxField = 10;
$lineperpage = 25;
$linenumber = true;
if(isset($_GET["p"])) $currentpage = $_GET["p"]; else $currentpage = 1;
if((int)phpversion() >= 7) $php7 = true; else $php7 = false;
if(isset($_GET["php"])) { if($_GET["php"] == "7") $php7 = true; else $php7 = false; }
$mytable = "<style>
.mytable { margin-left:auto; margin-right:auto; }
.mytable td {padding:3px; border-bottom: 1px solid #dddddd; }
.mytable tr:nth-child(even) {background-color: #ddffdd}
.mytable tr:nth-child(odd) {background-color: #ffffdd}
.mytable tr:hover {background-color: #ddddff}
</style>";
/* Section 3 : Connection */
if($php7) {
$connect = new mysqli($host, $uname, $upass, $db);
if ($connect->connect_error) die("Connection failed: " . $connect->connect_error);
} else {
if (function_exists('mysql_connect')) {
if(!$connect = mysql_connect($host, $uname, $upass)) die("Connect failed : ");
} else {
die("function mysql_connect : not exist in PHP");
}
}
/* Section 4 : SQL Command */
$sql = "select * from customers";
$sql_type = 0; /* SQL type */
if(isset($_GET["t"])) {
$sql_type = $_GET["t"];
switch ($_GET["t"]) {
/* 1 - start here */
case "1": $sql="
select * from orders
"; break;
/* 2 - if we have space in table name, it need to use ascii 96 */
case "2": $sql="
select * from `order details`
"; break;
/* 3 - selelct record in range of data */
case "3": $sql="
select * from products limit 0,5
"; break; /* 0 = start at first record, 5 = total record */
/* 4 - order have desc and asc */
case "4": $sql="
select * from products order by productname desc
"; break; /* desc = descending and asc = ascending */
/* 5 - where = < > */
case "5": $sql="
select * from orders where employeeid = 4
"; break; /* criteria = where */
/* 6 - another sample of where */
case "6": $sql="
select * from orders where freight > 100 and freight < 1000
"; break; /* freight = ค่าขนส่ง */
/* 7 - like = almost same */
case "7": $sql="
select * from products where QuantityPerUnit like '%pieces%'
"; break; /* % = The percent sign represents zero, one, or multiple characters */
/* 8 - where to join table */
case "8": $sql="
select `order details`.*, products.* from `order details`,products
where `order details`.productid = products.productid
"; break;
/* 9 - inner join */
case "9": $sql="
select `order details`.*, products.* from
(`order details` inner join products on `order details`.productid = products.productid)
"; break;
/* 10 - group and alias */
case "10": $sql="
select orderid, count(orderid) as cnt from `order details` group by orderid
"; break;
/* 11 - function sum */
case "11": $sql="
select orderid, sum(quantity * unitprice) as total
from `order details` group by orderid
"; break;
/* 12 - inner join and where */
case "12": $sql="
select `order details`.*, products.*
from (`order details` inner join products on `order details`.productid = products.productid)
where `order details`.unitprice > 100
"; break;
/* 13 - inneer join and where to merge table */
case "13": $sql="
select orders.customerid, `order details`.*, products.*
from orders, (`order details` inner join products on `order details`.productid = products.productid)
where `order details`.unitprice > 100 and orders.orderid = `order details`.orderid
"; break;
/* 14 - inner join 2 way */
case "14": $sql="
select orders.customerid, `order details`.*, products.*
from (orders inner join `order details` on orders.orderid = `order details`.orderid
inner join products on `order details`.productid = products.productid)
where `order details`.unitprice > 100
"; break; /* testing : [pass] in phpmyadmin but [fail] in MDBviewerplus */
/* 15 - function sum in group by */
case "15": $sql="
select orderid, sum(quantity * unitprice) as total from `order details`
group by orderid
having sum(quantity * unitprice) > 1000
"; break;
/* 16 - subquery of select */
case "16": $sql="
select * from `order details`
where productid in
(select productid from products where unitsinstock = 0) order by productid
"; break;
/* 17 - inner join to count >0 and no data of number 6 */
case "17": $sql="
select employees.employeeid, count(orders.orderid) as cnt_orderid
from (employees inner join orders on employees.employeeid = orders.employeeid)
where orders.shipcountry ='Spain'
group by employees.employeeid
"; break;
/* 18 - subquery and inner join and count >0 and no data of number 6 */
case "18": $sql="
select * from employees where employeeid in
(select employees.employeeid from
(employees inner join orders on employees.employeeid = orders.employeeid)
where orders.shipcountry ='Spain'
group by employees.employeeid)
"; break;
/* 19 - left join and subquery get >=0 and include number 6 */
case "19": $sql="
select emp_left.employeeid, emp_right.cnt_order
from employees emp_left left join
(select orders.employeeid, count(orders.orderid) as cnt_order from orders
where orders.shipcountry ='Spain' group by orders.employeeid) emp_right
on emp_right.employeeid= emp_left.employeeid
"; break;
}
}
/* Section 5 : Display data */
if(isset($_GET["sql"]) && $_GET["sql"] == "show") die($sql);
if($php7) {
$result = $connect->query($sql);
if ($result->num_rows == 0) die("Query : failed<br/>" . $sql);
$numField = mysqli_num_fields($result);
if($numField < $maxField) $maxField = $numField;
echo $mytable . "<table class='mytable'><tr style='background-color:black;color:white;'>";
if($linenumber) echo "<td>no.</td>";
$i = 0;
while ($fieldinfo=mysqli_fetch_field($result)) {
if ($i++ < $maxField) echo "<td style='text-align:center'>" . $fieldinfo->name . "</td>";
}
echo "</tr>";
$cntrec=1;
$totalRec = mysqli_num_rows($result);
while ($row = mysqli_fetch_array($result)) {
if ($cntrec >=firstrec($totalRec,$lineperpage,$currentpage) && $cntrec <=lastrec($totalRec,$lineperpage,$currentpage)) {
echo "<tr>";
if($linenumber) echo "<td>$cntrec</td>";
for ($i=0; $i<$maxField ; $i++ ) { echo "<td>$row[$i]</td>"; }
echo "</tr>";
}
$cntrec++;
}
echo "</table><div style='margin-left:auto;margin-right:auto;width:720px;text-align:center;background-color:#ffdddd'>";
for($i=1;$i<=totalpage($totalRec,$lineperpage);$i++) {
if ($i == $currentpage)
echo "$i : ";
else
echo "<a href='?t=$sql_type&p=$i'>$i</a> : ";
}
echo "Total $totalRec records";
$connect->close();
} else {
if (!$result=mysql_db_query($db,$sql)) die("Query : failed<br/>".$sql);
$numField = mysql_num_fields($result);
if($numField < $maxField) $maxField = $numField;
echo "<style>table,th,td {border: 1px solid #dddddd;border-spacing:1px}</style>";
echo "<table style='border-style:solid;border-width:2px;border-color:blue'><tr>";
if($linenumber) echo "<td>no.</td>";
for ($i=0; $i<$maxField ; $i++ ) {
echo "<td style='background-color:#ffffdd;text-align:center'>" . mysql_field_name($result, $i) . "</td>";
}
echo "</tr>";
$cntrec=1;
$totalRec = mysql_num_rows($result);
while ($row = mysql_fetch_array($result)) {
if ($cntrec >=firstrec($totalRec,$lineperpage,$currentpage) && $cntrec <=lastrec($totalRec,$lineperpage,$currentpage)) {
echo "<tr>";
if($linenumber) echo "<td>$cntrec</td>";
for ($i=0; $i<$maxField ; $i++ ) { echo "<td>$row[$i]</td>"; }
echo "</tr>";
}
$cntrec++;
}
/* while ($row = mysql_fetch_assoc($result)) { echo $row["CustomerID"]; } */
/* while ($row = mysql_fetch_object($result)) { echo $row->{"CustomerID"} หรือ $row->CustomerID } */
echo "</table><div style='margin-left:auto;margin-right:auto;width:720px;text-align:center;background-color:#dddddd'>";
for($i=1;$i<=totalpage($totalRec,$lineperpage);$i++) {
if ($i == $currentpage)
echo "$i : ";
else
echo "<a href='?t=$sql_type&p=$i'>$i</a> : ";
}
echo "Total $totalRec records";
mysql_close($connect);
}
echo '<br/><a href="?t=1">Table:Orders</a> : <a href="?t=0&sql=show">SQL show</a> : <a href="?t=1&php=5">PHP5</a> : <a href="?t=1&php=7">PHP7</a></div>';
/* Section 6 : Page number control */
/*
Sample from http://www.thaiall.com/php/indexo.html#short47
$totalrec = Total record in table such as 60
$lpp = Line per page || Record per page to display such as 25
$page = Page number such as (1:1-25, 2:26-50, 3:51-60)
*/
function totalpage($totalrec,$lpp) { return ceil($totalrec / $lpp); }
function firstrec($totalrec,$lpp,$page) { return (($lpp * ($page - 1) + 1) > $totalrec ? 1 : ($lpp * ($page - 1) + 1)); }
function lastrec($totalrec,$lpp,$page) { return (($lpp * $page) > $totalrec ? $totalrec : ($lpp * $page)); }
?>