Some help required

I am trying to add some paging code to a search script, the search script works fine without the paging code, i have managed to get the paging script working will another form to view records from the database, i just having some difficulty getting it to work with the search script, can anyone provide some help or advice to where im going wrong, im getting the 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 'WHERE jobcatergory LIKE 'Construction%' AND employmenttype LIKE '%permanent full' at line 1"

My script is:

<!--Search Job form -->
Find a jobseekers using the CV Search, carrying out searches based on Job
category the person is in and Employment type that they are looking for. You must select both of the options below to carryout a search<form method="POST" action="">
<fieldset>
<span class="navyboldtxt">
<label for="jobcatergory">Job Category:&nbsp; </label></span>
<select name="jobcatergory">
<option value="Please Select">Please Select</option>
<?php
$jobcatergory_opts = array(
"Accountancy and Finance",
"Banking and Insurance",
"Construction",
"Customer Service",
"Engineering",
"Management",
"Hotel and Catering",
"Information Technology",
"Legal",
"Marketing",
"Medical",
"Retail",
"Sales",
"Secretarial",
"Transport and Distribution",
"Working from home",
);
foreach($jobcatergory_opts as $opt){
$selected = $_POST['jobcatergory'] == $opt ? " selected=true":"";
print "<option value=\"{$opt}\"{$selected}>{$opt}</option>";
}
?>
</select><p></p>
<p><label for="employmenttype"><span class="navyboldtxt">Employment Type:</label></span>
<select name="employmenttype">
<option value="Please Select">Please Select</option>
<?php
$employmenttype_opts = array(
"permanent fulltime",
"permanent parttime",
"temporary fulltime",
"temporary parttime",
);
foreach($employmenttype_opts as $opt){
$selected = $_POST["employmenttype"] == $opt ? " selected=true":"";
echo "<option value=\"" . $opt . "\"" . $selected . ">" . $opt . "</option>";
}
?>
</select><p></p>
<input type="submit" value="Search" name="submit" /></p>
</fieldset>
</form>
<?php
// how many rows to show per page
$rowsPerPage = 4;

// by default we show first page
$pageNum = 1;

// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
$pageNum = $_GET['page'];
}

// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;

if ($_POST['submit']){

$query = "SELECT * FROM users LIMIT $offset, $rowsPerPage";

$jobcat = mysql_real_escape_string(trim($_POST['jobcatergory']));
$emptype = mysql_real_escape_string(trim($_POST['employmenttype']));

if ($jobcat != '' && $emptype != '') {
$sql .= " WHERE jobcatergory LIKE '$jobcat%' AND employmenttype LIKE '%$emptype%'";
}
else if ($jobcat != '' && $emptype == '') {
$sql .= " WHERE jobcatergory LIKE '%$jobcat%'";
}
else if ($emptype != '' && $jobcat == '') {
$sql .= " WHERE employmentype LIKE '%$emptype%'";
}

$query = mysql_query($sql) or die(mysql_error());

if(mysql_num_rows($query) > 0)
{
while ($job = mysql_fetch_array($query))
{
$username=$job["username"];
$jobcatergory=$job["jobcatergory"];
$employmenttype=$job["employmenttype"];
?>

<table class="sofT" cellspacing="0">

<tr>
<td class="Header">Username</td>
<td class="Header">Job Category</td>
<td class="Header">Employment Type</td>
<td class="Header">View CV</td>
<td class="Header">Contact</td>
</tr>
<tr>
<td class="Body"><?php echo $job["username"]; ?></td>
<td class="Body"><?php echo $job["jobcatergory"]; ?></td>
<td class="Body"><?php echo $job["employmenttype"]; ?></td>
<td class="Body"><?php echo "<a href='searchcvview.php?username=$username'>View CV</a>"?></td>
<td class="Body"><?php echo "<a href='searchcontactcv.php?username=$username'>Contact</a>"?></td>
</tr>
<br>
</table>
<?php
}
echo '<br>';
echo '<br>';

// how many rows we have in database
$query = "SELECT COUNT(id) AS numrows FROM job";
$result = mysql_query($query) or die('Error, query failed');
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];

// how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);

// print the link to access each page
$self = $_SERVER['PHP_SELF'];
$nav = '';
for($page = 1; $page <= $maxPage; $page++)
{
if ($page == $pageNum)
{
$nav .= " $page "; // no need to create a link to current page
}
else
{
$nav .= " <a href=\"$self?page=$page\">$page</a> ";
}
}

// creating previous and next link
// plus the link to go straight to
// the first and last page

if ($pageNum > 1)
{
$page = $pageNum - 1;
$prev = " <a href=\"$self?page=$page\">[Prev]</a> ";

$first = " <a href=\"$self?page=1\">[First Page]</a> ";
}
else
{
$prev = '&nbsp;'; // we're on page one, don't print previous link
$first = '&nbsp;'; // nor the first page link
}

if ($pageNum < $maxPage)
{
$page = $pageNum + 1;
$next = " <a href=\"$self?page=$page\">[Next]</a> ";

$last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
}
else
{
$next = '&nbsp;'; // we're on the last page, don't print next link
$last = '&nbsp;'; // nor the last page link
}

// print the navigation link
echo $first . $prev . $nav . $next . $last;
}
}
else
{
echo '<p>There are no search results with the search criteria you entered.</p>';
}
?>

