Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Jan 2008
    Posts
    216
    Thanks
    10
    Thanked 1 Time in 1 Post

    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:

    PHP Code:
    <!--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($resultMYSQL_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>'
      }
    ?>

  • #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,020
    Thanks
    2
    Thanked 313 Times in 305 Posts
    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.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #3
    Regular Coder
    Join Date
    Jan 2008
    Posts
    216
    Thanks
    10
    Thanked 1 Time in 1 Post
    Quote Originally Posted by CFMaBiSmAd View Post
    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?

  • #4
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,020
    Thanks
    2
    Thanked 313 Times in 305 Posts
    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
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #5
    Regular Coder
    Join Date
    Jan 2008
    Posts
    216
    Thanks
    10
    Thanked 1 Time in 1 Post
    Quote Originally Posted by CFMaBiSmAd View Post
    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

  • #6
    Regular Coder
    Join Date
    Jan 2008
    Posts
    216
    Thanks
    10
    Thanked 1 Time in 1 Post
    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:

    Code:
    <?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&#37;' 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>'; 
      }
    ?>
    Last edited by PRodgers4284; 03-13-2008 at 03:01 PM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •