[RESOLVED] Limiting number of results

[Twilite_Kid_82]

I would like the results to be limited to 5 per page.. not to sure how to code it tho 🙂

The output would Display the first 5 shows by date per page but when it gets to the 6th show that show gets moved too page 2

<?php

$page_title = "SHOWS";
require("header.php");


?>



<?



if ($show) {



$sql = "SELECT DATE_FORMAT(date,'%c/%e/%Y'), venue_id, description FROM site_tourdates WHERE id=$show LIMIT 1";

$result = mysql_query($sql,$dbh);

$err = mysql_error();



if (mysql_num_rows($result) > 0) {


if ($err) { echo "$err"; die; }

list($date, $venue_id, $description) = mysql_fetch_array($result);



$sql2 = "SELECT name, street_address, city, state, zipcode, country, age_restriction, phone_number, more_info FROM site_tourdates_venues WHERE id=$venue_id LIMIT 1";

$result2 = mysql_query($sql2,$dbh);

$err = mysql_error();

if ($err) { echo "$err"; die; }

list($venue_name, $street_address, $city, $state, $zipcode, $country, $age_restriction, $phone_number, $more_info ) = mysql_fetch_array($result2);



echo "When: $date <br> Where: $venue_name <br> Details: $description <br>&nbsp;<br>";



echo "<u>Venue Details</u> <br>&nbsp;<br> $venue_name<br>&nbsp;<br>Address:<br>$street_address<br>$city, $state &nbsp; $zipcode<br>
";

echo "<b><a href=\"http://maps.google.com/maps?q=$street_address+$city,+$state,+$zipcode\" target=\"_blank\">
<u>View Map</u></b> </a>

";



if ($country != 'US') echo "$country<br>";



if (!$phone_number) $phone_number="N/A";

echo "&nbsp;<br>Phone Number: $phone_number<br>&nbsp;<br>";



if ($age_restriction) echo "Age Restriction: $age_restriction +<br>";



if ($more_info) echo "More Info: $more_info";



} 

else 
{ 
echo "There are currently no shows"; 
} 

}



else {



  $sql1 = "SELECT site_tourdates.id, DATE_FORMAT(site_tourdates.date,'%c/%e'), site_tourdates.description, site_tourdates_venues.name, site_tourdates_venues.city, site_tourdates_venues.state, site_tourdates_venues.country FROM site_tourdates, site_tourdates_venues WHERE site_tourdates.venue_id=site_tourdates_venues.id AND site_tourdates.date>=NOW() ORDER BY site_tourdates.date";

  $result1 = mysql_query($sql1,$dbh);

  $err = mysql_error();


 if (mysql_num_rows($result1) > 0) 
{ 



  if ($err) { echo "$err"; die; }



  while (list($tourdate_id, $date, $description, $venue_name, $venue_city, $venue_state, $venue_country ) = mysql_fetch_array($result1))  {




echo "$<b><a href=\"shows.php?show=$tourdate_id\"onclick=\"NewWindow(this.href,'mywin','200','200','no','center');return false\" onfocus=\"this.blur()\">$date $venue_city, $venue_state - $venue_name</a> </b> <br><br>"
;


  }



  if (mysql_num_rows($result1)) echo "&nbsp;<br>&nbsp;<br>";

}

else
{ 
echo "There are currently no shows"; 
} 


}



?>

[Twilite_Kid_82]