Echoing your whole query would help, but it appears that you have a LIMIT clause in your query, followed by a WHERE clause. That is in the wrong order.

Echoing your whole query would help, but it appears that you have a LIMIT clause in your query, followed by a WHERE clause. That is in the wrong order.


Thanks for the reply, where can i put the LIMIT clause, im unsure how to do this?

The syntax prototype statement in the manual shows the permitted order of elements - http://dev.mysql.com/doc/refman/5.0/en/select.html

For a SELECT query, the order of the commonly used elements is -

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

The syntax prototype statement in the manual shows the permitted order of elements - http://dev.mysql.com/doc/refman/5.0/en/select.html

For a SELECT query, the order of the commonly used elements is -

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

Thanks for your help :)

I made the changes to the code but i cant get the search displaying the records now, im not getting any errors:

My code is now:

<?php
// how many rows to show per page
$rowsPerPage = 4;

// by default we show first page
$pageNum = 1;

// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
$pageNum = $_GET['page'];
}

// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;

if ($_POST['submit']){

$sql = "SELECT * FROM users";

$jobcat = mysql_real_escape_string(trim($_POST['jobcatergory']));
$emptype = mysql_real_escape_string(trim($_POST['employmenttype']));

if ($jobcat != '' && $emptype != '') {
$sql .= " WHERE jobcatergory LIKE '$jobcat%' AND employmenttype LIKE '%$emptype%'";
}
else if ($jobcat != '' && $emptype == '') {
$sql .= " WHERE jobcatergory LIKE '%$jobcat%'";
}
else if ($emptype != '' && $jobcat == '') {
$sql .= " WHERE employmentype LIKE '%$emptype%'";
}

$sql .= " LIMIT $offset, $rowsPerPage";

$query = mysql_query($sql) or die(mysql_error());

if(mysql_num_rows($query) > 0)
{
while ($job = mysql_fetch_array($query))
{
$username=$job["username"];
$jobcatergory=$job["jobcatergory"];
$employmenttype=$job["employmenttype"];
?>

<table class="sofT" cellspacing="0">

<tr>
<td class="Header">Username</td>
<td class="Header">Job Category</td>
<td class="Header">Employment Type</td>
<td class="Header">View CV</td>
<td class="Header">Contact</td>
</tr>
<tr>
<td class="Body"><?php echo $job["username"]; ?></td>
<td class="Body"><?php echo $job["jobcatergory"]; ?></td>
<td class="Body"><?php echo $job["employmenttype"]; ?></td>
<td class="Body"><?php echo "<a href='searchcvview.php?username=$username'>View CV</a>"?></td>
<td class="Body"><?php echo "<a href='searchcontactcv.php?username=$username'>Contact</a>"?></td>
</tr>
<br>
</table>
<?php
}
echo '<br>';
echo '<br>';

// how many rows we have in database
$query = "SELECT COUNT(id) AS numrows FROM users";
$result = mysql_query($query) or die('Error, query failed');
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];

// how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);

// print the link to access each page
$self = $_SERVER['PHP_SELF'];
$nav = '';
for($page = 1; $page <= $maxPage; $page++)
{
if ($page == $pageNum)
{
$nav .= " $page "; // no need to create a link to current page
}
else
{
$nav .= " <a href=\"$self?page=$page\">$page</a> ";
}
}

// creating previous and next link
// plus the link to go straight to
// the first and last page

if ($pageNum > 1)
{
$page = $pageNum - 1;
$prev = " <a href=\"$self?page=$page\">[Prev]</a> ";

$first = " <a href=\"$self?page=1\">[First Page]</a> ";
}
else
{
$prev = '&nbsp;'; // we're on page one, don't print previous link
$first = '&nbsp;'; // nor the first page link
}

if ($pageNum < $maxPage)
{
$page = $pageNum + 1;
$next = " <a href=\"$self?page=$page\">[Next]</a> ";

$last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
}
else
{
$next = '&nbsp;'; // we're on the last page, don't print next link
$last = '&nbsp;'; // nor the last page link
}

// print the navigation link
echo $first . $prev . $nav . $next . $last;
}
}
else
{
echo '<p>There are no search results with the search criteria you entered.</p>';
}
?>