PHP Paging

richo89 · February 27, 2009

Hey,

I am currently making a website thats using php/mysql to sell 2nd hand cars. My php page is searching a range of vehicles based on the criteria that the user enters.

The html for my document is as follows:

<html>
<head>

<!-- Right Menu Vehicle Search --!>

<div id="textcellNav" style="position: absolute; left: 320px; top:290px; width: 500px; height: 500px;">

<h1>Vehicle Search</h1>

<form action="cmSearch.php" method="post">

Choose Search Type:

<br>

<SELECT NAME="searchtype">

<OPTION VALUE="Make">Make

<OPTION VALUE="Model">Model

</SELECT>

</select>

<br>

Enter Search Term:

<br>

<input name="searchterm" type="text">

<br>

<input type = "submit" value="submit"/>

<?include('demo_paging4.php'); ?>

</form>

</div>

</body>

</html>

And my PHP as follows

<!doctype html public "-//w3c//dtd html 3.2//en">

<html>

<head>

<title>Multiple Results Using Paging Cars Database</title>

<meta name="GENERATOR" content="Arachnophilia 4.0">

<meta name="FORMATTER" content="Arachnophilia 4.0">

</head>

<body bgcolor="#ffffff" text="#000000" link="#0000ff" vlink="#800080" alink="#ff0000">

<?

require "config.php"; // All database details will be included here

$page_name="demo_paging4.php"; // If you use this code with a different page ( or file ) name then change this

@$column_name=$_GET['column_name']; // Read the column name from query string.

$start=$_GET['start']; // To take care global variable if OFF

if(!($start > 0)) { // This variable is set to zero for the first page

$start = 0;

}

$eu = ($start - 0);

$limit = 10; // No of records to be shown per page.

$this1 = $eu + $limit;

$back = $eu - $limit;

$next = $eu + $limit;

//Create variables to store data from .html file

$searchtype=$_POST['searchtype'];

$searchterm=$_POST['searchterm'];

/////////////// WE have to find out the number of records in our table. We will use this to break the pages///////

$query2=" SELECT * FROM cars WHERE ".$searchtype." LIKE '".$searchterm."'";;

$result2=mysql_query($query2);

echo mysql_error();

$nume=mysql_num_rows($result2);

/////// The variable nume above will store the total number of records in the table////

/////////// Now let us print the table headers ////////////////

$bgcolor="#f1f1f1";

echo "<TABLE width=50% align=center cellpadding=0 cellspacing=0> <tr>";

echo "<td bgcolor='dfdfdf' > <font face='arial,verdana,helvetica' color='#000000' size='4'><a href='$page_name?column_name=make'>make</a></font></td>";

echo "<td bgcolor='dfdfdf' > <font face='arial,verdana,helvetica' color='#000000' size='4'><a href='$page_name?column_name=model'>model</a></font></td>";

////////////// Now let us start executing the query with variables $eu and $limit set at the top of the page///////////

$query=" SELECT * FROM cars WHERE ".$searchtype." LIKE '".$searchterm."'";

if(isset($column_name) and strlen($column_name)>0){

$query = $query . " order by $column_name";

}

$query = $query. " limit $eu, $limit ";

$result=mysql_query($query);

echo mysql_error();

//////////////// Now we will display the returned records in side the rows of the table/////////

while($noticia = mysql_fetch_array($result))

{

if($bgcolor=='#f1f1f1'){$bgcolor='#ffffff';}

else{$bgcolor='#f1f1f1';}

echo "<tr >";

echo "<td align=left bgcolor=$bgcolor id='title'> <font face='Verdana' size='2'>$noticia[make]</font></td>";

echo "<td align=left bgcolor=$bgcolor id='title'> <font face='Verdana' size='2'>$noticia[model]</font></td>";

echo "</tr>";

}

echo "</table>";

////////////////////////////// End of displaying the table with records ////////////////////////

/////////////// Start the buttom links with Prev and next link with page numbers /////////////////

echo "<table align = 'center' width='50%'><tr><td align='left' width='30%'>";

//// if our variable $back is equal to 0 or more then only we will display the link to move back ////////

if($back >=0) {

print "<a href='$page_name?start=$back&column_name=$column_name'><font face='Verdana' size='2'>PREV</font></a>";

}

//////////////// Let us display the page links at center. We will not display the current page as a link ///////////

echo "</td><td align=center width='30%'>";

$i=0;

$l=1;

for($i=0;$i < $nume;$i=$i+$limit){

if($i <> $eu){

echo " <a href='$page_name?start=$i&column_name=$column_name'><font face='Verdana' size='2'>$l</font></a> ";

}

else { echo "<font face='Verdana' size='4' color=red>$l</font>";} /// Current page is not displayed as link and given font color red

$l=$l+1;

}

echo "</td><td align='right' width='30%'>";

