0

Can someone please assist me with a problem of navigating through returned records? I have a mysql query:

if(isset($_GET['record'])) {
$record_current = $_GET['record'];
}else{
$record_current = 1;
}

$useremail = $_SESSION['useremail'];

$view_sql = "SELECT * FROM missions, calendar, bookings 
WHERE missions.missions_id =  calendar.missions_id 
AND calendar.calendar_id = bookings.calendar_id 
AND bookings.booking_email ='$useremail' LIMIT $record_current,1";

$view_result = mysql_query($view_sql) or die(mysql_error());
$view_count = mysql_num_rows($view_result);
$rsView = mysql_fetch_assoc($view_result);

The query produces the results desired. The values are used to fill a dynamic form. The problem I am having is navigating through the records properly. I am using this code for navigation (to allow a user to see all the bookings they have made). The code:

<span class="label"><?php if($record_current > 1) { ?><a href="view.php?record=<?php echo $record_current - 1; ?>" >Previous</a><?php } ?></span>
<span class="element"><?php if($record_current < $view_result) { ?><a href="view.php?record=<?php echo $record_current + 1; ?>" >Next</a><?php } ?></span>

The problem is that the first record to be displayed is actually the 2nd record from the query and the 'Next' link displays even when the last record is displayed. If the link is clicked it shows a blank form. I don't know what I am doing wrong and I've played with this for hours, any help would be greatly appreciated. Cheers

Edit

I have amended the code to this (thanks to Sean):

$view_sql = "SELECT SQL_CALC_FOUND_ROWS * FROM missions, calendar, bookings 
WHERE missions.missions_id =  calendar.missions_id 
AND calendar.calendar_id = bookings.calendar_id 
AND bookings.booking_email ='$useremail' LIMIT $record_current,1";

$view_result = mysql_query($view_sql) or die(mysql_error());
$view_count = mysql_query("SELECT FOUND_ROWS() AS cnt");
$rsView = mysql_fetch_assoc($view_result);
$adj_count = (mysql_result($view_count, 0, "cnt") - 1);

Apparently, "SELECT FOUND_ROWS" returns a set starting from zero. This is now working. Thanks to all who contributed. Cheers, Spud

Spud
  • 361
  • 2
  • 15
  • you are using obsolete `mysql_*` api its no longer maintained and deprecated in new version of php >=5.5 so either use pdo or mysqli please check http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php/14110189#14110189 and you have nice hole for the **SQL INJECTION** in your code so dont worry if your database is paw3ed – NullPoiиteя Mar 26 '13 at 03:06
  • I know it is deprecated. This is an exercise for a course I'm taking at a tech campus and will never be used in production. I've spoken with the teacher about being taught old technology, but that is what the curriculum is and I have to work with it. Cheers – Spud Mar 26 '13 at 03:10
  • kudos to Sean for steering me in the right direction. Cheers, mate – Spud Mar 26 '13 at 09:22

3 Answers3

2

Note : in getting the LIMIT $record_current,1 be reminded of computers logic that every counts start at zero so if your trying to get the first data you must set your initial value of $record_current as 0

Jhonathan H.
  • 2,734
  • 1
  • 19
  • 28
  • Thanks. I was thinking that. I've come up with a mess fix by duplicating the query without the limit and counting the rows from that and then making $count = (total rows - 1) to stop the 'Next' link from showing on the last record. It's pretty messy, but "works" after a fashion. I would think there is a much cleaner way of approaching it, but it's beyond my current knowledge. Cheers – Spud Mar 26 '13 at 04:08
1

Your if() in your NEXT line is wrong, as you are checking $record_current against the query - $view_result instead of the number of rows - $view_count. Try changing to -

if($record_current < $view_count)

EDIT
Take a look at SQL_CALC_FOUND_ROWS / FOUND_ROWS() at http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows.

$view_sql = "SELECT SQL_CALC_FOUND_ROWS * FROM missions, calendar, bookings 
WHERE missions.missions_id =  calendar.missions_id 
AND calendar.calendar_id = bookings.calendar_id 
AND bookings.booking_email ='$useremail' LIMIT $record_current,1";

$view_result = mysql_query($view_sql) or die(mysql_error());
$view_count = mysql_query("SELECT FOUND_ROWS()");
$rsView = mysql_fetch_assoc($view_result);
Sean
  • 12,443
  • 3
  • 29
  • 47
  • Hi, That will only work if record=0 in the URL because $view_count is always 1 because of the LIMIT in the query. I've been trying and trying different things and none work. I wonder if I have the query structured correctly. I'm very new at this and just don't know. Is there a way I can get the total number of records before the LIMIT and place that into a variable? – Spud Mar 26 '13 at 03:48
  • Did not think of that. I have updated my answer with a way to get the total number of rows using [`SQL_CALC_FOUND_ROWS`/`FOUND_ROWS()`](http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows) – Sean Mar 26 '13 at 04:13
  • Thanks @Sean, you steered me in the right direction. Look at the original question EDIT to see how I was able to make it work. Cheers – Spud Mar 26 '13 at 08:43
1
if(isset($_GET['record'])) {
$record_current = $_GET['record'];
}else{
$record_current = 1;
}

$view_sql = "SELECT * FROM missions, calendar, bookings 
WHERE missions.missions_id =  calendar.missions_id 
AND calendar.calendar_id = bookings.calendar_id 
AND bookings.booking_email ='$useremail' LIMIT $record_current,1";

The problem is that the first record to be displayed is actually the 2nd record from the query

note that when you are using LIMIT the offset of the first row will always be 0 not 1...

Joseph Caracuel
  • 974
  • 1
  • 8
  • 15
  • Thank you. I thought that was the case. The bit about it still showing the "Next" link when viewing the last record was the main thing I wanted to correct. Cheers – Spud Mar 26 '13 at 06:49