title: Complicated Query + Pagination Script
body: <p>Problem 1:  Pagination on my script is not working.  After 20 entries, it only shows the most recent 20 entries and doesn't split them into different pages.  Code is below:</p>
  
  <p>Problem 2: I am using the same pagination script for something else and over there it splits correctly but on the next page it shows the same results from page 1. I am using the same script except for the second script the:</p>
  
  <pre><code>       $query = "SELECT COUNT(*) as num FROM table where id = 
        '$uid' ORDER BY id DESC"; 
  </code></pre>
  
  <p>and the SQL for that is:</p>
  
  <pre><code>       $sql="SELECT table_one.field_id, table_constant.field_name,
        table_one.field_2, table_one.field_3 FROM table_one LEFT 
        JOIN table_constant ON table_one.common_field 
        = table_constant.common_field WHERE table_constant.u_id = '$uid'"; 
  </code></pre>
  
  <p>Code:  </p>
  
  <pre><code> &lt;?php
  
  $tbl_name="";       //not using this since i am doing a union
  
  $adjacents = 3;
  
  $query = "SELECT COUNT(*) as num
      from table_one LEFT JOIN table_constant on table_one.c_id 
      = table_constant.c_id 
      where table_constant.user_id = '$uid'
      UNION
      SELECT COUNT(*) as num
      from table_two LEFT JOIN table_constant on table_two.c_id 
      = table_constant.c_id 
      where table_two.added_by = '$uid'
      UNION
      SELECT COUNT(*) as num
      from table_three LEFT JOIN table_constant ON table_three.c_id 
      = table_constant.c_id
      where table_constant.user_id = '$uid'
      UNION
      SELECT COUNT(*) as num
      from table_four LEFT JOIN table_constant ON table_four.c_id 
      = table_constant.c_id
      where table_constant.user_id = '$uid'
      ORDER BY date_time_added DESC";
  $total_pages = mysql_fetch_array(mysql_query($query));
  $total_pages = $total_pages[num];
  
  $targetpage = "page.php"; 
  $limit = 20;                                
  $page = $_GET['page'];
  if($page) 
      $start = ($page - 1) * $limit; //first item to display on this page
  else
      $start = 0; //if no page var is given, set start to 0
  
  $sql = "select table_one.field1, table_constant.field1, 
      table_one.field2, table_one.field3, table_one.field4, 
      table_one.field5, table_constant.c_id
      from table_one LEFT JOIN table_constant on table_one.field1 
      = table_constant.c_id 
      where table_constant.user_id = '$uid'
      UNION
      select table_two.field1, table_constant.field1, table_two.field2, 
      table_two.field3,    table_two.field4, table_two.field5, table_constant.c_id
      from table_two LEFT JOIN table_constant on table_two.c_id 
      = table_constant.c_id 
      where table_two.added_by = '$uid'
      UNION 
      select table_three.field1, table_constant.field1, table_three.field2, 
      table_three.field3, table_three.field4, table_three.field5,
      table_constant.c_id
      from table_three LEFT JOIN table_constant ON table_three.c_id 
      = table_constant.c_id
      where table_constant.user_id = '$uid'
      UNION
      select table_four.field1, table_constant.field1, table_four.field2, 
      table_four.field3, table_four.field4, table_four.field5, 
      table_constant.c_id
      from table_four LEFT JOIN table_constant ON table_four.c_id 
      = table_constant.c_id
      where table_constant.user_id = '$uid'
      ORDER BY date DESC LIMIT $start, $limit";
  $result = mysql_query($sql);
  
      $query = mysql_query($sql) or die ("Error: ".mysql_error());
  
      $result = mysql_query($sql);
  
      if ($result == "")
      {
      echo "";
      }
      echo "";
  
  
      $rows = mysql_num_rows($result);
  
      if($rows == 0)
      {
      print("");
  
       }
       elseif($rows &gt; 0)
        {
        while($row = mysql_fetch_array($query))
        {
  
        $fields = $row['field'];  //Table one Field 1
       $fields2 = $row['field']; //Table Constant Field 1
       $fields3 = $row['field'];// Table One field 4
       $fields4 = $row['field'];//Table Constant Field 2
  
  
      print("$fields&lt;br&gt;$fields2&lt;br&gt;$fields3&lt;br&gt;$fields4");
      }
  
      }
  
  
      if(mysql_num_rows($result) &lt; 1) {
      echo "";
      }
  
  
  /* Setup page vars for display. */
  if ($page == 0) $page = 1;  //if no page var is given, default to 1.
                      //next page is page + 1
  $lastpage = ceil($total_pages/$limit);  
     //lastpage is = total pages / items per page, 
     rounded up.
  $lpm1 = $lastpage - 1;  //last page minus 1
  
  /* 
  Now we apply our rules and draw the pagination object. 
  We're actually saving the code to a variable in case we 
      want to draw it more than once.
  */
  $pagination = "";
  if($lastpage &gt; 1)
  {   
      $pagination .= "&lt;div class=\"pagination\"&gt;&lt;/div&gt;";
      //previous button
      if ($page &gt; 1) 
          $pagination.= "";
      else
          $pagination.= "";   
  
      //pages 
      if ($lastpage &lt; 7 + ($adjacents * 2))   
             //not enough pages to bother breaking it up
      {   
          for ($counter = 1; $counter &lt;= $lastpage; $counter++)
          {
              if ($counter == $page)
  
           $pagination.= "&lt;span class=\"current\"&gt;$counter &amp;nbsp&lt;/span&gt;";
              else
                  $pagination.= "&lt;a id=\"numberhighlighter\" href=\"$targetpage?page=$counter\"&gt;$counter &amp;nbsp&lt;/a&gt;";                  
          }
      }
      elseif($lastpage &gt; 5 + ($adjacents * 2))    //enough pages to hide some
      {
          //close to beginning; only hide later pages
          if($page &lt; 1 + ($adjacents * 2))        
          {
              for ($counter = 1; $counter &lt; 4 + ($adjacents * 2); $counter++)
              {
                  if ($counter == $page)
                      $pagination.= "&lt;span class=\"current\"&gt;$counter &amp;nbsp&lt;/span&gt;";
                  else
                      $pagination.= "&lt;a href=\"$targetpage?page=$counter\"&gt;$counter &amp;nbsp &lt;/a&gt;";                  
              }
              $pagination.= "...";
              $pagination.= "&lt;a href=\"$targetpage?page=$lpm1\"&gt;$lpm1&lt;/a&gt;";
              $pagination.= "&lt;a href=\"$targetpage?page=$lastpage\"&gt;$lastpage&lt;/a&gt;";       
          }
          //in middle; hide some front and some back
          elseif($lastpage - ($adjacents * 2) &gt; $page &amp;&amp; $page &gt; ($adjacents * 2))
          {
              $pagination.= "&lt;a href=\"$targetpage?page=1\"&gt;1&lt;/a&gt;";
              $pagination.= "&lt;a href=\"$targetpage?page=2\"&gt;2&lt;/a&gt;";
              $pagination.= "...";
              for ($counter = $page - $adjacents; $counter &lt;= $page + $adjacents; $counter++)
              {
                  if ($counter == $page)
                      $pagination.= "&lt;span class=\"current\"&gt;$counter&lt;/span&gt;";
                  else
                      $pagination.= "&lt;a href=\"$targetpage?page=$counter\"&gt;$counter&lt;/a&gt;";                 
              }
              $pagination.= "...";
              $pagination.= "&lt;a href=\"$targetpage?page=$lpm1\"&gt;$lpm1&lt;/a&gt;";
              $pagination.= "&lt;a href=\"$targetpage?page=$lastpage\"&gt;$lastpage&lt;/a&gt;";       
          }
          //close to end; only hide early pages
          else
          {
              $pagination.= "&lt;a href=\"$targetpage?page=1\"&gt;1&lt;/a&gt;";
              $pagination.= "&amp;nbsp &amp;nbsp &amp;nbsp&lt;a href=\"$targetpage?page=2\"&gt;2&amp;&lt;/a&gt;";
              $pagination.= "...";
              for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter &lt;= $lastpage; $counter++)
              {
                  if ($counter == $page)
                      $pagination.= "&lt;span class=\"current\"&gt;$counter&lt;/span&gt;";
                  else
                      $pagination.= "&lt;a href=\"$targetpage?page=$counter\"&gt;$counter&lt;/a&gt;";                 
              }
          }
      }
  
      //next button
      if ($page &lt; $counter - 1) 
          $pagination.= "";
      else
          $pagination.= "";       
  }
                ?&gt;
             &lt;div id="page"&gt;
              &lt;?php 
  
             print("$pagination");
  
                ?&gt;
  </code></pre>
  
  <p>Thanks!</p>