Php: How To Display Multiple Mysql Table Records Per Html Table Row Using Mysqli
Solution 1:
Introduction
Note: This answer details creating a many-records-to-one-row arrangement. However, this answer can be altered to provide a single-record-to-one-row arrangement.
Separating concerns will help you write cleaner code. Separating concerns will make it easier to maintain your code. Clean code is loosely coupled, unburdened by embedded dependencies. Clean code identifies its dependencies in function signatures and class constructors with the expectation that these needs will be fulfilled externally. Clean code has tight cohesion. This means functions/methods have a single task, and classes have a single objective. Clean code is often reflected in a task that has been broken down and refined (but, not always). Clean code is an ideal I strive for, but no one is perfect.
Try to think of ways to get as much SQL and PHP out of your HTML files. Interpolating variables and showing the return results of functions only can make your HTML much easier to read. Good HTML structure counts, too.
Breaking down the task of dynamically building a <table> based on the results of a SQL query is very possible. Eventually, you may decide to use CSS and divs for styling and responsiveness reasons. This code can be altered to achieve this (after all, you'd just be stacking boxes in rows).
Eventually, creating an OOP class (with custom namespaces) would be great for modularizing your code and getting the vast majority of your symbols (variable names, etc) out of the global namespace.
Before we get going: php.ini: include_path
Do you want to setup a logical directory architecture for your project?
Set the include_path inside of php.ini.
If you search your php.ini for the include_path setting, you can set this to one directory, or any group of appropriate directories. This way, you can arrange your files in directories the way you desire, and your include, include_once, require, and require_once statements will still find the files they want to import. You will not have to type absolute paths like/dir/dir/file.php or relative paths like ../../core/database.php. In both cases, you could just specify the filename.
Example:
include'file.php'; //Finds the file if it is in the include_path.require'database.php'; //Finds the file if it is in the include_path.Note: Keep library files and other pure PHP coding files (etc ...) out of the webroot, or any publicly accessible directories. Keep them logically above the webroot. Set the include_path so you do not have to keep doing ../../blah/foo all of the time.
Tasks
1) First, make a function for getting an instance of a mysqli_result object.
/**
* Returns a string, or
* throws an UnexpectedValueException, otherwise.
*/functionisString($string)
{
if (!is_string($string)) {
thrownewUnexpectedValueException("$string must be a string data type.");
}
return$string;
}
/**
* Returns a mysqli_result object, or throws an `UnexpectedValueException`.
* You can reuse this for other SELECT, SHOW, DESCRIBE or EXPLAIN queries.
*/functiongetMySQLiResult(MySQLi $db, $sql)
{
$result = $db->query(isString($sql));
if (!($resultinstanceof mysqli_result)) {
thrownewUnexpectedValueException("<p>MySQLi error no {$db->errno} : {$db->error}</p>");
}
return$result;
}
2) Second, make a function to house your SQL and invoke getMySQLiResult().
/**
* Make sure you can get the data first.
* returns a mysqli_result object.
*/functiongetInterviews(MySQLi $db)
{
$sql = "SELECT * FROM `interviews` WHERE `featured` = 1";
return getMySQLiResult($db, $sql);
}
3) Make a function for building a table data (<td></td>) cell and its content. Put all HTML or data that you need to repeat for each record in here.
/**
* Returns one database table record a table data cell.
*/functionbuildCell(array$record)
{
return"<td>\n".
'<img src="' .$record['image']. '" alt="' .$record['title']. '">' ."\n".
'<h4>' .$record['title']. '</h4>' . "\n" .
'<hr>' . "\n" .
'<p class="description">' .$record['description']. '</p>' . "\n" .
'<div id="hovers">
<a href="#" class="button">
<span class="contentbut">Read More</span>
</a>
</div>' . "\n
</td>\n";
}
4) Make a function for building table rows. Be wary of partial rows. :-)
First, a little helper function.
/**
* Returns one <tr></tr> element. Helper.
*/
function makeTr($tds)
{
return"<tr>\n" .isString($tds). "\n</tr>";
}
Second, the real deal.
functionbuildTableRow (array$tableRow)
{
return makeTr(buildCell($tableRow)) . "\n"; //Done!
}
/**
* Returns a string of multiple <tr></tr> elements,
* $maxRecords per row.
*/functionbuildTableRows(array$tableRows, $numRecords, $maxPerRow)
{
$rows = []; // Holds finished groups of <tr>s$row = ''; // Temporary variable for building row of <td>s$numCells = 0; // Number of cells currently in a row of <td>s.$numRows = (int)($numRecords / $maxPerRow); //Rows to make.$numStragglers = $numRecords % $maxPerRow; // Extra <td>s, partialRow.if ($numStragglers !== 0) { //Check if extra row is needed.$numRows += 1;
}
foreach ($tableRowsas$record)
{
$row .= buildCell($record);
++$numCells;
if ($numCells === $numRecords) { // Builds partial, last row, if needed.$rows[] = makeTr($row);
break; // Done!
}
if ($numCells === $maxPerRow) { // Builds full row.$rows[] = makeTr($row); // Save the row.$numCells = 0; // Start cell counter over.$row = ''; // Start a new row.
}
}
if(count($rows) !== $numRows) { //Verify all rows were created.thrownewRuntimeException("Rows (<tr>) for all records were not created!");
}
return implode("\n", $rows) . "\n"; //Return all rows as a string.
}
5) Make a function that spits out the HTML you need on your page. In this case, you only need one (1) substitution to appear in the HTML.
/**
* returns a set of HTML table rows (<tr></tr>) to fill a <tbody>.
* or, returns an alternative message.
*/functiondrawInterviews(MySQLi $db, $maxPerRow) //PDOisrecommened. Dependencyinjection.
{
$defaultMessage = "<tr>\n<td>There are no featured interviewers.<td>\n<\tr>\n";
try {
if (!is_int($maxPerRow) || $maxPerRow < 1) {
thrownewRangeException("The number of interviews per row must be an integer equal to 1, or greater than 1.");
}
//Make a robust connection sequence, or pass it in like above.//$db = new mysqli('host', 'user', 'password', 'dbname');$result = getInterviews($db);
$numRecords = result->num_rows;
if ($numRecords < 1) {
return$defaultMessage;
}
if ($numRecords === 1) {
return buildTableRow($result->fetch_assoc());
}
return buildTableRows($result->fetch_all(), $numRecords, $maxPerRow);
} catch (Exception$e)
//Something went wrong with the query.
error_log($e->getMessage());
} finally { //PHP 5.5+$result->free();
}
return$defaultMessage;
}
6) Now, have a good HTML <table> structure. Only one interpolation needed. Assuming three <td>s (records) per row ...
Anyway, if you want a table, put a copy of this table "skeleton" inside of academytest.php, somewhere between the header and the footer (i.e. the main <body> of the HTML document).
<table><caption>Featured Interviewers</caption><!-- Centers above table. --><thead><tr><!-- If needed. --><th>Heading1</th><!-- If needed. --><th>Heading2</th><!-- If needed. --><th>Heading3</th><!-- If needed. --></tr></thead><tfoot></tfoot><!-- If needed. Yes, it goes after <thead>. --><tbody><!-- <div id="element1"></div> --> //What goes between here?
<!-- <div id="content1"> --> //What's this?
<?= drawInterviews($db, 3); ?><!-- Dependency injection. --></tbody></table>All of this can be made more modular and reusable (object-oriented, even).
Update:
Based on your Dropbox code ...
academytest.php
1) The best thing to do is create a separate PHP file named tbodyFiller.php, or something to that effect. Put all the functions in this file, except for getInterviews() and drawInterviews() which will go into academyLibray.php, isString() which will go into library.php, and getMySQLiResult() which will go in database.php (formerly init.php).
The beginning of academytest.php should look like this:
<?php// academytest.phprequire'../../includes/library.php'; //For now, put generic helper functions here. Group them, later.require_once'../../core/database.php'; //Formerly, init.php. Put getMySQLiResult() in here.require'../../includes/academyLibrary.php'; //Put the two "interview" functions here.$db = getMySQLi(); //Many things are dependent on this being here.require'../../includes/navigation.php';
/***************** DELETE THESE LINES *****************///$sql = "SELECT * FROM interviews WHERE featured = 1";//$featured = $db->query($sql);/******************************************************/In the footer of academytest.php, close the connection to your database.
<!-- ------FOOTER------ --><?phpinclude'../../includes/footer.php';
$db->close(); //Ensures $db is available to use in the footer, if necessary.?>library.php
The beginning of library.php should look like this:
<?php// library.php/**
* Returns a string, or
* throws an UnexpectedValueException, otherwise.
*/functionisString($string)
{
if (!is_string($string)) {
thrownewUnexpectedValueException("$string must be a string data type.");
}
return$string;
}
I think init.php should be named database.php. You can learn to use the object oriented constructor (using new) sequence with error checking at your leisure. Eventually, you will want to learn PDO.
Also, make a separate file to hold your credentials. Right now, this is better than hard coding them in to the getMySQLi() function.
dbCreds.php
<?php// dbCreds.php$host = ''; //IP or DNS name: string.$username = ''; //Your account: string.$passwd = ''; //The password: string.$dbname = ''; //The database you want to work with: string.//*************************************************************************//$port = '3306'; //Un-comment and change only if you need a differnt TCP port. //Also, you would need to add a $port as your last argument in new MySQLi(),//in the getMySQLi() function.database.php
<?php// database.php/**
* Returns a mysqli_result object, or throws an `UnexpectedValueException`.
* You can reuse this for other SELECT, SHOW, DESCRIBE or EXPLAIN queries.
*/functiongetMySQLiResult(MySQLi $db, $sql)
{
$result = $db->query(isString($sql));
if (!($resultinstanceof mysqli_result)) {
thrownewUnexpectedValueException("<p>MySQLi error no {$db->errno} : {$db->error}</p>");
}
return$result;
}
functiongetMySQLi() //Thiscanbeimproved, butthat'snottheissuerightnow.
{
require_once'dbCreds.php'; //Choose your own file name. Do not put in public directory.$db = new mysqli($host, $username, $passwd, $dbname); //$port would be next.if(!($dbinstanceof MySQLi)){
thrownewUnexpectedValueException("A MySQLi object was not returned during your connection attempt.");
}
if(isset($db->connect_error)){
thrownewUnexpectedValueException("The database connection was not established. {$db->connect_errno} : {$db->connect_error}");
}
return$db
} //Using the object form of MySQLi object has side benenfits.academyLibrary.php
The beginning of academyLibrary.php should look like this:
<?php// academyLibrary.phprequire'tbodyFiller.php'; //Put all but four functions in here.functiongetInterviews(MySQLi $db)
{
$sql = "SELECT * FROM `interviews` WHERE `featured` = 1";
return getMySQLiResult($db, $sql);
}
/**
* Comments //etc...
*/functiondrawInterviews(MySQLi $db, $maxPerRow)
{
//The code, etc ...
}
If you have not configured your include_path inside of the php.ini, make sure academyLibrary.php and tbodyFiller.php are located in the same directory.
navigation.php
We are going to replace the procedural forms of working with MySQL with the object-oriented ones. This is simple, and we do not need to change much. I will not replace your loops or queries at this time, but my advice is to get out of the habbit of putting PHP loops and SQL directly in your HTML. Find a way to use a function or method, like I did for the table's in academytest.php. By this time, you should have enough examples. :-)
Refactoring
I took some time to refactor this file. Here is what I have at the top. Once again, you may wish to create another PHP file, say navLibrary.php, and put these functions into it. In that case you would replace all the functions seen below with one line, require 'navLibrary.php';. Naturally, this way of importing code may depend on configuring your include_path inside of the php.ini.
<?php// navigation.php functiongetPqueryMainData(MySQLi $db)
{
$sql = "SELECT * FROM `mainmenu` WHERE `parent` = 0"; //pqueryMainreturn getMySQLiResult($db, $sql);
}
functiongetPqueryData(MySQLi $db)
{
$sql = "SELECT * FROM `categories` WHERE `parent` = 0"; //pqueryreturn getMySQLiResult($db, $sql);
}
functiongetCquery1Data(MySQLi $db)
{
$sql = "SELECT * FROM `categories` WHERE `parent` = 1"; //cquery1return getMySQLiResult($db, $sql);
}
functiongetCquery2Data(MySQLi $db, $int)
{
$sql = "SELECT * FROM `categories` WHERE `parent` = '$int'"; //cquery2return getMySQLiResult($db, $sql);
}
//Consider doing at most 3 queries early on.//Consider using better names for your variables.//I get that 'p' means "primary", and 'c' means "child", but come on. :-) $pqueryMain = getPqueryMainData($db);
$pquery = getPqueryData($db);
$cquery1 = getCquery1Data($db);
$cquery2 = null;
Solution 2:
There are few issues with your code, such as:
- You're creating a new table in each iteration of
while()loop. You're using the same div ids,
element1andcontent1for all of your table rows. Useclassinstead. However, based on your question,I would like every new element to appear one next to the other.
You can use the
idattributes there, but you have to take the entire<table> ... <div id="content1">and<div id="hovers"> ... </table>out of thewhile()loop. And of course, changeid="hovers"toclass="hovers"and the associated CSS accordingly.
So the solution code would be like this:
<table><tr><divid="element1"></div><divid="content1"><?phpwhile($product = mysqli_fetch_assoc($featured)){
?><td><imgsrc="<?=$product['image']; ?>"alt="<?=$product['title']; ?>"><h4><?=$product['title']; ?></h4><hr><pclass="description"><?=$product['description']; ?></p><!--------BUTTON 3--------><divclass="hovers"><ahref="#"class="button"><spanclass="contentbut"> Read More</span></a></div></td><?php
}
?></div></tr></table>Update(1):
From OP's comment,
I am trying to have 3 interviews in a row and several rows with interviews.
The solution would be to have three table cells in each row,
<table><divid="element1"></div><divid="content1"><tr><?php$counter = 1;
while($product = mysqli_fetch_assoc($featured)){
if($counter % 4 == 0){
echo'</tr><tr>';
}
++$counter;
?><td><imgsrc="<?=$product['image']; ?>"alt="<?=$product['title']; ?>"><h4><?=$product['title']; ?></h4><hr><pclass="description"><?=$product['description']; ?></p><!--------BUTTON 3--------><divclass="hovers"><ahref="#"class="button"><spanclass="contentbut"> Read More</span></a></div></td><?php
}
?></tr></div></table>
Post a Comment for "Php: How To Display Multiple Mysql Table Records Per Html Table Row Using Mysqli"