///////////// If we are not in the last page then Next link will be displayed. Here we check that /////

if($this1 < $nume) {

print "<a href='$page_name?start=$next&column_name=$column_name'><font face='Verdana' size='2'>NEXT</font></a>";}

echo "</td></tr></table>";

?>

</body>

</html>

My results display fine using the include command as below:

However when I click 2nd page I get the following error:

Any help I would be extremely grateful!

Thanks

richo89 · March 2, 2009

Anyone?

phpdragon · March 2, 2009

here is your problem you have an extra semi colon, remove it

$query2=" SELECT * FROM cars WHERE ".$searchtype." LIKE '".$searchterm."'";;

try

$query2="SELECT * FROM cars WHERE '$searchtype' LIKE '$searchterm'";

phpdragon · March 2, 2009

also you dont need the dots in your query

richo89 · March 2, 2009

I have ammended my Query to the following:

$query=" SELECT * FROM cars WHERE '$searchtype' LIKE '$searchterm'";

However when I click submit it now displays nothing.

phpdragon · March 2, 2009

$query=" SELECT * FROM cars WHERE '$searchtype' LIKE %'$searchterm'%";

if it still causes and issue remove the single quotes and test it

premiso · March 2, 2009

$query=" SELECT * FROM cars WHERE '$searchtype' LIKE %'$searchterm'%";

That query is bad. Use this instead as it has correct syntax.

$query=" SELECT * FROM cars WHERE `$searchtype` LIKE '%$searchterm%'";

richo89 · March 2, 2009

$query=" SELECT * FROM cars WHERE '$searchtype' LIKE %'$searchterm'%";

That query is bad. Use this instead as it has correct syntax.
$query=" SELECT * FROM cars WHERE `$searchtype` LIKE '%$searchterm%'";

Thank you that worked perfectly.

However I am now back to my original error where my HTML file includes my search syntax on the page correctly as screenshotted above. However, when I click page 2 of my results I get the following error:

Unknown column '' in 'where clause'
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in F:\xampp\htdocs\assignment\demo_paging4.php on line 41

Unknown column '' in 'where clause'

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in F:\xampp\htdocs\assignment\demo_paging4.php on line 61

Its like the 2nd page forgets the initial search criteria/query?

Thanks again

premiso · March 2, 2009

Store the criteria in Sessions and check if they are active. Or store them as GET data on the URL.

phpdragon · March 2, 2009

thanks for the correction premiso

also you have the same query further down in your script change it to be the same as the one you just fixed.

a-scripts.com · March 2, 2009

well .. your first query was ok but note few things

$query=" SELECT * FROM cars WHERE '$searchtype' LIKE '$searchterm'";

This is wrong because using single quotes '$searchtypes' means that the sql interprets this as a string and not a table name. So either use apostrophes `$searchtypes` or nothing at all.

That means your query was OK but the reason why you got the fatal error is followng:

$searchtype=$_POST['searchtype'];
$searchterm=$_POST['searchterm'];
....
$query=" SELECT * FROM cars WHERE $searchtype LIKE '$searchterm'";

You are using those variables from $_POST to reference the table name and search term. Not going to write about sql injections and stuff like that. This works ok for your first page after search because you type some text and send the form via POST. But what happens when you want to click "next" link. Yep there's nothing in $_POST. You need to check $_GET array then. You also need to pass "searchterm" and "searchtype" to each page in your case. The way you are using it right now the sql query looks like:

SELECT * FROM cars WHERE  LIKE ''

And that's beacause $searchterm and $searchtype are not set at all.

phpdragon · March 2, 2009

doh good pick up on that one a-script

richo89 · March 2, 2009

well .. your first query was ok but note few things
$query=" SELECT * FROM cars WHERE '$searchtype' LIKE '$searchterm'";
This is wrong because using single quotes '$searchtypes' means that the sql interprets this as a string and not a table name. So either use apostrophes `$searchtypes` or nothing at all.

That means your query was OK but the reason why you got the fatal error is followng:
$searchtype=$_POST['searchtype'];
$searchterm=$_POST['searchterm'];
....
$query=" SELECT * FROM cars WHERE $searchtype LIKE '$searchterm'";
You are using those variables from $_POST to reference the table name and search term. Not going to write about sql injections and stuff like that. This works ok for your first page after search because you type some text and send the form via POST. But what happens when you want to click "next" link. Yep there's nothing in $_POST. You need to check $_GET array then. You also need to pass "searchterm" and "searchtype" to each page in your case. The way you are using it right now the sql query looks like:
SELECT * FROM cars WHERE  LIKE ''
And that's beacause $searchterm and $searchtype are not set at all.

Thank you ever so much for you help I really appreciate it.

I will now try my very best to get it working with $_GET rather than POST so that my query passes through all of the pages when searching.

Once again thank you ever so much!

Sign In

PHP Paging

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information