Jump to content

Adding Paginatation to a script


amg182

Recommended Posts

Hi Guys.

 

I have the following PHP code querying results based on 4 combo filter boxes and would like to insert some pagination. I have found some pagination code that also works fine, but whenever I try to combine/merge the two PHP codes, I get some errors.... :shrug:

 

Here is the php that queries the mysql database:

<form name="input" action="" method="post">
Make: <Select name="make">
<option "Input" value="<?php echo $_POST['make']; ?>"><?php echo $_POST['make']; ?></option>
<option value="All Makes">All Makes</option>
<option value="Ford">ford</option>
<option value="BMW">BMW</option>
<option value="Honda">Honda</option>
<option value="Lexus">Lexus</option>
</select>

Model: <Select name="model">
<option "Input" value="<?php echo $_POST['model']; ?>"><?php echo $_POST['model']; ?></option>
<option value="All Models">All Models </option>
<option value="Civic">Civic</option>
<option value="3 Series">3 Series</option>
<option value="Fiesta">Fiesta</option>
<option value="IS200">IS200</option>
</select>

Fuel: <Select name="fuel">
<option "Input" value="<?php echo $_POST['fuel']; ?>"><?php echo $_POST['fuel']; ?></option>
<option value="Any">Any</option>
<option value="Petrol">Petrol</option>
<option value="Diesel">Diesel</option>
</select>

Location: <Select name="location">
<option "Input" value="<?php echo $_POST['location']; ?>"><?php echo $_POST['location']; ?></option>
<option value="UK">UK</option>
<option value="London">London</option>
<option value="Kent">Kent</option>
</select>

<input type="submit" value="Search Cars" />
</form>


<?php
include 'db.inc.php';

//variables from selection form
$make = mysql_real_escape_string($_POST['make']);
$model = mysql_real_escape_string($_POST['model']);
$fuel = mysql_real_escape_string($_POST['fuel']);
$location = mysql_real_escape_string($_POST['location']);

//default query
$query .= "SELECT * FROM `cars`";

//count the selections for queries
$query_count = 0;

//set queries and associate WHERE,AND
if(isset($_POST['make']) && $_POST['make'] != "All Makes" && $_POST['make'] != ""){
$query_count = $query_count +1;
if($query_count == 1) {
$where_and = "WHERE";
} else {
$where_and = "AND";
}
$query .= " $where_and Make='$make'";
}

if(isset($_POST['model']) && $_POST['model'] != "All Models" && $_POST['model'] != ""){
$query_count = $query_count +1;
if($query_count == 1) {
$where_and = "WHERE";
} else {
$where_and = "AND";
}
$query .= " $where_and Model='$model'";
}

if(isset($_POST['fuel']) && $_POST['fuel'] != "Any" && $_POST['fuel'] != ""){
$query_count = $query_count +1;
if($query_count == 1) {
$where_and = "WHERE";
} else {
$where_and = "AND";
}
$query .= "  $where_and Fuel='$fuel'";
}

if(isset($_POST['location']) && $_POST['location'] != "UK" && $_POST['location'] != ""){
$query_count = $query_count +1;
if($query_count == 1) {
$where_and = "WHERE";
} else {
$where_and = "AND";
}
$query .= "  $where_and Location='$location'";
}

//echo $query;//query built from values above depending on selections
$result = mysql_query("$query");

//check if results
if(!$result){
echo "No Results <br />";
} else {

echo "<table class='ex1' border='0' width='120%' style=text-align:center; cellpadding='6' cellspacing='0'>

</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr style=font-family:verdana;font-size:80%;>";

    echo "<td width=13%>" . $row[""] . "<img src=\"" . $row["Photo"] . "\"></a>";
    echo '<td width="14%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Make'] . '</a></td>';   
    echo '<td width="5%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Model'] . '</a></td>';
    echo '<td width="4%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Fuel'] . '</a></td>';
    echo '<td width="4%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Location'] . '</a></td>';
       
echo "</tr>";
  }
echo "</table>";
}

mysql_close($con);

?>

 

 

Here is the pagination code I am using:

<?php

include 'db.inc.php';
$per_page = 5;

