Some principles to follow:
- Avoid writing php code which contains html tags in it.
- Don't mix the php code which fetches db data with the html code. In my code you'll notice that I fetched and processed the db data only on the upper side of the web page. So completely separated from the html code. In the html code I am using only the arrays. Why is this important? Because, for example, if a web designer should customize the design of your pages, he should be able to change the html codes without beeing obliged (by your code) to learn any database access commands. Another reason is, that, at some point, you will probably want to separate the data access code from the html code. You can only do this without mixing them. So, in short, the html code should not do any processing, but only reading operations (from an array, from variables).
- In general, avoid using
while, or do while constructs. They introduce a big risc of infinite loops, which can break your apps, or systems. In case of the while loops used in mysqli, it would be ok to use them, but with caution. Make sure that no infinite loops can be activated.
- Consider each request made by your codes to a database as an expensive operation. For example, if you are fetching a list with 500 categories, and, for each category, you are making a new request to database to fetch the corresponding links list, then you'll "overwhelm" your web server and database.
- Consider applying prepared statements instead of directly querying the database with
query. Make yourself a habit from always using them. They are very important, because with them you are avoiding the so-called mysql injections.
- Consider naming the tables with plurals. Like "categories". And don't hesitate to give good understandable database/table/column names. Use the form "this_is_the_name" for them (with separating underscore and small characters). In php use the "camelCase" syntax for the php variable/function/method names. Like
$mysqlConnection. The id's of the html controls should be camelCase too: "thisIsAnInputId". And the css classes like this: "this-is-a-div-class".
- Write
echo '<table width="507" border="1">';. It looks better, isn't it? I am using the single quotes almost everywhere, instead of double quotes. In rare occasions I must escape the single quote character with a backslash, like this: echo 'I don\'t know';
Some good resources:
index.php:
<?php
require 'db/connect.php';
/*
* The SQL statement. The external values are passed directly into it.
* Therefore, the code is open to SQL injections. In order to avoid
* them, an sql statement receiving external values should always be
* prepared for execution.
*
* @link http://php.net/manual/en/mysqli.prepare.php
*/
$sql = 'SELECT
c.id AS categoryId,
c.cat,
c.type,
l.id AS linkId,
l.url,
l.links
FROM links AS l
INNER JOIN category AS c ON l.catID = c.id';
/*
* Performs a query on the database.
*
* Returns FALSE on failure [<< and raises warnings, fatal errors, exceptions, etc >>].
* For successful SELECT, SHOW, DESCRIBE or EXPLAIN queries mysqli_query() will return a
* mysqli_result object. For other successful queries will return TRUE.
*
* @link https://secure.php.net/manual/en/mysqli.query.php
*/
$result = $db->query($sql);
/*
* Fetch the data into an array like this:
*
* Array
* (
* [0] => Array
* (
* [categoryId] => 1
* [cat] => Platforms
* [type] =>
* [linkId] => 1
* [url] => #
* [links] => CSI (Internet Explorer)
* )
*
* [1] => Array
* (
* [categoryId] => 1
* [cat] => Platforms
* [type] =>
* [linkId] => 2
* [url] => #
* [links] => Customer view of Service Cloud
* )
*
* [...] => [...]
*
* [5] => Array
* (
* [categoryId] => 2
* [cat] => Engineering
* [type] =>
* [linkId] => 6
* [url] => #
* [links] => Check Returned OPTs
* )
*
* [6] => Array
* (
* [categoryId] => 2
* [cat] => Engineering
* [type] =>
* [linkId] => 7
* [url] => #
* [links] => Eng Links
* )
*
* [...] => [...]
*
* )
*/
$data = $result->fetch_all(MYSQLI_ASSOC);
/*
* Iterate through the fetched data and save it into a new array, with
* a structure suited for the required display. The new array looks like this:
*
* Array
* (
* [1] => Array
* (
* [categoryName] => Platforms
* [categoryType] => red
* [links] => Array
* (
* [0] => Array
* (
* [1] => Array
* (
* [linkUrl] => #
* [linkLinks] => CSI (Internet Explorer)
* )
*
* [2] => Array
* (
* [linkUrl] => #
* [linkLinks] => CSI (Backup link - Don't have link)
* )
*
* )
*
* [1] => Array
* (
* [3] => Array
* (
* [linkUrl] => #
* [linkLinks] => Customer view of Service Cloud
* )
*
* [4] => Array
* (
* [linkUrl] => #
* [linkLinks] => Service Cloud
* )
*
* )
*
* [2] => Array
* (
* [5] => Array
* (
* [linkUrl] => #
* [linkLinks] => Open to Suggestions
* )
*
* )
*
* )
*
* )
*
* [2] => Array
* (
* [categoryName] => Engineering
* [categoryType] => blue
* [links] => Array
* (
* [0] => Array
* (
* [6] => Array
* (
* [linkUrl] => #
* [linkLinks] => Check Returned OPTs
* )
*
* [7] => Array
* (
* [linkUrl] => #
* [linkLinks] => EMC Caller
* )
*
* )
*
* [1] => Array
* (
* [8] => Array
* (
* [linkUrl] => #
* [linkLinks] => Eng Links
* )
*
* [9] => Array
* (
* [linkUrl] => #
* [linkLinks] => Eng RCA Database
* )
*
* )
*
* [2] => Array
* (
* [10] => Array
* (
* [linkUrl] => #
* [linkLinks] => OPT
* )
*
* [11] => Array
* (
* [linkUrl] => #
* [linkLinks] => Surge (Internet Explorer)
* )
*
* )
*
* )
*
* )
*
* )
*/
$formattedData = [];
// The number of items (maximal 2) per each "links row" item.
$numberOfItemsPerLinksRow = 0;
// The index of a "links row" item.
$indexOfCurrentLinksRow = 0;
foreach ($data as $item) {
// Extract category data.
$categoryId = $item['categoryId'];
$categoryName = $item['cat'];
$categoryType = $item['type'];
// Add a category item if not already exist, with the category id as the item's key.
if (!array_key_exists($categoryId, $formattedData)) {
$formattedData[$categoryId] = [];
$formattedData[$categoryId]['categoryName'] = $categoryName;
$formattedData[$categoryId]['categoryType'] = $categoryType;
// In this category item add an array to hold the list of links items.
$formattedData[$categoryId]['links'] = [];
// Reset.
$indexOfCurrentLinksRow = 0;
// Reset.
$numberOfItemsPerLinksRow = 0;
}
// Extract link data.
$linkId = $item['linkId'];
$linkUrl = $item['url'];
$linkLinks = $item['links'];
// Add the details of the link as an item in the "links row" item. Notice the link id as key.
$formattedData[$categoryId]['links'][$indexOfCurrentLinksRow][$linkId] = [
'linkUrl' => $linkUrl,
'linkLinks' => $linkLinks,
];
// Increment.
$numberOfItemsPerLinksRow++;
if ($numberOfItemsPerLinksRow % 2 === 0) {
// Reset.
$numberOfItemsPerLinksRow = 0;
// Increment.
$indexOfCurrentLinksRow++;
}
}
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />
<meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=yes" />
<meta charset="UTF-8" />
<!-- The above 3 meta tags must come first in the head -->
<title>Demo</title>
<style type="text/css">
body {
margin: 0;
padding: 20px;
color: #333;
}
a {
text-decoration: none;
}
.category {
padding: 10px;
margin-bottom: 10px;
text-align: left;
font-weight: 700;
border: 1px solid #ccccff;
background-color: #f3f8fd;
}
.links {
width: 100%;
border-spacing: 1px;
border: 1px solid #fafafa;
}
.links td {
width: 50%;
padding: 10px;
background-color: #fafafa;
}
.links td a {
color: #0033ff;
font-weight: 700;
background-color: #f8f8f8;
}
.no-data {
padding: 10px;
margin-bottom: 10px;
border: 1px solid #ddd;
background-color: #f4f4f4;
}
</style>
</head>
<body>
<h3>
Categories
</h3>
<?php
if ($formattedData) { // Data exists.
foreach ($formattedData as $categoryId => $categoryItem) {
?>
<p class="category">
<?php echo $categoryItem['categoryName']; ?>
</p>
<table class="links">
<?php
$links = $categoryItem['links'];
foreach ($links as $linksRow) {
?>
<tr>
<?php
foreach ($linksRow as $linkId => $linkItem) {
?>
<td>
<a href="<?php echo $linkItem['linkUrl']; ?>">
<?php echo $linkItem['linkLinks']; ?>
</a>
</td>
<?php
}
// If there is no second link item in this "links row" item, then create an empty cell.
$numberOfItemsPerLinksRow = count($linksRow);
if ($numberOfItemsPerLinksRow === 1) {
?>
<td>
</td>
<?php
}
?>
</tr>
<?php
}
?>
</table>
<?php
}
} else { // No data.
?>
<p class="no-data">
No data.
</p>
<?php
}
?>
</body>
</html>
db/connect.php:
Don't forget to change the database credentials.
<?php
// Db configs.
define('HOST', 'localhost');
define('PORT', 3306);
define('DATABASE', 'tests');
define('USERNAME', 'root');
define('PASSWORD', 'root');
/*
* Enable internal report functions. This enables the exception handling,
* e.g. mysqli will not throw PHP warnings anymore, but mysqli exceptions
* (mysqli_sql_exception).
*
* MYSQLI_REPORT_ERROR: Report errors from mysqli function calls.
* MYSQLI_REPORT_STRICT: Throw a mysqli_sql_exception for errors instead of warnings.
*
* @link http://php.net/manual/en/class.mysqli-driver.php
* @link http://php.net/manual/en/mysqli-driver.report-mode.php
* @link http://php.net/manual/en/mysqli.constants.php
*/
$mysqliDriver = new mysqli_driver();
$mysqliDriver->report_mode = (MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
/*
* Create a new db connection.
*
* @see http://php.net/manual/en/mysqli.construct.php
*/
$db = new mysqli(HOST, USERNAME, PASSWORD, DATABASE, PORT);
Used definition and data for table "category":
CREATE TABLE `category` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`cat` varchar(100) DEFAULT NULL,
`type` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO `category` (`id`, `cat`, `type`)
VALUES
(1, 'Platforms', 'red'),
(2, 'Engineering', 'blue');
Used definition and data for table "links":
CREATE TABLE `links` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`links` varchar(100) DEFAULT NULL,
`url` varchar(100) DEFAULT NULL,
`catID` int(11) unsigned DEFAULT NULL,
`type` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `catID` (`catID`),
CONSTRAINT `links_ibfk_1` FOREIGN KEY (`catID`) REFERENCES `category` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
INSERT INTO `links` (`id`, `links`, `url`, `catID`, `type`)
VALUES
(1, 'CSI (Internet Explorer)', '#', 1, NULL),
(2, 'CSI (Backup link - Don\'t have link)', '#', 1, NULL),
(3, 'Customer view of Service Cloud', '#', 1, NULL),
(4, 'Service Cloud', '#', 1, NULL),
(5, 'Open to Suggestions', '#', 1, NULL),
(6, 'Check Returned OPTs', '#', 2, NULL),
(7, 'EMC Caller', '#', 2, NULL),
(8, 'Eng Links', '#', 2, NULL),
(9, 'Eng RCA Database', '#', 2, NULL),
(10, 'OPT', '#', 2, NULL),
(11, 'Surge (Internet Explorer)', '#', 2, NULL);
Output:
