Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
PHP snippets to tweak your MySQL database results https://www.tutdepot.com/tweak-mysql-database-result-pages/
<?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
<?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
<?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);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.