Some Help Required (Limit Clause)

[PRodgers4284]

I am having some problem with a search script that includes paging code. I have the search working without the paging code included, i am trying to add include the page code within the search script. Im having difficulty with the "LIMIT" clause in the query, i know this should be after the "WHERE" clause but im not sure where to include it, can anyone help me or provide some advice? I would really appreciate it.

 

My code is:

 

<?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  = ' '; // we're on page one, don't print previous link
$first = ' '; // 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 = ' '; // we're on the last page, don't print next link
$last = ' '; // 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>'; 
  }
?>

 

 

[MadTechie]

After a quick review i updated 1 line and added another see the //MT: comments

 

<?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"; //MT:Updated

$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"; //MT: ADDED

$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  = ' '; // we're on page one, don't print previous link
$first = ' '; // 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 = ' '; // we're on the last page, don't print next link
$last = ' '; // 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>'; 
  }
?>

 

[PRodgers4284]

After a quick review i updated 1 line and added another see the //MT: comments

 

<?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"; //MT:Updated

$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"; //MT: ADDED

$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  = ' '; // we're on page one, don't print previous link
$first = ' '; // 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 = ' '; // we're on the last page, don't print next link
$last = ' '; // 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>'; 
  }
?>

 

 

Hey thanks for your help on that, really appreciate it. Theres something wrong with the query though, i doesnt same to display the records in the table now, have a missed something in the code?

 

My full code is:

 

<fieldset>    
<span class="navyboldtxt">    
<label for="jobcatergory">Job Category:  </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']){

$sql = "SELECT * FROM users"; //MT:Updated

$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"; //MT: ADDED

$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  = ' '; // we're on page one, don't print previous link
$first = ' '; // 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 = ' '; // we're on the last page, don't print next link
$last = ' '; // 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>'; 
  }
?>

[MadTechie]

erm.. okay

can you echo the SQL and let me know what its dsiplays

$sql .= " LIMIT $offset, $rowsPerPage"; //MT: ADDED
echo $sql;//add this line

[PRodgers4284]

erm.. okay

can you echo the SQL and let me know what its dsiplays

$sql .= " LIMIT $offset, $rowsPerPage"; //MT: ADDED
echo $sql;//add this line

 

Ok ive solved the problem  , i was selecting the wrong table "job" I have changed the line:

 

$query  = "SELECT COUNT(id) AS numrows FROM job";

 

to:

 

$query  = "SELECT COUNT(username) AS numrows FROM users";

 

The problem now is that it counts all the records in the table "users". I only want it to count the number of records in the table of results that is outputted in the table from the search criteria that was entered.

 

 

[MadTechie]

i think this is was you want

 

i think 4 updates, basically had the "select" , "where" and "Limit" in 3 parts and reuse the "where" on the count

 

<fieldset>    
<span class="navyboldtxt">    
<label for="jobcatergory">Job Category:  </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']){

$select = "SELECT * FROM users"; //MT:Updated + updated
$sql = ""; //MT:Added

$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%'"; 
} 

$limit =" LIMIT $offset, $rowsPerPage"; //MT: ADDED + updated

$query = mysql_query($select.$sql.$limit) or die(mysql_error()); //MT: updated

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.$sql) or die('Error, query failed'); //MT: updated
$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  = ' '; // we're on page one, don't print previous link
$first = ' '; // 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 = ' '; // we're on the last page, don't print next link
$last = ' '; // 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>'; 
  }
?>

[PRodgers4284]

Hey thanks for that, that worked fine. The error message at very bottom of the code is supposed to display if there is nothing that matches the search criteria entered. The error only message appears once the search page is opened, i have the dropdown boxes set to "Please Select" by default and the error "There are no search results with the search criteria you entered" appears. How can i change this to only appear if the user selects values from the dropdown boxes and this is nothing that matches the search criteria?

 

Thanks

 

 

[MadTechie]

change from

// 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>'; 
  }
?>

 

to

 

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

 

thats from "// print the navigation link" to the end of the code  "?>"

 

thats should do it

[PRodgers4284]

That worked fine, i really appreciate your help 

 

Thanks again

[MadTechie]

woohoo 2 thread solved within minutes of each other 

 

your welcome,