Search help

[unplugged_web]

I've built a search facility into a site I'm working on, but it's the first time I've ever done this myself so wanted a little bit of help please. The code for the search works, but I want to change it so that a set number of results are displayed on each page (say 10 results). How do I do that and would I add something along the lines of

$eu = ($start - 0);		$sql_select = "SELECT * "; $sql_from = " FROM table "; $sql_where = " WHERE 1=1";$sql_order = " ORDER BY date DESC";$sql_limit = " LIMIT 5";

to the page? But what about the next and previous buttons and the number of pages?In an ideal world I'd also like to add a 'sort by salary, date added etc' feature.The code that I have at the moment is:

<?php$con = mysql_connect("server ip", "username", " password");if (!$con)  {  die('Could not connect: ' . mysql_error());  }mysql_select_db("database", $con);$sql_select = "SELECT * "; $sql_from = " FROM table "; $sql_where = " WHERE 1=1";$location=mysql_real_escape_string($_POST['location']);$sector =mysql_real_escape_string($_POST['sector']);$salary =mysql_real_escape_string($_POST['salary']);$salary =mysql_real_escape_string($_POST['description']);$salary =mysql_real_escape_string($_POST['Ref']);$salary =mysql_real_escape_string($_POST['title']);if ($location<>""){ $sql_where .= " AND location='".$location."' "; } if ($sector<>""){ $sql_where .= " AND sector='".$sector."' "; } if ($salary<>"") 	   { 		 if ($salary=="50"){ 		 $sql_where .= " AND salary < '50000.00'"; 		 } 		 if ($salary=="50_75"){ 		 $sql_where .= " AND salary BETWEEN '50000.00' AND '75000.00'"; 		 } 		 if ($salary=="75_100"){ 		 $sql_where .= " AND salary BETWEEN '75000.00' AND '100000.00'"; 		 } 		 if ($salary=="100_150"){ 		 $sql_where .= " AND salary BETWEEN '100000.00' AND '105000.00'"; 		 } 		 if ($salary=="150"){ 		 $sql_where .= " AND salary > '105000.00'"; 		 }  	   } $result = mysql_query ($sql_select . $sql_from . $sql_where); if (!$result ) { 	echo("<p>Doh! Error performing query: " . mysql_error($result) . "</p>"); 	exit(); }  if ($row = mysql_fetch_array($result)) { do { PRINT "<b>Job Title: </b> "; print $row["title"];print ("<p>"); PRINT "<b>Job ref: </b> "; print $row["Ref"];print ("<p>");PRINT "<b>Location: </b> "; print $row["location"];print ("<p>"); print ("<br>"); PRINT "<b>Sector: </b> "; print $row["sector"]; print ("<p>"); PRINT "<b>Salary: </b> "; print .$row["salary"]; PRINT "<b>Description: </b> "; print $row["description"]; print ("<p>"); } while($row = mysql_fetch_array($result)); } else {print "Sorry, no records were found!";} ?>

Thanks for your help.

[unplugged_web]

Okay I've worked out how to get the 10 most recent entries, but if I just want to display 10 on each page how do I do that? So far I've only been able to display 10 in totalI used:

$sql_select = "SELECT * "; $sql_from = " FROM table "; $sql_where = " WHERE 1=1";$sql_order = " ORDER BY date DESC";$sql_limit = " LIMIT 0,10";$result = mysql_query ($sql_select . $sql_from . $sql_where . $sql_order . $sql_limit);

to limit the results

[Synook]

You could have a GET variable that indicates the starting point for each, so page 2 may have a querystring as such ?start=10, page three ?start=20 and these links you would echo out then have

$start = (int) $_GET['start'];$sql_limit = " LIMIT $start,10";

[unplugged_web]

You could have a GET variable that indicates the starting point for each, so page 2 may have a querystring as such ?start=10, page three ?start=20 and these links you would echo out then have

$start = (int) $_GET['start'];$sql_limit = " LIMIT $start,10";

Thanks I'll give that a go, will I need to create a new page for every set of results or could I use ?start=10 for the link? and if so how does it know which page it's on already?

[unplugged_web]

Thanks I'll give that a go, will I need to create a new page for every set of results or could I use ?start=10 for the link? and if so how does it know which page it's on already?

I've added

<a href='?start=<? $start ?>'>next</a>

to the html part of the page and

$start = (int) $_GET['start'];$sql_limit = " LIMIT $start,10";

to the php part. I think I've done something wrong because when I click next it it goes to the same address (which I want it to do), but the last part of the address is: ?start= and the results don't change. I'm not sure if I've put it in the right place or not, but it's not workingAnd how would I go back to the previous page as well?

[Synook]

I think I've done something wrong because when I click next it it goes to the same address (which I want it to do), but the last part of the address is: ?start= and the results don't change.

You have to check whether $_GET['start'] is defined first.I would also use mysql_num_rows() to get the number of results then print out the appropriate number of links. E.g.

$start = isset($_GET['start']) ? (int) $_GET['start'] : 0;$results = mysql_num_rows($result);$pages = floor($results / 10);echo "Page ";for ($i = 0; $i < $pages; $i++ ) {	if ($i != $start) echo ""<a href=\"?start=" . (10 * $i) . "\">";	echo  $i + 1;	if ($i != $start) "</a> ";}echo "<br />";if ($start != 0) echo "<a href=\"?start=0\">First</a> <a href=\"?start=" . ($start - 10) . "\">Previous</a> ";if ($start != $pages * 10) echo "<a href=\"?start=" . ($start + 10) . "\">Next</a> <a href=\"?start=" . ($pages * 10) . "\">Last</a>";

[unplugged_web]

Okay, I'm definately doing something wrong. I've now got

<?php$con = mysql_connect("server ip", "username", " password");if (!$con)  {  die('Could not connect: ' . mysql_error());  }mysql_select_db("database", $con);$sql_select = "SELECT * "; $sql_from = " FROM table ";$sql_where = " WHERE 1=1";$sql_order = " ORDER BY date DESC";$results = mysql_num_rows($result);$pages = floor($results / 5);echo "Page ";for ($i = 0; $i < $pages; $i++ ) {    if ($i != $start) echo ""<a href=\"?start=" . (5 * $i) . "\">";    echo  $i + 1;    if ($i != $start) "</a> ";}echo "<br />";if ($start != 0) echo "<a href=\"?start=0\">First</a> <a href=\"?start=" . ($start - 5) . "\">Previous</a> ";if ($start != $pages * 5) echo "<a href=\"?start=" . ($start + 5) . "\">Next</a> <a href=\"?start=" . ($pages * 5) . "\">Last</a>";$location=mysql_real_escape_string($_POST['location']);$sector =mysql_real_escape_string($_POST['sector']);$salary =mysql_real_escape_string($_POST['salary']);$salary =mysql_real_escape_string($_POST['description']);$salary =mysql_real_escape_string($_POST['Ref']);$salary =mysql_real_escape_string($_POST['title']);if ($location<>""){ $sql_where .= " AND location='".$location."' "; } if ($sector<>""){ $sql_where .= " AND sector='".$sector."' "; } if ($salary<>"") 	   { 		 if ($salary=="50"){ 		 $sql_where .= " AND salary < '50000'"; 		 } 		 if ($salary=="50_75"){ 		 $sql_where .= " AND salary BETWEEN '50000' AND '75000'"; 		 } 		 if ($salary=="75_100"){ 		 $sql_where .= " AND salary BETWEEN '75000' AND '100000'"; 		 } 		 if ($salary=="100_150"){ 		 $sql_where .= " AND salary BETWEEN '100000' AND '105000'"; 		 } 		 if ($salary=="150"){ 		 $sql_where .= " AND salary > '105000'"; 		 }  	   } $result = mysql_query ($sql_select . $sql_from . $sql_where . $sql_order . $sql_limit); if (!$result ) { 	echo("<p>Doh! Error performing query: " . mysql_error($result) . "</p>"); 	exit(); }  if ($row = mysql_fetch_array($result)) { do { print "<div style='width: 659px; padding: 5px 20px; height: 241px; background-image: url(images/search_results_box.jpg); background-repeat: no-repeat;'>";print ("<p>"); print "<img src='images/round_logo.png' alt='' />";print '<b1>' . $row["title"] . '</b1>';print ("<br>");  PRINT "<b> Location: </b> "; print $row["location"]." ";PRINT "<b> Sector: </b> "; print $row["sector"]." "; PRINT "<b> Salary: </b> "; print "$".$row["salary"]." ";PRINT "<b> Reference: </b> "; print $row["Ref"]." ";print ("<br>");  print ("<br>");  PRINT "<b>Description: </b> "; print $row["description"]; print ("<p>");print "<a href='candidates.html'><img src='images/register.png' alt='' /></a>";print " ";print "<img src='images/apply_text.png' alt='' />";print '<a href="info@myemailaddy.com?subject=Job Title: ' . $row['title'] . '. Reference Number: ' . $row['Ref'] . '"><img src="images/apply.png" alt="" /></a>';print " ";print "</div>";} while($row = mysql_fetch_array($result)); } else {print "Sorry, no records were found!";} ?><h2><a href='?start=<? $start ?>'>previous</a>    <a href='?start=<? $start ?>'>next</a></h2>