by the way, a example of the output is this ... so the 6th result ( should be moved to a 2nd page

Show
Show
show

[scrupul0us]

search for pagination... its been covered here many many many times

[Twilite_Kid_82]

Hey, I found tutorials on that last night, but im having issues on where I should put that code into my existing code.. thats more of the issue for me.. not sure where to enter it

[scrupul0us]

what i would do

take the example code... adapt your code to fit it... change the query to meet your query and change the output to meet your output

trial and error is the best way to learn... give it a start and come back with some questions and im sure we can get you moving in the right direction 🙂

[Twilite_Kid_82]

Hey, i've been working on it and so far I tried using this code, and no im receiving "no matches found"

<?PHP>$title = "SHOWS";
require("header.php")
?>

<?
//REMEMBER TO CONNECT TO DATABASE! 

//**EDIT TO YOUR TABLE NAME, ECT. 

$t = mysql_query("SELECT DATE_FORMAT(date,'%c/%e/%Y') FROM site_tourdates WHERE id = '".addslashes($_GET['id'])."'"); 
  if(!$t) die(mysql_error()); 

$a                  = mysql_fetch_object($t); 
$total_items      = mysql_num_rows($t); 
$limit            = $_GET['limit']; 
$type             = $_GET['type']; 
$page             = $_GET['page']; 

//set default if: $limit is empty, non numerical, less than 10, greater than 50 
if((!$limit)  || (is_numeric($limit) == false) || ($limit < 10) || ($limit > 50)) { 
     $limit = 10; //default 
} 
//set default if: $page is empty, non numerical, less than zero, greater than total available 
if((!$page) || (is_numeric($page) == false) || ($page < 0) || ($page > $total_items)) { 
      $page = 1; //default 
} 

//calcuate total pages 
$total_pages     = ceil($total_items / $limit); 
$set_limit          = $page * $limit - ($limit); 

//query: **EDIT TO YOUR TABLE NAME, ECT. 

$q = mysql_query("SELECT DATE_FORMAT(date,'%c/%e/%Y') FROM site_tourdates WHERE id ='".addslashes($_GET['id'])."' LIMIT $set_limit, $limit"); 
  if(!$q) die(mysql_error()); 
     $err = mysql_num_rows($q); 
       if($err == 0) die("No matches met your criteria."); 

//Results per page: **EDIT LINK PATH** 
echo("   

<a href=www.yoursite.com/stuff/script.php?cat=$cat&amp;limit=10&amp;page=1>10</a> | 
<a href=www.yoursite.com/stuff/script.php?cat=$cat&amp;limit=25&amp;page=1>25</a> | 
<a href=www.yoursite.com/stuff/script.php?cat=$cat&amp;limit=50&amp;page=1>50</a>"); 

//show data matching query: 
while($code = mysql_fetch_object($q)) { 
     echo("item: ".$code->title."<BR>"); 
} 

$cat = urlencode($cat); //makes browser friendly 

//prev. page: **EDIT LINK PATH** 

$prev_page = $page - 1; 

if($prev_page >= 1) { 
  echo("<b>&lt;&lt;</b> <a href=http://www.yoursite.com/stuff/script.php?cat=$cat&amp;limit=$limit&amp;page=$prev_page><b>Prev.</b></a>"); 
} 

//Display middle pages: **EDIT LINK PATH** 

for($a = 1; $a <= $total_pages; $a++) 
{ 
   if($a == $page) { 
      echo("<b> $a</b> | "); //no link 
     } else { 
  echo("  <a href=http://www.yoursite.com/stuff/script.php?cat=$cat&amp;limit=$limit&amp;page=$a> $a </a> | "); 
     } 
} 

//next page: **EDIT THIS LINK PATH** 

$next_page = $page + 1; 
if($next_page <= $total_pages) { 
   echo("<a href=http://www.yoursite.com/stuff/script.php?cat=$cat&amp;limit=$limit&amp;page=$next_page><b>Next</b></a> &gt; &gt;"); 
} 

//all done 

?>

<?




if ($show) {


$sql = "SELECT DATE_FORMAT(date,'%c/%e/%Y'), venue_id, description FROM site_tourdates WHERE id=$show LIMIT 1";

$result = mysql_query($sql,$dbh);

$err = mysql_error();



if (mysql_num_rows($result) > 0) {


if ($err) { echo "$err"; die; }

list($date, $venue_id, $description) = mysql_fetch_array($result);



$sql2 = "SELECT name, street_address, city, state, zipcode, country, age_restriction, phone_number, more_info FROM site_tourdates_venues WHERE id=$venue_id LIMIT 1";

$result2 = mysql_query($sql2,$dbh);

$err = mysql_error();

if ($err) { echo "$err"; die; }

list($venue_name, $street_address, $city, $state, $zipcode, $country, $age_restriction, $phone_number, $more_info ) = mysql_fetch_array($result2);



echo "When: $date <br> Where: $venue_name <br> Details: $description <br>&nbsp;<br>";



echo "<u>Venue Details</u> <br>&nbsp;<br> $venue_name<br>&nbsp;<br>Address:<br>$street_address<br>$city, $state &nbsp; $zipcode<br>
";

echo "<b><a href=\"http://maps.google.com/maps?q=$street_address+$city,+$state,+$zipcode\" target=\"_blank\">
<u>View Map</u></b> </a>

";



if ($country != 'US') echo "$country<br>";



if (!$phone_number) $phone_number="N/A";

echo "&nbsp;<br>Phone Number: $phone_number<br>&nbsp;<br>";



if ($age_restriction) echo "Age Restriction: $age_restriction +<br>";



if ($more_info) echo "More Info: $more_info";



} 

else 
{ 
echo "There are currently no shows"; 
} 

}



else {



  $sql1 = "SELECT site_tourdates.id, DATE_FORMAT(site_tourdates.date,'%c/%e'), site_tourdates.description, site_tourdates_venues.name, site_tourdates_venues.city, site_tourdates_venues.state, site_tourdates_venues.country FROM site_tourdates, site_tourdates_venues WHERE site_tourdates.venue_id=site_tourdates_venues.id AND site_tourdates.date>=NOW() ORDER BY site_tourdates.date";

  $result1 = mysql_query($sql1,$dbh);

  $err = mysql_error();


 if (mysql_num_rows($result1) > 0) 
{ 



  if ($err) { echo "$err"; die; }



  while (list($tourdate_id, $date, $description, $venue_name, $venue_city, $venue_state, $venue_country ) = mysql_fetch_array($result1))  {




echo "<b><a href=\"shows.php?show=$tourdate_id\"onclick=\"NewWindow(this.href,'mywin','200','200','no','center');return false\" onfocus=\"this.blur()\">$date $venue_city, $venue_state - $venue_name</a> </b> <br><br>"
;


  }



  if (mysql_num_rows($result1)) echo "&nbsp;<br>&nbsp;<br>";

}

else
{ 
echo "There are currently no shows"; 
} 


}
?>

[scrupul0us]

two things:

your opening PHP tag for the page has a trailing ">"

second... i assume its an issue with:

$q = mysql_query("SELECT DATE_FORMAT(date,'%c/%e/%Y') FROM site_tourdates WHERE id ='".addslashes($_GET['id'])."' LIMIT $set_limit, $limit"); 

first off u never wanna embed any data from POST or GET directly into a query... secondly, IF you are going to, use the function mysql_real_escape_string() instead of addslashes

have u echo'd out that query string to see what it looks like... maybe something funky is in there... try hard coding some values, like a known ID... im pretty sure numbers have to be surrounded by single quotes '2'

[Twilite_Kid_82]

I updated the code from your advice (not sure if its correct)
now im receiving this error

"You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '0'' at line 1"

two things.. am I logging into the database correctly? all the login info is in header.php

and also is the for the mysql_Query am I calling the tables correctly?

<? 

$title = "SHOWS"; 
require("header.php");
//REMEMBER TO CONNECT TO DATABASE! 

//**EDIT TO YOUR TABLE NAME, ECT. 

$t = mysql_query("SELECT "*" FROM site_tourdates WHERE id = '".mysql_real_escape_string($_GET['id'])."'");
  if(!$t) die(mysql_error()); 

$a                  = mysql_fetch_object($t); 
$total_items      = mysql_num_rows($t); 
$limit            = $_GET['limit']; 
$type             = $_GET['type']; 
$page             = $_GET['page']; 

//set default if: $limit is empty, non numerical, less than 10, greater than 50 
if((!$limit)  || (is_numeric($limit) == false) || ($limit < 10) || ($limit > 50)) { 
     $limit = 10; //default 
} 
//set default if: $page is empty, non numerical, less than zero, greater than total available
if((!$page) || (is_numeric($page) == false) || ($page < 0) || ($page > $total_items)) { 
      $page = 1; //default 
} 

//calcuate total pages 
$total_pages     = ceil($total_items / $limit); 
$set_limit          = $page * $limit - ($limit); 

//query: **EDIT TO YOUR TABLE NAME, ECT. 

$q = mysql_query("SELECT "*" FROM site_tourdates WHERE id ='".mysql_real_escape_string($_GET['id'])."' LIMIT $set_limit, $limit");
  if(!$q) die(mysql_error()); 
     $err = mysql_num_rows($q); 
       if($err == 0) die("No matches met your criteria."); 

//Results per page: **EDIT LINK PATH** 
echo("    

<a href=www.yoursite.com/stuff/script.php?cat=$cat&amp;limit=10&amp;page=1>10</a> | 
<a href=www.yoursite.com/stuff/script.php?cat=$cat&amp;limit=25&amp;page=1>25</a> | 
<a href=www.yoursite.com/stuff/script.php?cat=$cat&amp;limit=50&amp;page=1>50</a>"); 

//show data matching query: 
while($code = mysql_fetch_object($q)) { 
     echo("item: ".$code->title."<BR>"); 
} 

$cat = urlencode($cat); //makes browser friendly 

//prev. page: **EDIT LINK PATH** 

$prev_page = $page - 1; 

if($prev_page >= 1) { 
  echo("<b>&lt;&lt;</b> <a href=http://www.yoursite.com/stuff/script.php?cat=$cat&amp;limit=$limit&amp;page=$prev_page><b>Prev.</b></a>");
} 

//Display middle pages: **EDIT LINK PATH** 

for($a = 1; $a <= $total_pages; $a++) 
{ 
   if($a == $page) { 
      echo("<b> $a</b> | "); //no link 
     } else { 
  echo("  <a href=http://www.yoursite.com/stuff/script.php?cat=$cat&amp;limit=$limit&amp;page=$a> $a </a> | ");
     } 
} 

//next page: **EDIT THIS LINK PATH** 

$next_page = $page + 1; 
if($next_page <= $total_pages) { 
   echo("<a href=http://www.yoursite.com/stuff/script.php?cat=$cat&amp;limit=$limit&amp;page=$next_page><b>Next</b></a> &gt; &gt;");
} 

//all done 

?> 

[scrupul0us]

save yourself some trouble:

$id = mysql_real_escape_string($_GET['id']);
$t = mysql_query("SELECT * FROM site_tourdates WHERE id = '".$id."'");

then look in your database and find a valid ID number... lets say 5 is a valid ID.. try this:

$t = mysql_query("SELECT * FROM site_tourdates WHERE id = '5');

and see what happens

[Twilite_Kid_82]

ok I changed these two parts "109" is a id for one of the entries in the database

now the # of pages are appearing but above the results.. the results appear below but still show 6 not 5

so im assuming its the positioning of the code now that is the issue.. progress at least 🙂

$id = mysql_real_escape_string($_GET['id']); 
$t = mysql_query("SELECT * FROM site_tourdates WHERE id = '109'"); 

and also

  $id = mysql_real_escape_string($_GET['id']); 
$q = mysql_query("SELECT * FROM site_tourdates WHERE id = '109'"); 

[SpecialFX]

Is your id always an integer?
If so you don't need to use mysql_real_escape_string(), you could just check to make sure it is an integer. Also inside your SQL, don't put your id in quotes if it is always a integer, because that makes it a string. So you could try this instead:

if(is_int($_GET['id'])) {
    $q = mysql_query("SELECT * FROM `site_tourdates` WHERE `id` = 109");
}

[Twilite_Kid_82]

yep they are integers.. I can actually say im lost..

The funny thing is for the news section on the left side, I was able to have only five items listed working fine

and im gonna attach a txt file of the code just for that problematic section

[Twilite_Kid_82]

alright, I figured it out.. thanks all

i took the code that worked in the news section for limiting items.. and working that into the shows section

<? 

$title = "Shows";
require("header.php");
$page_limit=5; // set this to the number of news items per page you want shown
?>


<?
if ($show) { 


$sql = "SELECT DATE_FORMAT(date,'%c/%e/%Y'), venue_id, description FROM site_tourdates WHERE id=$show LIMIT 1"; 

$result = mysql_query($sql,$dbh); 

$err = mysql_error(); 



if (mysql_num_rows($result) > 0) { 


if ($err) { echo "$err"; die; } 

list($date, $venue_id, $description) = mysql_fetch_array($result); 



$sql2 = "SELECT name, street_address, city, state, zipcode, country, age_restriction, phone_number, more_info FROM site_tourdates_venues WHERE id=$venue_id LIMIT 1"; 

$result2 = mysql_query($sql2,$dbh); 

$err = mysql_error(); 

if ($err) { echo "$err"; die; } 

list($venue_name, $street_address, $city, $state, $zipcode, $country, $age_restriction, $phone_number, $more_info ) = mysql_fetch_array($result2); 



echo "When: $date <br> Where: $venue_name <br> Details: $description <br>&nbsp;<br>"; 



echo "<u>Venue Details</u> <br>&nbsp;<br> $venue_name<br>&nbsp;<br>Address:<br>$street_address<br>$city, $state &nbsp; $zipcode<br> 
"; 

echo "<b><a href=\"http://maps.google.com/maps?q=$street_address+$city,+$state,+$zipcode\" target=\"_blank\"> 
<u>View Map</u></b> </a> 

"; 



if ($country != 'US') echo "$country<br>"; 



if (!$phone_number) $phone_number="N/A"; 

echo "&nbsp;<br>Phone Number: $phone_number<br>&nbsp;<br>"; 



if ($age_restriction) echo "Age Restriction: $age_restriction +<br>"; 



if ($more_info) echo "More Info: $more_info"; 



} 

else 
{ 
echo "There are currently no shows"; 
} 

} 



else { 


if ($page) {
	$limit_start=($page-1)*10;
	$limit="LIMIT $limit_start, $page_limit";
} else $limit="LIMIT $page_limit";



  $sql1 = "SELECT site_tourdates.id, DATE_FORMAT(site_tourdates.date,'%c/%e'), site_tourdates.description, site_tourdates_venues.name, site_tourdates_venues.city, site_tourdates_venues.state, site_tourdates_venues.country FROM site_tourdates, site_tourdates_venues WHERE site_tourdates.venue_id=site_tourdates_venues.id AND site_tourdates.date>=NOW() ORDER BY site_tourdates.date DESC $limit"; 

  $result1 = mysql_query($sql1,$dbh); 

  $err = mysql_error(); 

if (mysql_num_rows($result1) > 0) 
{ 



  if ($err) { echo "$err"; die; } 



  while (list($tourdate_id, $date, $description, $venue_name, $venue_city, $venue_state, $venue_country ) = mysql_fetch_array($result1))  { 




echo "<b><a href=\"shows.php?show=$tourdate_id\"onclick=\"NewWindow(this.href,'mywin','200','200','no','center');return false\" onfocus=\"this.blur()\">$date $venue_city, $venue_state - $venue_name</a> </b> <br><br>" 
; 


  } 



  if (mysql_num_rows($result1)) echo "&nbsp;<br>&nbsp;<br>"; 

} 

else 
{ 
echo "There are currently no shows"; 
} 


} 


?>