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

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
eludlow
Forum Newbie
Posts: 13
Joined: Thu Jan 22, 2004 3:48 pm

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

Post 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
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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]
Last edited by twigletmac on Fri Jan 23, 2004 9:53 am, edited 1 time in total.
eludlow
Forum Newbie
Posts: 13
Joined: Thu Jan 22, 2004 3:48 pm

Post 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
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
eludlow
Forum Newbie
Posts: 13
Joined: Thu Jan 22, 2004 3:48 pm

Post by eludlow »

Brillaint - thank you VERY much. Virtual pint for you me thinks :)

Ed
Post Reply