Jump to content

Pagination


pazafuera

Recommended Posts

Hello everybody!  I'm completely new to PHP and have summoned the depths of my PHP knowledge to get into the problems I currently have.

 

I have a search form for users, the results of which get passed to the results page.

 

I build dynamic content for the WHERE clause on the results page based on what the users selected on the search form.  My SQL statement looks like this:

 

$t = mysql_query("SELECT * FROM `jos_jobfeeds` WHERE $query;");

 

$query is the dynamic WHERE content.

 

That all works fine and dandy.

 

My problem is that I found a PHP pagination script that I desparately need.  That works until I try to go to a page past the first.  I think it is because I am losing the $query content when the next page of results is loaded.  I've tried a few little tweaks, but to no avail.  I am flying by the seat of my pants and can't seem to figure this one out.

 

Any ideas?  The code:

 

<?php

 

$query="";

 

if ($_POST[searchKeywords]<>"")

  $query="jobtitle LIKE '$_POST[searchKeywords]' OR employer LIKE '$_POST[searchKeywords]' OR excerpt LIKE '$_POST[searchKeywords]' OR description LIKE '$_POST[searchKeywords]'";

 

if ($_POST[Amateur]<>"")

  {

  if ($query <> "")

    $query=$query." OR ";

  $query=$query."jobsegment = '$_POST[Amateur]'";

  }

 

if ($_POST[Corporate]<>"")

  {

  if ($query <> "")

    $query=$query." OR ";

  $query= $query."jobsegment = '$_POST[Corporate]'";

  }

 

if ($_POST[Facilities]<>"")

  {

  if ($query <> "")

    $query=$query." OR ";

  $query= $query."jobsegment = '$_POST[Facilities]'";

  }

 

if ($_POST[Health]<>"")

  {

  if ($query <> "")

    $query=$query." OR ";

  $query= $query."jobsegment = '$_POST[Health]'";

  }

 

if ($_POST[Professional]<>"")

  {

  if ($query <> "")

    $query=$query." OR ";

  $query= $query."jobsegment = '$_POST[Professional]'";

  }

 

if ($_POST[sporting]<>"")

  {

  if ($query <> "")

    $query=$query." OR ";

  $query= $query."jobsegment = '$_POST[sporting]'";

  }

 

if ($_POST[Location]="Region")

  {

  if ($_POST[West]<>"")

    {

    if ($query <> "")

      $query=$query." OR ";

    $query= $query."jobregion = '$_POST[West]'";

    }

   

  if ($_POST[Central]<>"")

    {

    if ($query <> "")

      $query=$query." OR ";

    $query= $query."jobregion = '$_POST[Central]'";

    }

   

  if ($_POST[Northeast]<>"")

    {

    if ($query <> "")

      $query=$query." OR ";

    $query= $query."jobregion = '$_POST[Northeast]'";

    }

   

  if ($_POST[southeast]<>"")

    {

    if ($query <> "")

      $query=$query." OR ";

    $query= $query."jobregion = '$_POST[southeast]'";

    }

   

  if ($_POST[Canada]<>"")

    {

    if ($query <> "")

      $query=$query." OR ";

    $query= $query."jobregion = '$_POST[Canada]'";

    }

  }

if ($_POST[Location]="State")

  {

  if ($_POST[state]<>"")

    {

    if ($query <> "")

      $query=$query." OR ";

  $query= $query."jobstateabbrv = '$_POST[state]'";

    }

  }

 

 

$t = mysql_query("SELECT * FROM `jos_jobfeeds` WHERE $query;");

  if(!$t) die(mysql_error());

   

$a                  = mysql_fetch_object($t);

$total_items      = mysql_num_rows($t);

$limit            = $_GET['limit'];

$type            = $_GET['type'];

$page            = $_GET['page'];

 

//set default if: $limit is empty, non numerical, less than 10, greater than 50

if((!$limit)  || (is_numeric($limit) == false) || ($limit < 10) || ($limit > 50)) {

    $limit = 50; //default

}

//set default if: $page is empty, non numerical, less than zero, greater than total available