//count of pages which is displays amount of page if you want to count rows you need to remove the divide sign
$pages_query = mysql_query("SELECT COUNT('id') FROM cars");

//add echo before dollar pages below if you want to show count of rows or pages
$pages = ceil(mysql_result($pages_query, 0) / $per_page);

$page = (isset($_GET['page'])) ? (int)$_GET['page'] : 1;
$start = ($page - 1) * $per_page;

//query here
$query = mysql_query("SELECT * FROM `cars` LIMIT $start, $per_page");

//get results
while($query_row = mysql_fetch_assoc($query)) {

//show results
  echo "<p>" . $query_row['Model'] . "</p>";
}

//show number of pages at footer
if ($pages >= 1 && $page <= $pages) {

for ($x =1; $x<=$pages; $x++ ){
   echo ($x == $page) ? '<strong><a href="?page='.$x.'">'.$x.'</a></strong> ' : '<a href="?page='.$x.'">'.$x.'</a> ' ;

}

}
?>

 

Both PHP codes work fine seperately, but dont know how to merge them... :confused:

 

Can anyone help me on this?

 

Thanks! :)

Link to comment
Share on other sites

Let me know if this doesn't work:  It is Un-tested!

<?php
include 'db.inc.php';

//variables from selection form
$cars['Make'] = mysql_real_escape_string($_POST['make']);
$cars['Model'] = mysql_real_escape_string($_POST['model']);
$cars['Fuel'] = mysql_real_escape_string($_POST['fuel']);
$cars['Location'] = mysql_real_escape_string($_POST['location']);

//make sure all $cars variables hold a value;
//set it to an array if it does.
foreach($cars as $key => $value) {
if(!empty($value)) {
	$query_string[] = "$key = '$value' ";
}
}

//default query, with added parameters.
$query = "SELECT * FROM `cars` WHERE " . implode('AND ',$query_string);

/*paganation starts =================*/
$per_page = 5;
$page_query = str_replace('*','COUNT(*)',$query);
$page_result = mysql_query($page_query) or trigger_error(mysql_error());
$count = mysql_fetch_row($page_result);
$row_count = $count[0];
//add echo before dollar pages below if you want to show count of rows or pages
$pages = ceil($row_count / $per_page);

$page = (isset($_GET['page'])) ? (int)$_GET['page'] : 1;
$query .= ' LIMIT ' . (($page - 1) * $per_page) . ',' . $per_page;


//echo $query;//query built from values above depending on selections
$result = mysql_query($query);

//check if results
if(!$result){
echo "No Results <br />";
} else {

echo "<table class='ex1' border='0' width='120%' style=text-align:center; cellpadding='6' cellspacing='0'>

</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr style=font-family:verdana;font-size:80%;>";

    echo "<td width=13%>" . $row[""] . "<img src=\"" . $row["Photo"] . "\"></a>";
    echo '<td width="14%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Make'] . '</a></td>';   
    echo '<td width="5%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Model'] . '</a></td>';
    echo '<td width="4%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Fuel'] . '</a></td>';
    echo '<td width="4%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Location'] . '</a></td>';
       
echo "</tr>";
  }
echo "</table>";
}
//show number of pages at footer
if ($pages >= 1 && $page <= $pages) {
 for ($x =1; $x<=$pages; $x++ ){
   echo ($x == $page) ? '<strong><a href="?page='.$x.'">'.$x.'</a></strong> ' : '<a href="?page='.$x.'">'.$x.'</a> ' ;
}
}

mysql_close($con);

?>

Link to comment
Share on other sites

Hi Thanks for reply, unfortunately it comes up with this error

Warning: mysql_close(): supplied argument is not a valid MySQL-Link resource in /home/a5525005/public_html/pagination.php on line 102

 

Any ideas?

 

Thanks again

Link to comment
Share on other sites

Sorry, its actually posting these errors....

 

Warning: implode() [function.implode]: Invalid arguments passed in /home/a5525005/public_html/pagination.php on line 73

 

 

 

PHP Error Message

 

Notice: 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 '' at line 1 in /home/a5525005/public_html/pagination.php on line 78

 

 

 

PHP Error Message

 

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /home/a5525005/public_html/pagination.php on line 79

