Jump to content

MYSQL search results and pagination problem

nuil

By nuil
in PHP Coding Help

 Share

Recommended Posts

nuil Newbie

nuil

Posted
Posted (edited)

Hi

 

I learnt to stick stuff in mysql(connect db etc), then get results from mysql, then display it. And i had trouble getting things to work with pagination so i downloaded this source code. Combined it to mine and got most things working, problem things below

 

1. Search results return 10 items even if only one item occurs in the database for the serach term. Caused by code below by me not using the $query = "SELECT(this did work fine for returning results before i added pagenation, now its not used but left there for help what to do) and using the second one called $query1 = "SELECT. Only way i could get pagination to work. Pagnation works good the way it is but its causing the problem to return 10 results listed even if only one is relevant

Example search, word Articuno only exists(exists one time in the db) with the top result from below link, but 9 other enteries there

http://tigerpaws.sit...et/?q= Articuno

 

Pagination works fine

http://tigerpaws.sit...

 

2. If i type 2 keywords only to search from, the second term is the only relevant term appearing in the results, first term is ignored. Is it also posible for a tip in what would be needed to be done in php code(not the html form code thats no worries to sort out) to be able to do a exact phrase match, how to go about doing it

 

Tried hard to sanitize it, tested with <script>alert(document.cookie);</script> and RIPS, it complains about the same line of code that sort of sanitized(it needs to still be able to return the results of é that is %E9)

$search = mysql_real_escape_string(urldecode(filter_var($_GET['q'], FILTER_SANITIZE_STRING , FILTER_SANITIZE_ENCODED)));

 

If there is any thing unsecure please let me know or needs fixing, im still learning

 

Its chopped down a bit but its so spread out, i use a config file but added a bit of it to make it easier to run if anyone was looking at running it

<?php
/**************************************************************************************
* Main Search Page - index.php
* Author: Tiger
* This file searches the database
**************************************************************************************/
//Get variables from config.php to connect to mysql server
$dbusername="root";
$dbuserpass="test";
$dbhost="localhost";
$dbname7="PokemonBW";
$table7="cardsbw";
//================================================//
//'''''''''''Page Display Configuration'''''''''''//
//================================================//
$limit = 10;

//================================================//
//''''''''''Create Table Database config''''''''''//
//================================================//
$column1="cardName";
$column2="cardImage";

// connect to the mysql database server.
mysql_connect ($dbhost, $dbusername, $dbuserpass);
//select the database
mysql_select_db($dbname7) or die('Cannot select database');

//search variable = data in search box or url
if(isset($_GET['q'])) {
   $search = mysql_real_escape_string(urldecode(filter_var($_GET['q'], FILTER_SANITIZE_STRING , FILTER_SANITIZE_ENCODED)));
} else {
   $search = ""; // Undefined variable fix
}

//trim whitespace from variable
$search = trim($search);
$search = preg_replace('/\s+/', ' ', $search);

//seperate multiple keywords into array space delimited
$keywords = explode(" ", $search);

//Clean empty arrays so they don't get every row as result
$keywords = array_diff($keywords, array(""));

//Set the MySQL query
if ($search == NULL or $search == '%'){
} else {
   for ($i=0; $i<count($keywords); $i++) {
       $query = "SELECT * FROM ".$table7." " .
       "WHERE ".$column1." LIKE '%".$keywords[$i]."%'".
       " OR ".$column2." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column3." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column4." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column5." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column6." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column7." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column8." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column9." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column10." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column11." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column12." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column13." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column14." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column15." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column16." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column17." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column18." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column19." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column20." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column21." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column22." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column23." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column24." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column25." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column26." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column27." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column28." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column29." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column30." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column31." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column32." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column33." LIKE '%".$keywords[$i]."%'" .
       " ORDER BY ".$column1." ";

       $query1 = "SELECT COUNT(*) as num FROM ".$table7." " .
       "WHERE ".$column1." LIKE '%".$keywords[$i]."%'".
       " OR ".$column2." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column3." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column4." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column5." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column6." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column7." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column8." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column9." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column10." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column11." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column12." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column13." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column14." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column15." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column16." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column17." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column18." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column19." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column20." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column21." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column22." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column23." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column24." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column25." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column26." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column27." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column28." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column29." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column30." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column31." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column32." LIKE '%".$keywords[$i]."%'" .
       " OR ".$column33." LIKE '%".$keywords[$i]."%'" .
       " ORDER BY ".$column1." ";
       $total_pages = mysql_fetch_array(mysql_query($query1));
       $total_pages = urldecode(filter_var($total_pages['num'], FILTER_SANITIZE_ENCODED, FILTER_SANITIZE_NUMBER_FLOAT));

       $stages = 3;
       if (!isset($_GET['page'])) {
           $_GET['page'] = 0;
       } else {
           $page = urldecode(filter_var($_GET['page'], FILTER_SANITIZE_STRING, FILTER_SANITIZE_ENCODED));
       }

       $page = mysql_real_escape_string(urldecode(filter_var($_GET['page'], FILTER_SANITIZE_NUMBER_FLOAT)));
       if($page) {
           $start = ($page - 1) * $limit;
       } else {
           $start = 0;
       }

       // Get page data
       $query2 = "SELECT * FROM ".$table7." LIMIT ".$start.", ".$limit." ";
       $result2 = mysql_query($query2) or die(mysql_error());

   }    
   //Store the results in a variable or die if query fails
   //$result = mysql_query($query) or die(mysql_error());
}