but all I got was an error saying:

Parse error: syntax error, unexpected T_ECHO in /home/winchest/public_html/search.php on line 79

I'm sure I'm going wrong somewhere, but am not sure where.

[Synook]

* What is line 79?* Why have you got four things assigned to $salary right after each other?* You missed $start = isset($_GET['start']) ? (int) $_GET['start'] : 0; - put it up the top.* Place my code after the mysql_query();* I've realised mysql_num_rows() won't work. After the mysql_query() (but before the pagenation code), put this:

$results = implode(mysql_fetch_assoc(mysql_query ("SELECT COUNT(*) " . $sql_from . $sql_where . $sql_order)));

[unplugged_web]

* What is line 79?* Why have you got four things assigned to $salary right after each other?* You missed $start = isset($_GET['start']) ? (int) $_GET['start'] : 0; - put it up the top.* Place my code after the mysql_query();* I've realised mysql_num_rows() won't work. After the mysql_query() (but before the pagenation code), put this:

$results = implode(mysql_fetch_assoc(mysql_query ("SELECT COUNT(*) " . $sql_from . $sql_where . $sql_order)));

Okay the error I got this time was

Parse error: syntax error, unexpected T_STRING, expecting ',' or ';' in /home/winchest/public_html/search.php on line 127

line 127 is

if ($i != $start) echo ""<a href=\"?start=" . (5 * $i) . "\">";

The code now reads

