Page 1 of 2

[SOLVED] Next and Previous for a report!

Posted: Thu Jul 01, 2004 1:37 am
by deniscyriac
This is the report file I'm having. I'm parsing the query as string from flash
"report = "SELECT DATE_FORMAT(b.date_txt, ''%m-%d-%Y'' ) AS ''Date'' ,b.Team, b.CourseCode, b.Activity, b.time AS ''Time Taken'' FROM tracker b, users a where a.pkecode = b.pkecode and a.username ='"+names+"' ORDER BY b.date_txt LIMIT 0, 30";" NB:"names" is a data I'm taking from a combobox.
It's working fine for first page, if i press on "next" button it's giving error. I guess, the first query I have passed from flash is not carring on to the next page, Please give me some solution......

Code: Select all

<?php
<? 
$query = stripslashes($report); 
$hostname = "localhost"; 
$username = ""; 
$password = ""; 
$dbName = "timetracker"; 
  MYSQL_CONNECT($hostname, $username, $password) OR DIE("Unable to connect"); 
  @MYSQL_SELECT_DB("$dbName") OR DIE("Unable to select database"); 
     
   if(!isset($start)) $start = 0; 


   $result = MYSQL_QUERY($query); 

  $total_rows = mysql_num_rows($result); 
  $row = mysql_fetch_row($result); 
   
  print $rows; 
  $total_cols = count($row); 

  if (!$total_rows) { 
    print "<HTML><title>Reports</title><FONT size='4' color='#000000' face='Verdana, Arial, Helvetica, sans-serif'><b>No report found! Please search again.... </b></FONT></BODY></HTML>"; 
    return;      
  } 
print "<HTML><title>Reports</title><BODY text="#000000" link="#FFFFFF" vlink="#FFFFFF" alink="#FFFFFF" topmargin='0' marginheight='0'>"; 


  print "<table width='500' border='0' cellspacing='0' cellpadding='3' align='center'> "; 
  print "<tr bgcolor="#61ABD0"><td colspan=$total_cols align=center><FONT size='3' color='#FFFFFF' face='Verdana, Arial, Helvetica, sans-serif'><b>Reports</b></FONT></td></tr>"; 
  print "<tr bgcolor="#A8D2F2">"; 
$count = mysql_num_fields($result); 
  for ($i = 0; $i < $count; $i++) { 
    print "<td><FONT size='2' face='Verdana, Arial, Helvetica, sans-serif'><b>"; 
    $header = mysql_field_name($result, $i)."\t"; 
    print $header; 
    print "</b></FONT></td>"; 
} 
print "</tr>"; 
print "<tr>"; 
  $i=0; 
    while($i < $total_cols){ 
    print "<td><FONT size='1' face='Verdana, Arial, Helvetica, sans-serif'>"; 
    print $row[$i]; 
    print "</FONT></td>"; 
    $i++; 
  } 
  print "</tr>"; 
$j = 1; 
  while($row = mysql_fetch_row ($result)) { 
  $j++; 
    $i = 0; 
   if ($j%2 == 0) { 
   print "<tr bgcolor="#EAF3FB">"; 
   }else{ 
    print "<tr bgcolor="#FFFFFF">"; 
    } 
    while($i < $total_cols){ 
     
      print "<td ><FONT size='1' face='Verdana, Arial, Helvetica, sans-serif'>"; 
      print $row[$i]; 
      print "</FONT></td>"; 
      $i++; 
    } 
    print "</tr>"; 
  } 
  print "<tr><td> "; 

