I am sorry to disappoint you, but the solution is a bit more complex than you maybe thought. As I see, @aynber already suggested it. So, if he writes an answer, it would be fair to take his answer into consideration first.
Suggestions
About Separation of Concerns:
Let me now begin by saying, that, for the future, you should familiarize yourself with the Separation of Concerns principle. In simple words, taking your code as example: always separate the code involving access to database (for fetching data, updating, etc) from the one displaying the data (e.g. the HTML part of the page).
This means that, if you need to fetch data from the database, then do it at the top of the web page, and save it in arrays. Then just use these arrays inside the HTML part of the web page, instead of some db-related functions like mysqli_query, or mysqli_fetch_assoc, or etc. For clarity, see the HTML part of the code I provided ("index.php").
A big advantage of this approach is, that you can move the whole php code from the top of the page into php functions, or class methods. The arrays will then just contain data resulting from calling these functions/methods.
The main point for all of the above statements? Juggle as you wish with the php code and the data on top of the web page, and save the results in php arrays. The arrays should, in the end, have such a structure, that the job of the HTML part of the web page will be totally simple: to just read and display the array elements.
So don't mix HTML code with db-related code. If you do it, then the code is too hard to maintain.
About printing client-side code from PHP:
Another important convention you should remember for the future is, to not print any client-side code by using php code. E.g. to not use such statements like echo "<table><tr><td>".... In this echo case, just save the content that you want to present into variables, and display them in the HTML part of the web page as desired.
About prepared statements:
If you need to execute sql statements with parameters, then use prepared statements (instead of mysqli::query in this case). They will protect your code against eventual sql injections. For completion, at the end of this answer I posted an example of index.php using prepared statements instead of mysqli::query.
Solution to question:
Steps:
Regarding the solution I prepared, it involves four steps:
- Fetch the data from the database and save it into an array (
$data).
- First, create a second array (
$formattedData). Then iterate through $data and save its items into $formattedData in such a way, that they can be very easily displayed in chosen HTML structures (div for letter, table for categories).
- Iterate through
$formattedData and append an item with null as category name for each missing category in the last category row of each letter. Sorry, I wrote here one sentence, but, if you'll read the comments in my code, you'll certainly understand better what I mean.
- Display the data in HTML part of the page by iterating through
$formattedData and reading its values.
Of course, you can optimize the php code as you wish and/or distribute it into two-three functions. Then you can just call them and assign their returned values to the $data and $formattedData variables.
Note:
If you use my connection code, don't forget to replace my db credentials with yours.
index.php
<?php
require 'Database/dbh.php';
$sql = 'SELECT
DISTINCT Kategori,
LEFT(Kategori, 1) AS Letter
FROM kategorier
ORDER BY Kategori';
$result = mysqli_query($conn, $sql);
/*
* Fetch all data at once, into an array like this:
*
* Array
* (
* [0] => Array
* (
* [Kategori] => Artiskok
* [Letter] => A
* )
*
* [1] => Array
* (
* [Kategori] => Asiatisk
* [Letter] => A
* )
*
* [2] => Array
* (
* [Kategori] => Burger
* [Letter] => B
* )
*
* [...] => [...]
*
* )
*/
$data = mysqli_fetch_all($result, MYSQLI_ASSOC);
/*
* Free the memory associated with the result. You should
* always free your result when it is not needed anymore.
*
* @link http://php.net/manual/en/mysqli-result.free.php
*/
mysqli_free_result($result);
/*
* Close the previously opened database connection. Not really needed because
* the PHP engine closes the connection anyway when the PHP script is finished.
*
* @link http://php.net/manual/en/mysqli.close.php
*/
mysqli_close($conn);
/*
* Iterate through the fetched data and save it into a new array, with a structure suited for the
* required HTML display. To each letter, a list of category rows is assigned. The new array will
* look like this, when the maximal number of categories per category row is 2:
*
* Array
* (
* [A] => Array
* (
* [0] => Array
* (
* [0] => Aoiuoiiiu
* [1] => Aqewroiuoiiu
* )
*
* [1] => Array
* (
* [0] => Artiskok
* [1] => Asiatisk
* )
*
* [2] => Array
* (
* [0] => Azkajhsdfjkh
* )
*
* )
*
* [B] => Array
* (
* [0] => Array
* (
* [0] => Bhaskdfhjkh
* [1] => Biuzutt
* )
*
* [1] => Array
* (
* [0] => Burger
* )
*
* )
*
* [...] => [...]
*
* )
*/
$formattedData = [];
// The maximal number of categories per each category row.
$maximalNumberOfCategoriesPerCategoryRow = 2;
// The number of categories per current category row.
$numberOfCategoriesPerCurrentCategoryRow = 0;
// The index of a category row in the list of all category rows assigned to a letter.
$indexOfCurrentCategoryRow = 0;
foreach ($data as $item) {
$letter = $item['Letter'];
$category = $item['Kategori'];
if (!array_key_exists($letter, $formattedData)) {
/*
* Assign an item with the current letter as key and an array as value.
* The array holds all category rows for the current letter.
*/
$formattedData[$letter] = [];
// Reset.
$indexOfCurrentCategoryRow = 0;
// Reset.
$numberOfCategoriesPerCurrentCategoryRow = 0;
}
// Append the current category to the current category row for the current letter.
$formattedData[$letter][$indexOfCurrentCategoryRow][] = $category;
// Increment.
$numberOfCategoriesPerCurrentCategoryRow++;
/*
* If the maximal number of categories per category row is reached...
*
* @see "Modulo" operator at https://secure.php.net/manual/en/language.operators.arithmetic.php
*/
if (
$numberOfCategoriesPerCurrentCategoryRow %
$maximalNumberOfCategoriesPerCategoryRow === 0
) {
// Reset.
$numberOfCategoriesPerCurrentCategoryRow = 0;
// Increment.
$indexOfCurrentCategoryRow++;
}
}
/*
* Append an item with "null" as category for each missing category in the last
* category row of each letter. The array holding the formatted data will look
* like this, when the maximal number of categories per category row is 2:
*
* Array
* (
* [A] => Array
* (
* [...] => [...]
*
* [2] => Array
* (
* [0] => Azkajhsdfjkh
* [1] => null
* )
*
* )
*
* [B] => Array
* (
* [...] => [...]
*
* [1] => Array
* (
* [0] => Burger
* [1] => null
* )
*
* )
*
* [...] => [...]
*
* )
*/
foreach ($formattedData as $letter => $categoryRows) {
$lastCategoryRow = end($categoryRows);
$lastCategoryRowKey = key($categoryRows);
$numberOfCategoriesPerLastCategoryRow = count($lastCategoryRow);
$numberOfMissingCategoriesInLastCategoryRow = $maximalNumberOfCategoriesPerCategoryRow -
$numberOfCategoriesPerLastCategoryRow;
for ($i = 0; $i < $numberOfMissingCategoriesInLastCategoryRow; $i++) {
// Append an item with "null" as category.
$formattedData[$letter][$lastCategoryRowKey][] = null;
}
}
//=====================================================================================
//@todo Just for testing: uncomment the next two lines to display the arrays on screen.
//=====================================================================================
//echo '<pre>' . print_r($data, TRUE) . '</pre>';
//echo '<pre>' . print_r($formattedData, TRUE) . '</pre>';
//=====================================================================================
?>
<!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>
<link href="custom.css" type="text/css" rel="stylesheet">
</head>
<body>
<h3>
Demo: Print a list of categories per category letter, on multiple columns.
</h3>
<?php
if ($formattedData) { /* Data exists */
foreach ($formattedData as $letter => $categoryRows) {
?>
<div class="categories-container">
<div class="letter">
<?php echo $letter; ?>
</div>
<table class="categories">
<?php
foreach ($categoryRows as $categoryRow) {
?>
<tr>
<?php
foreach ($categoryRow as $category) {
?>
<td>
<?php echo $category; ?>
</td>
<?php
}
?>
</tr>
<?php
}
?>
</table>
</div>
<?php
}
} else { /* No data */
?>
<p class="no-data">
No data found
</p>
<?php
}
?>
</body>
</html>
custom.css
body {
margin: 0;
padding: 20px;
color: #333;
}
a {
text-decoration: none;
}
.categories-container {
margin-bottom: 10px;
}
.letter {
padding: 10px;
text-align: left;
font-weight: 700;
background-color: #a0c3e5;
}
.categories {
width: 100%;
border-spacing: 1px;
border-collapse: separate;
}
.categories td {
width: 50%;
padding: 10px;
background-color: #f4f4f4;
}
.no-data {
padding: 10px;
background-color: #f4f4f4;
}
Database/dbh.php
<?php
/*
* This page contains the code for creating a mysqli connection instance.
*/
// Db configs.
define('HOST', 'localhost');
define('PORT', 3306);
define('DATABASE', 'tests');
define('USERNAME', 'root');
define('PASSWORD', 'root');
// Error reporting.
error_reporting(E_ALL);
ini_set('display_errors', 1); /* SET IT TO 0 ON A LIVE SERVER! */
/*
* 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.
$conn = mysqli_connect(HOST, USERNAME, PASSWORD, DATABASE, PORT);
Used data for testing
CREATE TABLE `kategorier` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`Kategori` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `kategorier` (`id`, `Kategori`)
VALUES
(1,'Artiskok'),
(2,'Asiatisk'),
(3,'Burger'),
(4,'Pizza'),
(5,'Asiatisk'),
(6,'Artiskok'),
(7,'Artiskok'),
(8,'Durum'),
(9,'Durum'),
(10,'Pizza'),
(11,'Chinaboks'),
(12,'Azkajhsdfjkh'),
(13,'Aoiuoiiiu'),
(14,'Aqewroiuoiiu'),
(15,'Bhaskdfhjkh'),
(16,'Biuzutt');
Result
![enter image description here]()
Additional content:
This is an example of how to fetch the data using prepared statements instead of mysqli::query. Note that I only put the data fetching code here. The rest of the code is identical with the omolog part of the above index.php page, which uses mysqli::query.
index.php
<?php
require 'Database/dbh.php';
/*
* Save the values, with which the database data will be filtered, into variables.
* These values will replace the parameter markers in the sql statement.
* They can come, for example, from a POST request of a submitted form.
*/
$letterParam1 = 'A';
$letterParam2 = 'C';
$letterParam3 = 'P';
/*
* The SQL statement to be prepared. Notice the so-called markers, e.g. the "?" signs. They
* will be replaced later with the corresponding values when using mysqli_stmt::bind_param.
*
* @link http://php.net/manual/en/mysqli.prepare.php
*/
$sql = 'SELECT
DISTINCT Kategori,
LEFT(Kategori, 1) AS Letter
FROM kategorier
WHERE
LEFT(Kategori, 1) = ?
OR LEFT(Kategori, 1) = ?
OR LEFT(Kategori, 1) = ?
ORDER BY Kategori';
/*
* Prepare the SQL statement for execution - ONLY ONCE.
*
* @link http://php.net/manual/en/mysqli.prepare.php
*/
$statement = mysqli_prepare($conn, $sql);
/*
* Bind variables for the parameter markers (?) in the SQL statement that was passed to prepare().
* The first argument of bind_param() is a string that contains one or more characters which
* specify the types for the corresponding bind variables.
*
* @link http://php.net/manual/en/mysqli-stmt.bind-param.php
*/
mysqli_stmt_bind_param($statement, 'sss'
, $letterParam1
, $letterParam2
, $letterParam3
);
/*
* Execute the prepared SQL statement. When executed any parameter markers
* which exist will automatically be replaced with the appropriate data.
*
* @link http://php.net/manual/en/mysqli-stmt.execute.php
*/
mysqli_stmt_execute($statement);
/*
* Get the result set from the prepared statement.
*
* NOTA BENE:
*
* Available only with mysqlnd ("MySQL Native Driver")! If this is not installed, then
* uncomment "extension=php_mysqli_mysqlnd.dll" in PHP config file (php.ini) and restart
* web server (I assume Apache) and mysql service. Or use the following functions instead:
* mysqli_stmt::store_result + mysqli_stmt::bind_result + mysqli_stmt::fetch.
*
* @link http://php.net/manual/en/mysqli-stmt.get-result.php
* @link https://stackoverflow.com/questions/8321096/call-to-undefined-method-mysqli-stmtget-result
*/
$result = mysqli_stmt_get_result($statement);
/*
* Fetch all data at once, into an array like this:
*
* Array
* (
* [0] => Array
* (
* [Kategori] => Artiskok
* [Letter] => A
* )
*
* [1] => Array
* (
* [Kategori] => Asiatisk
* [Letter] => A
* )
*
* [2] => Array
* (
* [Kategori] => Burger
* [Letter] => B
* )
*
* [...] => [...]
*
* )
*/
$data = mysqli_fetch_all($result, MYSQLI_ASSOC);
/*
* Free the memory associated with the result. You should
* always free your result when it is not needed anymore.
*
* @link http://php.net/manual/en/mysqli-result.free.php
*/
mysqli_free_result($result);
/*
* Close the prepared statement. It also deallocates the statement handle.
* If the statement has pending or unread results, it cancels them
* so that the next query can be executed.
*
* @link http://php.net/manual/en/mysqli-stmt.close.php
*/
mysqli_stmt_close($statement);
/*
* Close the previously opened database connection. Not really needed because
* the PHP engine closes the connection anyway when the PHP script is finished.
*
* @link http://php.net/manual/en/mysqli.close.php
*/
mysqli_close($conn);
/*
* ---------------------------------------------------------------------------------------------
* The rest of the page is identical with the omolog part of index.php, which uses mysqli::query
* ---------------------------------------------------------------------------------------------
*/
// ...
