Display mysql query results divided in dynamic numbered pages
| From: | Kpromos | Date: | Thu, 20 May 2004 13:35:06 +0000 |
| Subject: | Display mysql query results divided in dynamic numbered pages | ||
| Groups: | php.db | ||
| Request: | Send a blank email to php-db+get-34491@lists.php.net to get a copy of this message | ||
Dears, I'm new to php, so I'm not able to solve the following problem. I have a function
with a while loop that generates the results of a mysql query. I'd need to generate a flow of
the results, divided in pages as ... <Previous> 1 2 3 4 <Next> ...
The function I'm using follows .. and also the script that should generate the numbered pages.
Someone could help me in incorporating the script that follows too?
===== function displaying the results ======
function display($show_what) {
switch ($show_what) {
case "":
$sql = "select * from news2 ORDER BY date DESC, time DESC";
break;
}
display_posts($sql);
}
function display_posts($sql) {
connect();
$result = mysql_query($sql) or die(mysql_error());
?>
<table width="100%" border="0" cellpadding="5"
align="CENTER">
<tr>
<td> <hr align="RIGHT" size="1" width="100%"
noshade> </td>
</tr>
<?
while($row = mysql_fetch_array($result)) {
?> <tr><td> <?
if($last_date == $row["date"]):?>
<p class="date"><b><?echo $row["time"]?></b>
<?else:?>
<p class="date"><?echo $row["time"]?> - <b><?echo
$row["date"]?></b>
<?endif;
$cat_num = $row["category_id"];
$cat_result = mysql_query("select categories.category from news2, categories where
news2.category_id = categories.category_id and news2.category_id = '$cat_num'");
$cat = mysql_fetch_array($cat_result);
?>
<br>
................
..........
</tr>
<?
}
?>
</table>
<?
}
=== script for generating the pages ===
$table = 'news2'; // The name of your table in the database
$limit = '2'; // How many results should be shown at a time
$scroll = '10'; // How many elements to the navigation bar are shown at a time
// Get the total number of rows in a database
$query1 = mysql_query ("SELECT * FROM news2");
$numrows = mysql_num_rows ($query1);
//
if (!isset ($_GET[show])) {
$display = 1; // Change to $NUMROWS if DESCENDING order is required
} else {
$display = $_GET[show];
}
// Return results from START to LIMIT
$start = (($display * $limit) - $limit);
$query2 = mysql_query ("SELECT * FROM $table LIMIT $start,$limit");
while ($myrow = mysql_fetch_array ($query2)) {
echo "<p>".$myrow[ROW]."</p>"; // EDIT TO REFLECT YOUR RESULTS
}
//
$paging = ceil ($numrows / $limit);
// Display the navigation
if ($display > 1) {
$previous = $display - 1;
echo "<a href=\"$_SERVER[PHP_SELF]?show=1\"><< First</a> | ";
echo "<a href=\"$_SERVER[PHP_SELF]?show=$previous\">< Previous</a> |
";
}
if ($numrows != $limit) {
if ($paging > $scroll) {
$first = $_GET[show];
$last = ($scroll - 1) + $_GET[show];
} else {
$first = 1;
$last = $paging;
}
if ($last > $paging ) {
$first = $paging - ($scroll - 1);
$last = $paging;
}
for ($i = $first;$i <= $last;$i++){
if ($display == $i) {
echo "<b>$i</b> ";
} else {
echo "<a href=\"$_SERVER[PHP_SELF]?show=$i\">$i</a> ";
}
}
}
if ($display < $paging) {
$next = $display + 1;
echo "| <a href=\"$_SERVER[PHP_SELF]?show=$next\">Next ></a> |
";
echo "<a href=\"$_SERVER[PHP_SELF]?show=$numrows\">Last
>></a>";
}
//
?>
Thanks for all,
Alessandro Folghera