$result = mysql_query ($sql_select . $sql_from . $sql_where . $sql_order . $sql_limit);$results = implode(mysql_fetch_assoc(mysql_query ("SELECT COUNT(*) " . $sql_from . $sql_where . $sql_order))); $start = isset($_GET['start']) ? (int) $_GET['start'] : 0;$results = mysql_num_rows($result);$pages = floor($results / 5);echo "Page ";for ($i = 0; $i < $pages; $i++ ) {	if ($i != $start) echo ""<a href=\"?start=" . (5 * $i) . "\">";	echo  $i + 1;	if ($i != $start) "</a> ";}echo "<br />";if ($start != 0) echo "<a href=\"?start=0\">First</a> <a href=\"?start=" . ($start - 5) . "\">Previous</a> ";if ($start != $pages * 5) echo "<a href=\"?start=" . ($start + 5) . "\">Next</a> <a href=\"?start=" . ($pages * 5) . "\">Last</a>";if (!$result ) { 	echo("<p>Doh! Error performing query: " . mysql_error($result) . "</p>"); 	exit(); }  if ($row = mysql_fetch_array($result)) { do {

The reason though that there's so many things assigned to $salary is because it's searching between different amounts. I'm not sure if it's the right way of doing things, but it seems to work though

[Synook]

Okay the error I got this time was... if ($i != $start) echo ""<a href=\"?start=" . (5 * $i) . "\">";

There's one to many quotes there. Can you see it?

The code now reads...

Remove

$results = mysql_num_rows($result);

I'm not sure if it's the right way of doing things, but it seems to work though

Well I'm not sure what you are doing, but think about it... what is happening there?

[unplugged_web]

There's one to many quotes there. Can you see it?Remove

$results = mysql_num_rows($result);

Well I'm not sure what you are doing, but think about it... what is happening there?

I removed some of the quotes and I'm not getting any errors now. When I click next or last then the last part of the address is:

%5C?start=

and the page it loads returns a 404 error.

[Synook]

I removed some of the quotes and I'm not getting any errors now. When I click next or last then the last part of the address is: ... and the page it loads returns a 404 error.

Umm... this is confusing do you have a link to your page?Did you remove $results = mysql_num_rows($result); by the way?

[unplugged_web]

Umm... this is confusing do you have a link to your page?Did you remove $results = mysql_num_rows($result); by the way?

Yep I removed that. The search for the site can be seen hereThanks

[Synook]

That link doesn't work

[unplugged_web]

That link doesn't work

sorry, I put . in instead of / try this

[Synook]

I think you removed a few too many quotes there! I only said there was one too many. The contentious line should look like

if ($i != $start) echo "<a href=\"?start=" . (10 * $i) . "\">";

What is your code now?And I have to go now. Try looking at my original code posted, and see the structures.

[unplugged_web]

I think you removed a few too many quotes there! I only said there was one too many. What does the code look like?

Oh, my mistake - I misunderstood what you said. At the moment its:

$result = mysql_query ($sql_select . $sql_from . $sql_where . $sql_order . $sql_limit);$results = implode(mysql_fetch_assoc(mysql_query ("SELECT COUNT(*) " . $sql_from . $sql_where . $sql_order))); $start = isset($_GET['start']) ? (int) $_GET['start'] : 0;$pages = floor($results / 5);echo "Page ";for ($i = 0; $i < $pages; $i++ ) {	if ($i != $start) echo "<a href=\?start= . (5 * $i) . \>";	echo  $i + 1;	if ($i != $start) "</a> ";}echo "<br />";if ($start != 0) echo "<a href=\?start= . ($start - 5) . \>Previous</a> ";if ($start != $pages * 5) echo "<a href=\?start= . ($start + 5) . \>Next</a>";if (!$result ) { 	echo("<p>Doh! Error performing query: " . mysql_error($result) . "</p>"); 	exit(); }  if ($row = mysql_fetch_array($result)) { do { print "<div style='width: 659px; padding: 5px 20px; height: 241px; background-image: url(images/search_results_box.jpg); background-repeat: no-repeat;'>";print ("<p>");

[unplugged_web]

I changed the code so it's now:

$results = implode(mysql_fetch_assoc(mysql_query ("SELECT COUNT(*) " . $sql_from . $sql_where . $sql_order))); $start = isset($_GET['start']) ? (int) $_GET['start'] : 0;$pages = floor($results / 5);for ($i = 0; $i < $pages; $i++ ) {    if ($i != $start) echo "<a href=\"?start=" . (5 * $i) . "\">";    echo  $i + 1;    if ($i != $start) "</a> ";}echo "<br />";if ($start != 0) echo "<a href=\"?start=0\">First</a> <a href=\"?start=" . ($start - 5) . "\">Previous</a> ";if ($start != $pages * 5) echo "<a href=\"?start=" . ($start + 5) . "\">Next</a> <a href=\"?start=" . ($pages * 5) . "\">Last</a>";

but I now get an error saying

Parse error: syntax error, unexpected T_ECHO in /home/west/public_html/search.php on line 124

line 124 is:

    if ($i != $start) echo "<a href=\"?start=" . (5 * $i) . "\">";

[unplugged_web]

Okay. I've now managed to remove the right amount on quotes and it works I'm soooooo happy thank you

[unplugged_web]

Okay. I've now managed to remove the right amount on quotes and it works I'm soooooo happy thank you

Sorry it's stopped working. It worked until I added another $_GET for the order by now I get an error saying

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/west/public_html/search.php on line 121Warning: implode() [function.implode]: Argument to implode must be an array. in /home/west/public_html/search.php on line 121Warning: mysql_error(): supplied argument is not a valid MySQL-Link resource in /home/west/public_html/search.php on line 128

Line 121 is:

$results = implode(mysql_fetch_assoc(mysql_query ("SELECT COUNT(*) " . $sql_from . $sql_where . $sql_order)));

and line 128 is:

echo("<p>Doh! Error performing query: " . mysql_error($result) . "</p>");

The code is now:

Order by: <a href='?orderby=title'>Job</a><a href='?orderby=location'>Location</a><a href='?orderby=sector'>Sector</a></div><?php$con = mysql_connect("server IP", "username", " password");if (!$con)  {  die('Could not connect: ' . mysql_error());  }mysql_select_db("database", $con);$sql_select = "SELECT * "; $sql_from = " FROM table "; $sql_where = " WHERE 1=1";$sql_order = ' ORDER BY '. $_GET['orderby'] .' DESC';$start = (int) $_GET['start'];$sql_limit = " LIMIT $start,5";// $_POST[''] - makes your variables compatible with PHP5 // It'll save you a lot of heartache in the future when your host upgrades// mysql_real_escape_string() - Escapes special characters in your variable// to try and stop idiots wrecking your site - http://uk2.php.net/mysql_real_escape_string // this is the minimum ammount of protection you should use when accessing your DB.$location=mysql_real_escape_string($_POST['location']);$sector =mysql_real_escape_string($_POST['sector']);$salary =mysql_real_escape_string($_POST['salary']);$salary =mysql_real_escape_string($_POST['description']);$salary =mysql_real_escape_string($_POST['Ref']);$salary =mysql_real_escape_string($_POST['title']);if ($location<>""){ $sql_where .= " AND location='".$location."' "; } if ($sector<>""){ $sql_where .= " AND sector='".$sector."' "; } if ($salary<>"") 	   { 		 if ($salary=="50"){ 		 $sql_where .= " AND salary < '50000'"; 		 } 		 if ($salary=="50_75"){ 		 $sql_where .= " AND salary BETWEEN '50000' AND '75000'"; 		 } 		 if ($salary=="75_100"){ 		 $sql_where .= " AND salary BETWEEN '75000' AND '100000'"; 		 } 		 if ($salary=="100_150"){ 		 $sql_where .= " AND salary BETWEEN '100000' AND '105000'"; 		 } 		 if ($salary=="150"){ 		 $sql_where .= " AND salary > '105000'"; 		 }  	   } $result = mysql_query ($sql_select . $sql_from . $sql_where . $sql_order . $sql_limit);$results = implode(mysql_fetch_assoc(mysql_query ("SELECT COUNT(*) " . $sql_from . $sql_where . $sql_order))); $start = isset($_GET['start']) ? (int) $_GET['start'] : 0;$pages = floor($results / 5);

[justsomeguy]

Print the query you're building to see what it looks like.

[unplugged_web]

Print the query you're building to see what it looks like.

How do I do that?I used:

if (!$result ) { echo(print "<p> . mysql_error() . </p>"); exit(); }

but it didn't return any errors other than the ones I got before

[justsomeguy]

echo $sql_select . $sql_from . $sql_where . $sql_order . $sql_limit;After you've built it. You need to print what you're sending to the SQL server to see why it's failing.

[unplugged_web]

echo $sql_select . $sql_from . $sql_where . $sql_order . $sql_limit;After you've built it. You need to print what you're sending to the SQL server to see why it's failing.

I put it right at the end and it said

SELECT * FROM jobs WHERE 1=1 ORDER BY location DESC LIMIT 0,5

I think that it's this line

$results = implode(mysql_fetch_assoc(mysql_query ("SELECT COUNT(*) " . $sql_from . $sql_where . $sql_order)));

that is causing the problem because I always get an error saying:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/west/public_html/search.php on line 121Warning: implode() [function.implode]: Argument to implode must be an array. in /home/west/public_html/search.php on line 121

[justsomeguy]

Print out that query as well. Print anything you think might be the problem, it's the only way to see what's going on. If that's line 121 then print that stuff out. You can also try copying and pasting what gets printed and run that directly on phpMyAdmin.