pazafuera Posted April 8, 2008 Share Posted April 8, 2008 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> > >"); } ?> Quote Link to comment Share on other sites More sharing options...
Psycho Posted April 8, 2008 Share Posted April 8, 2008 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);?> Quote Link to comment Share on other sites More sharing options...
pazafuera Posted April 8, 2008 Author Share Posted April 8, 2008 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 ?> Quote Link to comment Share on other sites More sharing options...
Psycho Posted April 8, 2008 Share Posted April 8, 2008 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. Quote Link to comment Share on other sites More sharing options...
pazafuera Posted April 8, 2008 Author Share Posted April 8, 2008 Thanks for the explaination...that makes perfect sense. I've tried to serialize the $query string, but no luck...I'm getting something funky. Here's my line of code: $querydata = serialize ($query); Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.