Paginating Query results...Problems

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
invision
Forum Newbie
Posts: 12
Joined: Wed Feb 01, 2006 4:29 am
Location: Scotland

Paginating Query results...Problems

Post by invision »

Hello,

I've made the following code for a basic web site I'm working on.

It displays 10 registered users, and as my site has 11, it also displays 1 | 2 at the bottom as links, but it doesn't like when I select '2' from the num_pages , it gives me the following errors :
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/students/projects/p002/public_html/admin/userlist.php on line 66

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home/students/projects/p002/public_html/admin/userlist.php on line 80
System Info :
PHP Version 4.3.4
MySQL 4.0.18
Apache-AdvancedExtranetServer/2.0.48

Here's my code, can anyone spot any problems ?

Code: Select all

<?php # - userlist.php

// Script to display list of users on Pictures of Scotland, if logged in as 'admin', allow for editing/deleting.

require_once('../../mysql_connect.php');

include ('../includes/aheader.html');

if (($_SESSION['user_id']=='1')) {

// Page Header
echo '<h2>Registered Users</h2>';

require_once ('../../mysql_connect.php'); // Connect to database.

// Number of records to show per page. Tens a good number.
$display = 10;

// Determine the number of pages.
if (isset($_GET['np'])) { // If it has already been determined. 'np' is new page.

	$num_pages = $_GET['np'];

} else { // Got to determine.

	// Count the number of records in users table.
	$query = "SELECT COUNT(*) FROM users ORDER BY registration_date ASC";
	$result = mysql_query ($query);
	$row = mysql_fetch_array ($result, MYSQL_NUM);
	$num_records = $row[0];

	// Do calculation to add up number of pages
	if ($num_records > $display) {
	$num_pages = ceil ($num_records/$display); // takes the next highest integer from dividing both.
} else {
	$num_pages = 1;
}

} // End of 'np' IF statement.

// Determine where to start returning results from the db.
if (isset($_GET['s'])) {
	$start = $_GET['id'];

} else {
	$start = 0;
}

// Make the query to retrieve users surname and first name.
$query = "SELECT last_name, first_name, DATE_FORMAT(registration_date, '%M %d, %Y') AS dr, user_id FROM users ORDER BY registration_date ASC LIMIT $start, $display";
$result = mysql_query ($query); // Run the query.

// Table header
echo '<table align="center" cellspacing="0" cellpadding="5">
<tr>
	<td align="left"><b>Edit</b></td>
	<td align="left"><b>Delete</b></td>
	<td align="left"><b>Last Name</b></td>
	<td align="left"><b>First Name</b></td>
	<td align="left"><b>Date Registered</b></td>
</tr>
';

// Fetch and print the records from the users table.
$bg = '#eeeeee'; // Sets the background colour.
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$bg = ($bg=='#eeeeee' ? '#ffffff' : '#eeeeee'); // Switches the background colour between the two.
echo '<tr bgcolor="' . $bg . '">
	<td align="left"><a href="edit_user.php?id=' . $row['user_id'] . '">Edit</a></td>
	<td align="left"><a href="delete_user.php?id=' . $row['user_id'] . '">Delete</a></td>
	<td align="left">' . $row['last_name'] . '</td>
	<td align="left">' . $row['first_name'] . '</td>
	<td align="left">' . $row['dr'] . '</td>
	</tr>
';
}

echo '</table>';

mysql_free_result($result); // Free up resources.

mysql_close(); // Close the database connection.

// Make links to other pages at foot of web page so if more than 10 users register.
if ($num_pages > 1) {

	echo '<br /><p>';
	// Determine what page the script is on.
	$current_page = ($start/$display) + 1;

	// If it's not the first page, make a Previous button.
	if ($current_page != 1) {
	echo '<a href="userlist.php?s=' . ($start - $display) . '&np=' . $num_pages . '">Previous</a> ';
}

// Using FOR loop , make all the numbered pages.
for ($i = 1; $i <= $num_pages; $i++) {
if ($i != $current_page) {
	echo '<a href="userlist.php?s=' . (($display * ($i - 1))) . '&np=' . $num_pages . '">' . $i . '</a> ';
} else {
	echo $i . ' ';
	}
}

// If it's not the last page, make a Next button.
if ($current_page != $num_pages) {
	echo '<a href="userlist.php?s=' . ($start + $display) . '&np=' . $num_pages . '">Next</a>';
}

echo '</p>';

} // End of Links section
}
else {

	echo '<p>Sorry, you need to be admin to enter this area.<br />';
}

include ('../includes/afooter.html');
?>
Thanks a great deal for looking at it.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

use mysql_error() to help tell you where the error is in you query string.
invision
Forum Newbie
Posts: 12
Joined: Wed Feb 01, 2006 4:29 am
Location: Scotland

Post by invision »

Great stuff - found it,

Thanks again Feyd.
Post Reply