3 methods to tweak your MySQL database result pages

Most people use PHP and MySQL together and there are several reasons to do so: It’s easy, fast and powerful! If you create a MySQL database result, the data is not formatted and looks ugly. The following functions or code examples will help you to create better looking MySQL database result pages.

Present a database result in columns

Presenting data into rows or columns is a standard action realized with a simple loop through your record set. Often the data have to be presented in a different order. This example shows a table where the results are provided into columns and rows. Each row holds a number of records from your database; if the number of columns is reached a new row will be used. If there are no more records empty cells will be created and the dynamic creation of cells and rows is ended after the last row has reached the maximum number of columns. Check our PHP Nested Loop Demo.

<?php
// this is an example query from the link page on my website, use your own data and don't forget to change the names of the results inside the table below
$db = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
$query = "SELECT title, descr, link FROM linksite ORDER BY vote DESC";
$result = $db->query($query);
$total_records = $result->num_rows; // the number of records in your result set
while($row = $result->fetch_array()) { // store all records in an numbered array
 $datarows[] = $row;
}

$num_cols = 3; // the number of columns 
$num_rows = ceil($total_records / $num_cols); // the number of rows
$num = 0; // don't change this value, this is the first number of each record inside a record set

echo "<table>\n";
// next the loop for the table rows
for ($rows = 0; $rows < $num_rows; $rows++) {
 echo "<tr>\n"; 
 // this is the loop for the table columns
 for ($cols = 0; $cols < $num_cols; $cols++) {
 if ($num < $total_records) { // show records if available (reduce by one because the first record is no. "0" (zero)
 // first create variables with the values of the current record 
 $titel = $datarows[$num]['title']; // you have to chenge the names here to fit your own sql statement
 $description = $datarows[$num]['descr'];
 $url = $datarows[$num]['link'];
 echo "<td><b>".$titel."</b><br>".$description."<i><a href=\"".$url."\">Visit here!</a></i><td>\n";
 } else { // show an empty cell
 echo "<td>&nbsp;</td>\n";
 }
 $num++; // raise the number by one for the next record
 }
 echo "</tr>\n"; // there are no more cols in this row, close the table row tag 
}
echo "</table>\n"; // end of the region = closing tag for the table element

Show database rows in different colors

This small PHP code snippet is an example how to create alternative row colors for database/array data which have to be presented in HTML. The only “special” thing what happens is that background color changes upon the result of a modulus operator. Check the MySQL alternative colored rows demo for the more advanced example. I used for the demo CSS pseudo classes and also JavaScript events to create a nice hover effect.

<?php
//example sql and mysql result
$db = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
$sql = "SELECT titel, url, description FROM your_table LIMIT 0, 10";
$result = $db->query($sql);

// show this result in paragraphs
$colorRow = 1;
while ($obj = $result->fetch_object())) {
 $rowStyle = ($colorRow % 2 == 1) ? "#99FF00" : "#99FF99"; // this is the magic: the modulus operator is used to switch the colors
 echo "\n <p style=\"background-color:".$rowStyle.";\"><a href=\"".$obj->url."\" target=\"_blank\"><b>".$obj->titel."</b></a><br>";
 echo "\n ".$obj->description."</p>";
 $colorRow++;
}
$result->close(); // at last free the result

Paginate your MySQL database result

Use this small function to create page links for your MySQL database result set. This PHP function is all you need to create forward / backward links (paging) for the records in a database result set. The setup is very easy, just call the function at the place where the links to have appear. Then create a variable for the current limit inside your SQL and create a variable with the total number of records in your result set. The function takes care about an existing query string and creates the whole html code you need for the page navigation.

<?php
// example variable 
$sql_limit = (isset($_GET['limit'])) ? $_GET['limit'] : 0;

function navigation_links($curr_limit, $num_records, $limit_val, $limit_var = "limit", $next = "next >", $prev = "< prev", $seperator = "|") {
 // rebuild query string
 if (!empty($_SERVER['QUERY_STRING'])) {
 $parts = explode("&", $_SERVER['QUERY_STRING']);
 $newParts = array();
 foreach ($parts as $val) {
 if (stristr($val, $limit_var) == false) array_push($newParts, $val);
 }
 $qs = (count($newParts) > 0) ? "&".implode("&", $newParts) : "";
 } else {
 $qs = "";
 }
 $navi = "";
 if ($curr_limit > 0) {
 $navi .= "<a href=\"".$_SERVER['PHP_SELF']."?".$limit_var. "=".($curr_limit-$limit_val).$qs."\">".$prev."</a>";
 }
 $navi .= " ".$seperator." ";
 if ($curr_limit < ($num_records-$limit_val)) {
 $navi .= "<a href=\"".$_SERVER['PHP_SELF']."?".$limit_var. "=".($curr_limit+$limit_val).$qs."\">".$next."</a>";
 }
 return trim($navi, " | ");
}
// example placing links ($num_all is the value of all records in you result set)
echo navigation_links($sql_limit, $num_all, 10);

One thought on “3 methods to tweak your MySQL database result pages”

  1. Thanks for sharing, is there an easy way to combine the function for the vertical arranged result set with the colored rows?

Leave a Reply

Your email address will not be published. Required fields are marked *