$query1 = "SELECT count(*) as count FROM tracker"; 
$result1 = mysql_query($query1); 
$row1 = mysql_fetch_array($result1); 
$numrows = $row1['count']; 
print "<tr bgcolor="#BBD0F2"><td colspan=$total_cols align=center>"; 
print "<table width='100%' border='1' cellspacing='0' cellpadding='2' align='center'> "; 
print "<tr width='50%'><td align='left' width='50%'><FONT size='2' color='#FFFFFF' face='Verdana, Arial, Helvetica, sans-serif'>"; 
if($start > 0) 
echo "<a href="" . $PHP_SELF . "?start=" . ($start - 30) ."">Previous</a>"; 
print "</FONT></td><td align='right' width='50%'><FONT size='2' color='#FFFFFF' face='Verdana, Arial, Helvetica, sans-serif'>"; 
if($numrows > ($start + 30)) 
echo "<a href="" . $PHP_SELF . "?start=" . ($start + 30) ."">Next</a>"; 
print "</FONT></td></tr>"; 
print "</TABLE>"; 
print "</td></tr>"; 
print "</TABLE></BODY></HTML>"; 
?> 

?>

Posted: Thu Jul 01, 2004 3:20 am
by Grim...
First, congrats on using the

Code: Select all

tags.

I think a good start is to add the query to your 'next' link. So:

Code: Select all

echo "<a href="" . $PHP_SELF . "?start=" . ($start + 30) ."&report=".$query."">Next</a>";

Posted: Thu Jul 01, 2004 4:34 am
by deniscyriac
Thanks a lot for the quick respose.........

Now it's working but it's showing only two pages, first one with next and second with previous.

I added this code for previous also.

Code: Select all

echo "<a href="" . $PHP_SELF . "?start=" . ($start - 5) ."">Previous</a>";

Posted: Thu Jul 01, 2004 4:53 am
by Grim...
So it's working as designed?

No it's not!

Posted: Thu Jul 01, 2004 4:57 am
by deniscyriac
No it's not! it's showing only 2 pages. actually the result is more.....may be more than 8 pages if we limit it by 5.

Posted: Thu Jul 01, 2004 5:02 am
by Grim...
Hmm. Firstly, shouldn't the SQL query be paying some attention to $start?

Code: Select all

SELECT DATE_FORMAT(b.date_txt, ''%m-%d-%Y'' ) AS ''Date'' ,b.Team, b.CourseCode, b.Activity, b.time AS ''Time Taken'' FROM tracker b, users a where a.pkecode = b.pkecode and a.username ='"+names+"' ORDER BY b.date_txt LIMIT 0, 30

is always going to take the first 30 records, you need the LIMIT section to read

Code: Select all

LIMIT $start, 30

Posted: Thu Jul 01, 2004 5:32 am
by deniscyriac
Grim,

paging is happening now. But at the last page also Next is visible and if we click on it, page shows error........ :( sorry for bugging u again and again...

Posted: Thu Jul 01, 2004 5:41 am
by Grim...
Are you sure $query1 gives the correct amount of rows?

Posted: Thu Jul 01, 2004 6:26 am
by deniscyriac
I'm not sure about it. shall we combine query and query1? Actually I tried to fetch the no. of rows from first query only but i couldn't....

Posted: Thu Jul 01, 2004 7:39 am
by deniscyriac
$query1 is not giving the correct amount of the rows. It's counting the total no. from table directly, not from the first query. help me pls...........

Posted: Thu Jul 01, 2004 7:47 am
by Grim...
Well, that's easy. Just rewrite the first query without the 'LIMIT' (or it will always be 30). So:

Code: Select all

<?php

$query1 = "SELECT DATE_FORMAT(b.date_txt, ''%m-%d-%Y'' ) AS ''Date'' ,b.Team, b.CourseCode, b.Activity, b.time AS ''Time Taken'' FROM tracker b, users a where a.pkecode = b.pkecode and a.username ='"+names+"'";
$numrows = mysql_num_rows( $result );
?>

Posted: Thu Jul 01, 2004 8:01 am
by deniscyriac
THANKS A TON GRIM!
IT's WORKING FINE!!!!!!!!!!

I have another query should I post it seperately?

Posted: Thu Jul 01, 2004 9:33 am
by deniscyriac
oh!!! one more problem. whenever I refresh the result first page it's giving error!

Posted: Thu Jul 01, 2004 11:46 pm
by deniscyriac
one more problem. whenever I refresh the result first page it's giving error! all the other pages are working fine! help me please

Posted: Thu Jul 01, 2004 11:47 pm
by feyd
what's the error?