if((!$page) || (is_numeric($page) == false) || ($page < 0) || ($page > $total_items)) {

      $page = 1; //default

}

 

//calcuate total pages

$total_pages    = ceil($total_items / $limit);

$set_limit          = $page * $limit - ($limit);

 

$q = mysql_query("SELECT * FROM `jos_jobfeeds` WHERE $query LIMIT $set_limit, $limit");

  if(!$q) die(mysql_error());

    $err = mysql_num_rows($q);

      if($err == 0) die("No matches met your criteria.");

 

echo(" 

<a href=http://www.mysite.com/index.php?option=com_content&task=view&id=375&Itemid=271&cat=$cat&limit=10&page=1>10</a> |

<a href=http://www.mysite.com/index.php?option=com_content&task=view&id=375&Itemid=271&cat=$cat&limit=25&page=1>25</a> |

<a href=http://www.mysite.com/index.php?option=com_content&task=view&id=375&Itemid=271&cat=$cat&limit=50&page=1>50</a>");

 

//show data matching query:

 

 

echo ("<table border='0' width='100%' bordercolor='#000000'>");

echo ("<tr>");

echo ("<td valign='top'>");

echo ("<table border='0' width='100%' cellspacing='0' cellpadding='4' style='border-left-width: 0px; border-right-width: 0px'>");

echo ("<tr>");

echo ("<td><h3>Results of Your Search</h3></td>");

echo ("</tr>");

echo ("<tr>");

echo ("<td style='border-style: solid; border-width: 1px' bordercolor='#CBDDF1' bgcolor='#CBDDF1'>");

echo ("<table border='0' width='100%'>");

echo ("<tr>");

echo ("<td width='10%'><font color='#000000'><b>DATE</b></font></td>");

echo ("<td width='35%'><font color='#000000'><b>JOB TITLE</b></font></td>");

echo ("<td width='35%'><font color='#000000'><b>EMPLOYER</b></font></td>");

echo ("<td width='15%'><font color='#000000'><b>LOCATION</b></font></td>");

echo ("</tr>");

echo ("</table>");

echo ("</td>");

echo ("</tr>");

while($code = mysql_fetch_object($q)) {

echo ("<tr>");

echo ("<td style='border-style: solid; border-width: 1px' bordercolor='#CBDDF1'>");

echo ("<table border='0' width='100%' height='31' cellpadding='0'>");

echo ("<tr>");

echo ("<td bgcolor='#EDF3FA' height='20' style='border-bottom-style: solid; border-bottom-width: 1px'>");

echo ("<table border='0' cellspacing='0' cellpadding='2' width='100%' height='35'>");

echo ("<tr>");

echo ("<td width='10%' height='20'>$code->lastupdatedate</td>");

echo ("<td width='35%' height='20' valign='top'><a href='http://www.sportscareers.com/index.php?option=com_content&task=view&id=372&Itemid=269&jobid=$code->jobid'><b>$code->jobtitle</b></a></td>");

echo ("<td width='25%' height='20' valign='top'>$code->employer</td>");

echo ("<td width='25%' height='20' valign='top'>$code->jobcity, $code->jobstate</td>");

echo ("</tr>");

echo ("<tr>");

echo ("<td width='10%'> </td>");

echo ("<td width='85%' colspan='3' valign='top'>$code->excerpt</td>");

echo ("</tr>");

echo ("</table>");

echo ("</td>");

echo ("</tr>");

echo ("</table>");

echo ("</td>");

echo ("</tr>");

}

echo ("</table>");

echo ("</td>");

echo ("</tr>");

echo ("</table>");

 

 

$cat = urlencode($cat); //makes browser friendly

 

$prev_page = $page - 1;

 

if($prev_page >= 1) {

  echo("<b><<</b> <a href=http://www.mysite.com/index.php?option=com_content&task=view&id=375&Itemid=271&cat=$cat&limit=$limit&page=$prev_page><b>Prev.</b></a>");

}

 

 

for($a = 1; $a <= $total_pages; $a++)

{

  if($a == $page) {

      echo("<b> $a</b> | "); //no link

    } else {

  echo("  <a href=http://www.mysite.com/index.php?option=com_content&task=view&id=375&Itemid=271&cat=$cat&limit=$limit&page=$a> $a </a> | ");

    }

}

 

 

$next_page = $page + 1;

if($next_page <= $total_pages) {

  echo("<a href=http://www.mysite.com/index.php?option=com_content&task=view&id=375&Itemid=271&cat=$cat&limit=$limit&page=$next_page><b>Next</b></a> > >");

}

 

 

?>

Link to comment
Share on other sites

Well, since you put the COED within QUOTE tags the links are being processed, so it is difficult to see how the links are being built. I would guess the problem is that you are passing all the WHERE clauses as POST variables, but the pagination script is using GET variables for the page to show.

 

I would suggest taking all the WHERE paramaters and serializing it into a single string and appending that to the query string. Then simply check if there is POST data, if so then recreate the WHERE caluse and show page 1. If there is no POST data, but there is GET data then get the page and serialized WHERE clause and show the appropriate page.

 

Also, this will trim down (and more importantly clean) the user input for the WHERE clause:

 

<?php
$queryAry = array();

if ($_POST[Amateur]<>"")
{
  $queryAry[] = "jobsegment = '".mysql_real_escape_string($_POST[Amateur])."'";
}

if ($_POST[Corporate]<>"")
  {
    $queryAry[] = "jobsegment = '".mysql_real_escape_string($_POST[Corporate])."'";
  }

if ($_POST[Facilities]<>"")
  {
    $queryAry[] = "jobsegment = '".mysql_real_escape_string($_POST[Facilities])."'";
  }

if ($_POST[Health]<>"")
  {
    $queryAry[] ="jobsegment = '".mysql_real_escape_string($_POST[Health])."'";
  }

if ($_POST[Professional]<>"")
  {
    $queryAry[] = "jobsegment = '".mysql_real_escape_string($_POST[Professional])."'";
  }

if ($_POST[sporting]<>"")
  {
    $queryAry[] = "jobsegment = '".mysql_real_escape_string($_POST[sporting])."'";
  }

if ($_POST[Location]="Region")
{
  if ($_POST[West]<>"")
  {
    $queryAry[] = "jobregion = '".mysql_real_escape_string($_POST[West])."'";
  }
    
  if ($_POST[Central]<>"")
  {
    $queryAry[] = ."jobregion = '".mysql_real_escape_string($_POST[Central])."'";
  }
    
  if ($_POST[Northeast]<>"")
  {
    $queryAry[] = "jobregion = '".mysql_real_escape_string($_POST[Northeast])."'";
  }
    
  if ($_POST[southeast]<>"")
  {
    $queryAry[] = "jobregion = '".mysql_real_escape_string($_POST[southeast])."'";
  }
    
  if ($_POST[Canada]<>"")
  {
    $queryAry[] = "jobregion = '".mysql_real_escape_string($_POST[Canada])."'";
  }
}

if ($_POST[Location]="State")
{
  if ($_POST[state]<>"")
  {
    $queryAry[] = "jobstateabbrv = '".mysql_real_escape_string($_POST[state])."'";
  }
}

$query = implode(' OR ', $queryAry);?>

Link to comment
Share on other sites

Thanks so much for the fast reply.  Its amazing how you can rework in 5 minutes what took me 5 hours to create.

 

Can you explain to me what exactly you did to trim down the user input?

 

I'll try your other suggestions

 

Here's the code properly displayed...and seriously, thank you for the help.

 

<?php 


$query="";

if ($_POST[searchKeywords]<>"")
  $query="jobtitle LIKE '$_POST[searchKeywords]' OR employer LIKE '$_POST[searchKeywords]' OR excerpt LIKE '$_POST[searchKeywords]' OR description LIKE '$_POST[searchKeywords]'"; 

if ($_POST[Amateur]<>"")
  {
  if ($query <> "")
     $query=$query." OR ";
  $query=$query."jobsegment = '$_POST[Amateur]'"; 
  }

if ($_POST[Corporate]<>"")
  {
  if ($query <> "")
    $query=$query." OR ";
  $query= $query."jobsegment = '$_POST[Corporate]'";
  }

if ($_POST[Facilities]<>"")
  {
  if ($query <> "")
    $query=$query." OR ";
  $query= $query."jobsegment = '$_POST[Facilities]'";
  }

if ($_POST[Health]<>"")
  {
  if ($query <> "")
    $query=$query." OR ";
  $query= $query."jobsegment = '$_POST[Health]'";
  }

if ($_POST[Professional]<>"")
  {
  if ($query <> "")
    $query=$query." OR ";
  $query= $query."jobsegment = '$_POST[Professional]'";
  }

if ($_POST[sporting]<>"")
  {
  if ($query <> "")
    $query=$query." OR ";
  $query= $query."jobsegment = '$_POST[sporting]'";
  }

if ($_POST[Location]="Region")
  {
  if ($_POST[West]<>"")
    {
    if ($query <> "")
      $query=$query." OR ";
    $query= $query."jobregion = '$_POST[West]'";
    }
    
  if ($_POST[Central]<>"")
    {
    if ($query <> "")
      $query=$query." OR ";
    $query= $query."jobregion = '$_POST[Central]'";
    }
    
  if ($_POST[Northeast]<>"")
    {
    if ($query <> "")
      $query=$query." OR ";
    $query= $query."jobregion = '$_POST[Northeast]'";
    }
    
  if ($_POST[southeast]<>"")
    {
    if ($query <> "")
      $query=$query." OR ";
    $query= $query."jobregion = '$_POST[southeast]'";
    }
    
  if ($_POST[Canada]<>"")
    {
    if ($query <> "")
      $query=$query." OR ";
    $query= $query."jobregion = '$_POST[Canada]'";
    }
  }
if ($_POST[Location]="State")
  {
  if ($_POST[state]<>"")
    {
    if ($query <> "")
      $query=$query." OR ";
  $query= $query."jobstateabbrv = '$_POST[state]'";
    }
  }


$t = mysql_query("SELECT * FROM `jos_jobfeeds` WHERE $query;"); 
  if(!$t) die(mysql_error()); 
    
$a                = mysql_fetch_object($t); 
$total_items      = mysql_num_rows($t); 
$limit            = $_GET['limit']; 
$type             = $_GET['type']; 
$page             = $_GET['page']; 

//set default if: $limit is empty, non numerical, less than 10, greater than 50 
if((!$limit)  || (is_numeric($limit) == false) || ($limit < 10) || ($limit > 50)) { 
     $limit = 50; //default 
} 
//set default if: $page is empty, non numerical, less than zero, greater than total available 
if((!$page) || (is_numeric($page) == false) || ($page < 0) || ($page > $total_items)) { 
      $page = 1; //default 
} 

//calcuate total pages 
$total_pages     = ceil($total_items / $limit); 
$set_limit          = $page * $limit - ($limit); 

//query: **EDIT TO YOUR TABLE NAME, ECT. 

$q = mysql_query("SELECT * FROM `jos_jobfeeds` WHERE $query LIMIT $set_limit, $limit"); 
  if(!$q) die(mysql_error()); 
     $err = mysql_num_rows($q); 
       if($err == 0) die("No matches met your criteria."); 


echo("   
<a href=http://www.mysite.com/index.php?option=com_content&task=view&id=375&Itemid=271&cat=$cat&limit=10&page=1>10</a> | 
<a href=http://www.mysite.com/index.php?option=com_content&task=view&id=375&Itemid=271&cat=$cat&limit=25&page=1>25</a> | 
<a href=http://www.mysite.com/index.php?option=com_content&task=view&id=375&Itemid=271&cat=$cat&limit=50&page=1>50</a>"); 

//show data matching query: 


echo ("<table border='0' width='100%' bordercolor='#000000'>");
echo ("<tr>");
echo ("<td valign='top'>");
echo ("<table border='0' width='100%' cellspacing='0' cellpadding='4' style='border-left-width: 0px; border-right-width: 0px'>");
echo ("<tr>");
echo ("<td><h3>Results of Your Search</h3></td>");
echo ("</tr>");
echo ("<tr>");
echo ("<td style='border-style: solid; border-width: 1px' bordercolor='#CBDDF1' bgcolor='#CBDDF1'>");
echo ("<table border='0' width='100%'>");
echo ("<tr>");
echo ("<td width='10%'><font color='#000000'><b>DATE</b></font></td>");
echo ("<td width='35%'><font color='#000000'><b>JOB TITLE</b></font></td>");
echo ("<td width='35%'><font color='#000000'><b>EMPLOYER</b></font></td>");
echo ("<td width='15%'><font color='#000000'><b>LOCATION</b></font></td>");
echo ("</tr>");
echo ("</table>");
echo ("</td>");
echo ("</tr>");
while($code = mysql_fetch_object($q)) { 
echo ("<tr>");
echo ("<td style='border-style: solid; border-width: 1px' bordercolor='#CBDDF1'>");
echo ("<table border='0' width='100%' height='31' cellpadding='0'>");
echo ("<tr>");
echo ("<td bgcolor='#EDF3FA' height='20' style='border-bottom-style: solid; border-bottom-width: 1px'>");
echo ("<table border='0' cellspacing='0' cellpadding='2' width='100%' height='35'>");
echo ("<tr>");
echo ("<td width='10%' height='20'>$code->lastupdatedate</td>");
echo ("<td width='35%' height='20' valign='top'><a href='http://www.mysite.com/index.php?option=com_content&task=view&id=372&Itemid=269&jobid=$code->jobid'><b>$code->jobtitle</b></a></td>");
echo ("<td width='25%' height='20' valign='top'>$code->employer</td>");
echo ("<td width='25%' height='20' valign='top'>$code->jobcity, $code->jobstate</td>");
echo ("</tr>");
echo ("<tr>");
echo ("<td width='10%'> </td>");
echo ("<td width='85%' colspan='3' valign='top'>$code->excerpt</td>");
echo ("</tr>");
echo ("</table>");
echo ("</td>");
echo ("</tr>");
echo ("</table>");
echo ("</td>");
echo ("</tr>");
}
echo ("</table>");
echo ("</td>");
echo ("</tr>");
echo ("</table>");


$cat = urlencode($cat); //makes browser friendly 

//prev. page: **EDIT LINK PATH** 

$prev_page = $page - 1; 

if($prev_page >= 1) { 
  echo("<b><<</b> <a href=http://www.mysite.com/index.php?option=com_content&task=view&id=375&Itemid=271&cat=$cat&limit=$limit&page=$prev_page><b>Prev.</b></a>"); 
} 

//Display middle pages: **EDIT LINK PATH** 

for($a = 1; $a <= $total_pages; $a++) 
{ 
   if($a == $page) { 
      echo("<b> $a</b> | "); //no link 
     } else { 
  echo("  <a href=http://www.mysite.com/index.php?option=com_content&task=view&id=375&Itemid=271&cat=$cat&limit=$limit&page=$a> $a </a> | "); 
     } 
} 

//next page: **EDIT THIS LINK PATH** 

$next_page = $page + 1; 
if($next_page <= $total_pages) { 
   echo("<a href=http://www.mysite.com/index.php?option=com_content&task=view&id=375&Itemid=271&cat=$cat&limit=$limit&page=$next_page><b>Next</b></a> > >"); 
} 



//all done 
?> 

Link to comment
Share on other sites

Well, for each user input you had an if statement to determine if you needed to append an OR first. So, you had those same two lines repeated over and over. Instead of doing that I first created an array "$queryAry". Then for each user input I kept the logic to determin whether or not the WHERE clause was need - if so, I added that clause to the array.

 

At the end of the process you are left with an array of multiple clauses. Then the last step is to simply concatenate all of the clauses separated by OR's using the implode() command. So in essence it is replacing the two lines of code that is duplicated for every field with a single line.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.