Page 1 of 1

[SOLVED] SQL - what's up with this?

Posted: Fri Jan 23, 2004 8:17 am
by eludlow
<?php

$connection = mysql_connect ('localhost', 'ed', 'password') or die(mysql_error());
mysql_db_query ("x-appeal", $connection);

$q = "SELECT date,newsitem,id FROM xanews ORDER BY id DESC, LIMIT 0,5";

$q_temp = "SELECT id FROM xanews";
if(mysql_num_rows($q) != 0) {
$idcollection= "";
$c = 0;
while($q1 = mysql_fetch_row($q)) {
echo $q1[1]."<br>";
$idcollection = $idcollection.$q1[0].",";
$c++;
}
$idcollection = substr($idcollection,0,strlen($idcollection)-1);
if($c == 5 && mysql_num_rows($q_temp) > 5) {
print "<a href='index.php?dontShowThese=$idcollection'>next page</a>";
}
}


?>




Keep getting told:
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource

Thanks,
Ed Ludlow

Posted: Fri Jan 23, 2004 9:15 am
by twigletmac
There's a few problems with the script, the main one being that you never query the database - I've done some modifications and commented the code below, any questions just ask (I have tested this with my own data and it appears to work):

Code: Select all

<?php

// you can do the pagination a bit easier by using a starting point
// from the URL (set to 0 if it isn't in the URL)
$limit_start = (!empty($_GET['start']) && is_numeric($_GET['start'])) ? $_GET['start'] : 0;

$connection = mysql_connect ('localhost', 'ed', 'password') or die(mysql_error());

// use mysql_select_db(), not mysql_query_db() here
mysql_select_db('x-appeal', $connection);

// count the number of records in total to allow you to see whether 
// to show a next page link
$sql_count = "SELECT COUNT(id) FROM xanews";
$result_count = mysql_query($sql_count) or die(mysql_error().'<p>'.$sql_count.'</p>');
$total_records = mysql_result($result_count, 0);

// now use the $limit_start variable to determine where to start
// returning records from in the SQL
$sql = "SELECT date, newsitem, id FROM xanews ORDER BY id DESC LIMIT $limit_start, 5";
$result = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');

if (mysql_num_rows($result) > 0) {

	// using mysql_fetch_assoc() allows you to reference the column
	// value by the column name - $row['newsitem'] instead of 
	// $row[1] - makes your code easier to maintain
	while ($row = mysql_fetch_assoc($result1)) {
		echo $row['newsitem'].'<br>';
	}

	// you can now add next and previous links
	if ($limit_start != 0) {
		// get the 5 previous records
		$start = $limit_start - 5;
		// start the records at 0 if the previous start minus 5 gives us
		// a negative number
		$start = ($start < 0) ? 0 : $start;
		echo '<a href="index.php?start='.$start.'"><<< Previous Page</a> &nbsp;';
	}
	
	if ($limit_start < $total_records) {
		// get the next 5 records
		$start = $limit_start + 5;

		echo '<a href="index.php?start='.$start.'">Next Page >>></a>';

	}
}


?>
Mac

[Did an edit 'cause I noticed I'd used $limit instead of $limit_start in one place]

Posted: Fri Jan 23, 2004 9:46 am
by eludlow
That's brilliant, thank you very much.

One little thing....

Is it easy to edit it so that once the last lot of rows have been shown (ie the page that will contain the first (ie eldest) row in the table), that page won't contain a "next page" link?

Once again, thanks for your help,
Ed

Posted: Fri Jan 23, 2004 10:00 am
by twigletmac
Sorry about the oversite - try changing:

Code: Select all

if ($limit_start < $total_records) {
      // get the next 5 records
      $start = $limit_start + 5;

      echo '<a href="index.php?start='.$start.'">Next Page >>></a>';

   }
to

Code: Select all

if ($limit_start < $total_records) {
      // get the next 5 records
      $start = $limit_start + 5;

      if ($total_records > $start) {
            echo '<a href="index.php?start='.$start.'">Next Page >>></a>';
      }

   }
Mac

Posted: Fri Jan 23, 2004 10:10 am
by eludlow
Brillaint - thank you VERY much. Virtual pint for you me thinks :)

Ed