echo '
<body onload="self.focus();document.searchform.search.focus()">
<center>
<br>
<form name="searchform" method="GET" action="">
<input type="text" name="q" size="20" TABINDEX="1" placeholder="';  
   foreach($keywords as $value) { print "$value"; }
   echo '" onfocus="if(this.value==\'Initial text\')this.value=\'" onblur="if(this.value==\')this.value=\'Initial text\'">
<input type="submit" value="Search">
</form>
';

//If users doesn't enter anything into search box tell them to.
if ($search == NULL){
   echo '<table id="search" bgcolor="#00BB77">
<tr>
<td>
<b> Please enter a search parameter to continue </b>
</td>
<tr>
</table>
</center><br>';
} elseif ($search == '%'){
   echo '<table id="search" bgcolor="#00BB77">
<tr>
<td>
<b> Please enter a search parameter to continue </b>
</td>
<tr>
</table>
</center><br>';
   //If no results are returned print it
} elseif ($total_pages <= 0){
   //ResultTable();
   echo '<table id="search" bgcolor="#00BB77">
<tr>
<td>
Your search for <b><FONT COLOR="0022FF">';
   foreach($keywords as $value) { print "$value "; }
   echo '</b></font> found <b>' . $total_pages . ' cards</b>
</td>
<tr>
</table>
</center><br>';

//ELSE print the data in a table
} else {
   echo '<table id="search" bgcolor="#00BB77" border="1">
<tr>
<td>
Your search for <b><FONT COLOR="0022FF">';
   foreach($keywords as $value) { print "$value "; }
   echo '</b></font> found <b>' . $total_pages . ' cards</b>
</td>
<tr>
</table>
</center><br>';

// Initial page num setup
if ($page == 0){$page = 1;}
$prev = $page - 1;
$next = $page + 1;
$lastpage = ceil($total_pages/$limit);
$LastPagem1 = $lastpage - 1;

$paginate = '';
if($lastpage > 1) {
   $paginate .= '<div class="paginate">';
   // Previous
   if ($page > 1) {
       $paginate.= '<a href="?q='.$search.'&page='.$prev.'">previous</a>';
   } else {
       $paginate.= '<span class="disabled">previous</span>';
   }

   // Pages    
   if ($lastpage < 7 + ($stages * 2)) {    // Not enough pages to breaking it up
       for ($counter = 1; $counter <= $lastpage; $counter++) {
           if ($counter == $page){
               $paginate.= '<span class="current">'.$counter.'</span>';
           } else {
               $paginate.= '<a href="?q='.$search.'&page='.$counter.'">'.$counter.'</a>';
           }                    
       }
   }
   elseif($lastpage > 5 + ($stages * 2)) {        // Enough pages to hide a few?
       // Beginning only hide later pages
       if($page < 1 + ($stages * 2)) {
           for ($counter = 1; $counter < 4 + ($stages * 2); $counter++) {
               if ($counter == $page){
                   $paginate.= '<span class="current">$counter</span>';
               } else {
               $paginate.= '<a href="?q='.$search.'&page='.$counter.'">'.$counter.'</a>';}                    
           }
           $paginate.= '...';
           $paginate.= '<a href="?q='.$search.'&page='.$LastPagem1.'">'.$LastPagem1.'</a>';
           $paginate.= '<a href="?q='.$search.'&page='.$lastpage.'">'.$lastpage.'</a>';        

           // Middle hide some front and some back
       } elseif($lastpage - ($stages * 2) > $page && $page > ($stages * 2)) {
           $paginate.= '<a href="?q='.$search.'&page=1">1</a>';
           $paginate.= '<a href="?q='.$search.'&page=2">2</a>';
           $paginate.= '...';
           for ($counter = $page - $stages; $counter <= $page + $stages; $counter++) {
               if ($counter == $page){
                   $paginate.= '<span class="current">'.$counter.'</span>';
               } else {
               $paginate.= '<a href="?q='.$search.'&page='.$counter.'">'.$counter.'</a>';}                    
           }
           $paginate.= '...';
           $paginate.= '<a href="?q='.$search.'&page='.$LastPagem1.'">'.$LastPagem1.'</a>';
           $paginate.= '<a href="?q='.$search.'&page='.$lastpage.'">'.$lastpage.'</a>';        

           // End only hide early pages
       } else {
           $paginate.= '<a href="?q='.$search.'&page=1">1</a>';
           $paginate.= '<a href="?q='.$search.'&page=2">2</a>';
           $paginate.= "...";
           for ($counter = $lastpage - (2 + ($stages * 2)); $counter <= $lastpage; $counter++) {
               if ($counter == $page){
                   $paginate.= '<span class="current">'.$counter.'</span>';
               } else {
                   $paginate.= '<a href="?q='.$search.'&page='.$counter.'">'.$counter.'</a>';
               }                    
           }
       }
   }

   // Next
   if ($page < $counter - 1){
       $paginate.= '<a href="?q='.$search.'&page='.$next.'">next</a>';
   } else {
       $paginate.= '<span class="disabled">next</span>';
   }

   $paginate.= '</div>';
}

echo '<center><table>
<tr>
<td>';
// pagination
echo '<div align="right">'.$paginate.'</div>';

//While there are rows, print it.
while($row = mysql_fetch_array($result2)) {
echo '

<!-- BACKGROUND-COLOR VARIES BY POKEMON TYPE -->
<div class="cdbResult">
<img style="width: 102px; height: 142px;" class="cardImage" src="'.$row[$column2].'" alt="'.$row[$column1].'" height="142" width="102">

<div class="clear"></div>
<div class="column-1">
<h3 class="dynamic-din">'.$row[$column1].'</h3>

<p class="hp">HP <span>'.$row[$column3].'</span>
<img src="images/bw/'.$row[$column4].'.png" alt="'.$row[$column4].'" height="25" width="25">
</p>

';

if ($row[$column6]) { echo '<img src="images/bw/'.$row[$column6].'.png" alt="'.$row[$column6].'" height="25" width="25">
';}
if ($row[$column7]) { echo '<img src="images/bw/'.$row[$column7].'.png" alt="'.$row[$column7].'" height="25" width="25">
';}
if ($row[$column8]) { echo '<img src="images/bw/'.$row[$column8].'.png" alt="'.$row[$column8].'" height="25" width="25">
';}
if ($row[$column9]) { echo '<img src="images/bw/'.$row[$column9].'.png" alt="'.$row[$column9].'" height="25" width="25">
';}
if ($row[$column10]) { echo '<img src="images/bw/'.$row[$column10].'.png" alt="'.$row[$column10].'" height="25" width="25">
';}

if ($row[$column24]) { echo '
<p class="lozenge lozenge-ability"><span class="offscreen">Ability</span></p>
<p class="ability">'.$row[$column24].'</p>
<div class="clear"></div>
';}

if ($row[$column27]) { echo '<img src="images/bw/'.$row[$column27].'.png" alt="'.$row[$column27].'" height="25" width="25"> ×'.$row[$column26];}

   }
}


if(isset($paginate)) {
   echo '<div align="right">'.$paginate.'</div>';
} else {
   $paginate = ""; // Undefined variable fix
}

//echo '<div align="right">'.$paginate.'</div>';
echo '</td>
<tr>
</table>
</center>

</body>
</html>';
if ($search == NULL or $search == '%') {
} else {
   //clear memory
   mysql_free_result($result2);
}

?>

Edited by nuil
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.

×
 Share
Go to topic listing
×
×
  • 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.