[SOLVED] Mysql Join and pagination

[stan801003]

Ok i have put together a mysql join query with a sub query to allow me to get all the information i need from 4 database tables.

 

Now i have managed to get this to work fine, it returns all the information i need perfectly.  The problem i am having is with the pagination.

 

I have a script that i use for everything and it works great however i can not get it to work with this query. 

 

When the page is called it displays all of the information as should be and places the individual page numbers at the bottom of the screen

 

but still display all of the rows within the database.

 

I would be very grateful for any advice or guidance,

 

Stan

 

if(!isset($_GET['page'])){
      $page = 1;
  } else {
      $page = $_GET['page'];
  }
  
  // Define the number of results per page
  $max_results = 1;
  
  // Figure out the limit for the query based
  // on the current page number.
  $from = (($page * $max_results) - $max_results);
  
  // Perform MySQL query on only the current page number's results
  
$candidate_id =3;

$sql = "SELECT joborder.title, joborder.site_id,  candidate_joborder.date_submitted, candidate_joborder.status,candidate_joborder_status.short_description, candidate_joborder.site_id


FROM joborder JOIN candidate_joborder ON joborder.joborder_id = candidate_joborder.joborder_id 
JOIN 
candidate_joborder_status ON candidate_joborder_status.candidate_joborder_status_id = candidate_joborder.status  

WHERE candidate_id = '$candidate_id' ORDER BY title";
$result = mysql_query($sql) or die(mysql_error());
    
//Iterate through each factory
if ($result) {
	while($row = mysql_fetch_array($result)) {

		//Now create a second query to get details for this factory
		$sql = "select name from site where site_id=" . $row['site_id'];
		$result2 = mysql_query($sql) or die(mysql_error()); 
		$row2 = mysql_fetch_array($result2);
		//Generate <set name='..' value='..' />        

		mysql_free_result($result2);

</td>

            </tr>

          </table>

        <table width="100%" border="0">

            <tr>

              <td width="115" bgcolor="#E2E6EF"><?php echo $row['title']; ?> </td>

              <td width="121" bgcolor="#E2E6EF"><?php echo $row2['name']; ?> </td>

              <td width="155" bgcolor="#E2E6EF"><?php echo $row['short_description']; ?> </td>

              <td width="134" bgcolor="#E2E6EF"><?php echo $row['date_submitted']; ?> </td>

            </tr>

          </table>

        <table width="100%" border="0">

            <tr>

	}
}
  
  
  // Figure out the total number of results in DB:
  $total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM joborder JOIN candidate_joborder ON joborder.joborder_id = candidate_joborder.joborder_id 
JOIN 
candidate_joborder_status ON candidate_joborder_status.candidate_joborder_status_id = candidate_joborder.status  

WHERE candidate_id = '$candidate_id' ORDER BY title"),0);
  
  // Figure out the total number of pages. Always round up using ceil()
  $total_pages = ceil($total_results / $max_results);
  
  // Page Number Hyperlinks

  
  // Previous Link
if($page > 1){
       $prev = ($page - 1);
     echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev&candidate_id=$candidate_id\"><<Back</a> ";
   }
   // Page Numbers
   for($i = 1; $i <= $total_pages; $i++){
   if(($page) == $i){
   echo "<strong><font size=2 face=calibri color=#336699>Page $i of $total_pages Total Pages</font></strong>";
   } else {
   echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i&candidate_id=$candidate_id\">[ $i ]</a> ";
   }
   if(($i + 30) % 30 == 0)
   {
   echo '</td></tr><tr><td colspan=4 align=center>';
   }
   }
   
   // Next Link
   if($page < $total_pages){
       $next = ($page + 1);
     echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next&candidate_id=$candidate_id\">Next>></a>";
   }
   echo "</center>";
   

[stan801003]

In case anybody wants to know the answer i forgot to add the max results part to the first query, so it's all working now