Link to comment
Share on other sites

Here is what i tried, but still no joy... anyone know whats wrong with this? no errors appearing.

 

<form name="input" action="" method="post">
Make: <Select name="make">
<option "Input" value="<?php echo $_POST['make']; ?>"><?php echo $_POST['make']; ?></option>
<option value="All Makes">All Makes</option>
<option value="Ford">ford</option>
<option value="BMW">BMW</option>
<option value="Honda">Honda</option>
<option value="Lexus">Lexus</option>
</select>

Model: <Select name="model">
<option "Input" value="<?php echo $_POST['model']; ?>"><?php echo $_POST['model']; ?></option>
<option value="All Models">All Models </option>
<option value="Civic">Civic</option>
<option value="3 Series">3 Series</option>
<option value="Fiesta">Fiesta</option>
<option value="IS200">IS200</option>
</select>

Fuel: <Select name="fuel">
<option "Input" value="<?php echo $_POST['fuel']; ?>"><?php echo $_POST['fuel']; ?></option>
<option value="Any">Any</option>
<option value="Petrol">Petrol</option>
<option value="Diesel">Diesel</option>
</select>

Location: <Select name="location">
<option "Input" value="<?php echo $_POST['location']; ?>"><?php echo $_POST['location']; ?></option>
<option value="UK">UK</option>
<option value="London">London</option>
<option value="Kent">Kent</option>
</select>

<input type="submit" value="Search Cars" />
</form>


<?php
include 'db.inc.php';
$per_page = 2;

//count of pages which is displays amount of page if you want to count rows you need to remove the divide sign
$pages_query = mysql_query("SELECT COUNT('id') FROM cars");

//add echo before dollar pages below if you want to show count of rows or pages
$pages = ceil(mysql_result($pages_query, 0) / $per_page);

$page = (isset($_GET['page'])) ? (int)$_GET['page'] : 1;
$start = ($page - 1) * $per_page;

//variables from selection form
$make = mysql_real_escape_string($_POST['make']);
$model = mysql_real_escape_string($_POST['model']);
$fuel = mysql_real_escape_string($_POST['fuel']);
$location = mysql_real_escape_string($_POST['location']);

//default query
$query = "SELECT * FROM `cars` LIMIT $start, $per_page";



//count the selections for queries
$query_count = 0;

//set queries and associate WHERE,AND
if(isset($_POST['make']) && $_POST['make'] != "All Makes" && $_POST['make'] != ""){
$query_count = $query_count +1;
if($query_count == 1) {
$where_and = "WHERE";
} else {
$where_and = "AND";
}
$query .= " $where_and Make='$make'";
}

if(isset($_POST['model']) && $_POST['model'] != "All Models" && $_POST['model'] != ""){
$query_count = $query_count +1;
if($query_count == 1) {
$where_and = "WHERE";
} else {
$where_and = "AND";
}
$query .= " $where_and Model='$model'";
}

if(isset($_POST['fuel']) && $_POST['fuel'] != "Any" && $_POST['fuel'] != ""){
$query_count = $query_count +1;
if($query_count == 1) {
$where_and = "WHERE";
} else {
$where_and = "AND";
}
$query .= "  $where_and Fuel='$fuel'";
}

if(isset($_POST['location']) && $_POST['location'] != "UK" && $_POST['location'] != ""){
$query_count = $query_count +1;
if($query_count == 1) {
$where_and = "WHERE";
} else {
$where_and = "AND";
}
$query .= "  $where_and Location='$location'";
}

//echo $query;//query built from values above depending on selections
$result = mysql_query("$query");




//check if results
if(!$result){
echo "No Results <br />";
} else {

echo "<table class='ex1' border='0' width='120%' style=text-align:center; cellpadding='6' cellspacing='0'>

</tr>";

while($result_row = mysql_fetch_array($result)) {

  echo "<tr style=font-family:verdana;font-size:80%;>";

    echo "<td width=13%>" . $row[""] . "<img src=\"" . $row["Photo"] . "\"></a>";
    echo '<td width="14%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Make'] . '</a></td>';   
    echo '<td width="5%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Model'] . '</a></td>';
    echo '<td width="4%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Fuel'] . '</a></td>';
    echo '<td width="4%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Location'] . '</a></td>';
       
echo "</tr>";
  }
echo "</table>";
}


?>

Link to comment
Share on other sites

Try this one:

<form name="input" action="" method="post">
Make: <Select name="make">
<option "Input" value="<?php echo $_POST['make']; ?>"><?php echo $_POST['make']; ?></option>
<option value="All Makes">All Makes</option>
<option value="Ford">ford</option>
<option value="BMW">BMW</option>
<option value="Honda">Honda</option>
<option value="Lexus">Lexus</option>
</select>

Model: <Select name="model">
<option "Input" value="<?php echo $_POST['model']; ?>"><?php echo $_POST['model']; ?></option>
<option value="All Models">All Models </option>
<option value="Civic">Civic</option>
<option value="3 Series">3 Series</option>
<option value="Fiesta">Fiesta</option>
<option value="IS200">IS200</option>
</select>

Fuel: <Select name="fuel">
<option "Input" value="<?php echo $_POST['fuel']; ?>"><?php echo $_POST['fuel']; ?></option>
<option value="Any">Any</option>
<option value="Petrol">Petrol</option>
<option value="Diesel">Diesel</option>
</select>

Location: <Select name="location">
<option "Input" value="<?php echo $_POST['location']; ?>"><?php echo $_POST['location']; ?></option>
<option value="UK">UK</option>
<option value="London">London</option>
<option value="Kent">Kent</option>
</select>

<input type="submit" name="searchCars" value="Search Cars" />
</form>

<?php
if(!isset($_POST['searchCars']) || $_POST['searchCars'] != 'Search Cars') { exit(); }
include 'db.inc.php';


//variables from selection form
$cars['Make'] = mysql_real_escape_string($_POST['make']);
$cars['Model'] = mysql_real_escape_string($_POST['model']);
$cars['Fuel'] = mysql_real_escape_string($_POST['fuel']);
$cars['Location'] = mysql_real_escape_string($_POST['location']);

//make sure all $cars variables hold a value;
//set it to an array if it does.
foreach($cars as $key => $value) {
if(!empty($value) && (substr($value,0,3) != 'All') && (substr($value,0,3) != 'Any')) {
	$query_string[] = "$key = '$value' ";
}
}


//default query, with added parameters.
$query = "SELECT * FROM `cars` ";

if(is_array($query_string)) {
$query .= 'WHERE ' . implode('AND ',$query_string);
}

/*paganation starts =================*/
$per_page = 5;
$page_query = str_replace('*','COUNT(*)',$query);
$page_result = mysql_query($page_query) or trigger_error(mysql_error());
$count = mysql_fetch_row($page_result);
$row_count = $count[0];
//add echo before dollar pages below if you want to show count of rows or pages
$pages = ceil($row_count / $per_page);

$page = (isset($_GET['page'])) ? (int)$_GET['page'] : 1;
$query .= ' LIMIT ' . (($page - 1) * $per_page) . ',' . $per_page;


//echo $query;//query built from values above depending on selections
$result = mysql_query($query);

//check if results
if(!$result){
echo "No Results <br />";
} else {
echo "<table class='ex1' border='0' width='120%' style=text-align:center; cellpadding='6' cellspacing='0'>
		</tr>";

while($row = mysql_fetch_array($result))
  {
echo "<tr style=font-family:verdana;font-size:80%;>";

    echo "<td width=13%>" . $row[""] . "<img src=\"" . $row["Photo"] . "\"></a>";
    echo '<td width="14%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Make'] . '</a></td>';   
    echo '<td width="5%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Model'] . '</a></td>';
    echo '<td width="4%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Fuel'] . '</a></td>';
    echo '<td width="4%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Location'] . '</a></td>';
       
echo "</tr>";
  }
echo "</table>";
}
//show number of pages at footer
if ($pages >= 1 && $page <= $pages) {
for ($x =1; $x<=$pages; $x++ ){
   echo ($x == $page) ? '<strong><a href="?page='.$x.'">'.$x.'</a></strong> ' : '<a href="?page='.$x.'">'.$x.'</a> ' ;
}
}


?>

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.