PHP/mpSQL Website Search

General PHP Help
#1

On my website (in progress), I have a search script, which allows you to search like on http://www.deviantart.com (lots of different options can be searched at once). However, I have just thrown together 115 lines of rubbish for my search script, and whilst it works in most areas, I know that there must be a easier way to process a form. I am quite good at PHP (this search script is about the last thing I have to do in a Content Management System I’ve made) and am simply looking for a PHP script that gets results from a mySQL database depending on input from more than one form field.

For example, you can enter a keyword (where like), choose how many results you want on a page (limit), how to display them (order by), what category to view results from (where), what author to view from (where). I want the visitor to be able to use as many or as little of these search areas as they want.

[code]<?php
$limit = $_POST [‘limit’];
$order = $_POST [‘order’];
$category = $_POST [‘category’];
$author = $_POST [‘author’];
$textsearch = $_POST [‘textsearch’];

if(isset($textsearch)){
$string= “title LIKE ‘%$textsearch%’”;}

if($category != All){
$string1=“category=’$category’”;}

if($author != All){
$string2=“author=’$author’”;}

if($order == A){
$string3=“ORDER BY title”;}

if($order == Z){
$string3=“ORDER BY title ASC”;}

if($order == Plays){
$string3=“ORDER BY hits DESC”;}

if($order == Rating){
$string3=“ORDER BY score DESC”;}

if(isset($string2) && ($string1) && ($string)){
$string="WHERE title LIKE ‘%$textsearch%’ ";
$string1="AND category=’$category’ ";
$string2=“AND author=’$author’”;}

if(isset($string) && ($string1) && (!$string2)){
$string="WHERE title LIKE ‘%$textsearch%’ ";
$string1=“AND category=’$category’”;}

if(isset($string) && ($string2) && (!$string1)){
$string="WHERE title LIKE ‘%$textsearch%’ ";
$string2=“AND author=’$author’”;}

if(isset($string2) && ($string1) && (!$string)){
$string1="WHERE category=’$category’ ";
$string2=“AND author=’$author’”;}

if(isset($string) && (!$string1) && (!$string2)){
$string=“WHERE title LIKE ‘%$textsearch%’”;}

if(isset($string1) && (!$string) && (!$string2)){
$string1=“WHERE category=’$category’”;}

if(isset($string2) && (!$string1) && (!$string)){
$string1=“WHERE author=’$author’”;}

if($category == All){
$string1="";}

if($author == All){
$string2="";}

$query_count    = "SELECT count(*) FROM games $string $string1 $string2 $string3";  
$result_count   = mysql_query($query_count);     
$totalrows      = mysql_num_rows($result_count); 

if(empty($page)){ 
    $page = 1; 
} 
     
$limitvalue = $page * $limit - ($limit); 
$query  = "SELECT * FROM games $string $string1 $string2 $string3 LIMIT $limitvalue, $limit";         
$result = mysql_query($query) or die("Error: " . mysql_error()); 

if(mysql_num_rows($result) == 0){ 
    echo("<p>No games found!</p>"); 
} 

while($r = mysql_fetch_array($result)){

include(“function.php”);
echo("
");
}

if($page != 1){ 
    $pageprev = $page--; 
     
    echo("<a href="?Page==Search&page=$pageprev">&lt;&lt;Previous</a>&nbsp;"); 
}else{ 
    echo("&lt;&lt;Previous &nbsp;"); 
} 

$numofpages = $totalrows / $limit; 
 
for($i = 1; $i <= $numofpages; $i++){ 
    if($i == $page){ 
        echo($i."&nbsp;"); 
    }else{ 
        echo("<a href="?Page==Search&page=$i">$i</a>&nbsp;"); 
    } 
} 


if(($totalrows % $limit) != 0){ 
    if($i == $page){ 
        echo($i."&nbsp;"); 
    }else{ 
        echo("<a href="?Page==Search&page=$i">$i</a>&nbsp;"); 
    } 
} 

if(($totalrows - ($limit * $page)) > 0){ 
    $pagenext = $page++; 
      
    echo("  <a href="?Page=Search&page=$pagenext">Next&gt;&gt;</a>"); 
}else{ 
    echo(" Next&gt;&gt;"); 
} 
 
mysql_free_result($result);

?>[/code]That is what I have, but there has to be a much simpler way that going through each possibility individually?

Of course, there is a working database connection connected to this file etc. It isn’t a database problem.

#2

have you tried handling the $_POST variable like just another array and doing a switch? I honestly have no idea if this would work but I don’t see why not with a little tweeking. shrug - as I’m doing it -it seems to generate more code then your version… but hypothetically it should be faster and allows you to handle the logic in related blocks. sigh really up to you.

[php]
for($i=0;$i <count($_POST); $i++)
{
if (isset($POST[$i]))
{
switch ($_POST)
{
case"order":
switch ($_POST[‘order’])
{
case “A”:
break;
case “Z”:
break;
case “plays”:
break;
case “rating”:
break;
default:
break;
}
break;
case “author”:
…
}
}
}
[/php]

#3
<?php $string = ''; $category_search = FALSE; if(isset($_POST['textsearch'])) $string .= ' title LIKE "%'.$_POST['textsearch'].'%"'; if(isset($_POST['category']) && $_POST['category'] != 'All') { $string .= ' WHERE category = "'.$_POST['category'].'"'; $category_search = TRUE; } if(isset($_POST['author']) && $_POST['author'] != 'All') { $string .= ($category_search) ? ' AND' : ' WHERE'; $string .= ' author = "'.$_POST['author'].'"'; } if(isset($_POST['order'])) { switch($_POST['order']) { case 'A': $string .= ' ORDER BY title'; break; case 'Z': $string .= ' ORDER BY title DESC'; break; case 'Plays': $string .= ' ORDER BY hits DESC'; break; case 'Rating': $string .= ' ORDER BY score DESC'; break; } } $page = (isset($_GET['page']) && intval($_GET['page'])) ? $_GET['page'] : 1; $limitvalue = $page * $limit - $limit; $result = mysql_query('SELECT * FROM games'.$string.' LIMIT '.$limitvalue.', '.$limit) or die('Error: '.mysql_error()); if(!mysql_num_rows($result)) echo '

No games found!

'; else { while($r = mysql_fetch_array($result)) { include('function.php'); // why to hell are you including function.php for every row returned? echo '
'; } echo ($page > 1) ? '<<Previous  ' : '<<Previous   '; $result_count = mysql_query('SELECT COUNT(*) FROM games'.$string) or die('Query problem: '.mysql_error()); $totalrows = mysql_num_rows($result_count); $numofpages = ceil($totalrows / $limit); for($i = 1; $i < $numofpages + 1; $i++) { echo ($page == $i) ? $i.'  ' : ''.$i.'  '; } echo ($totalrows - $limit * $page > 0) ? 'Next>> ' : 'Next>> '; } ?>

I got this back from another forum, but I get an SQL error. I really am at a wit’s end with this!

Error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIKE “%searchword%” WHERE category = “Adventure” AND author = "

To me, that query looks fine with what is given.

#4

Hi KingSwing,

Well, if it says there is an error, I`m afraid there is an error. :)

Where is your code for the actual search query so we can see this line?

'LIKE "%searchword%" WHERE category = "Adventure" AND author = "

Maybe its because you are using apostrophes when you should be using quotes? And what is %searchword%? Should that be a variable?

#5

$result = mysql_query(‘SELECT * FROM games’.$string.’ LIMIT ‘.$limitvalue.’, '.$limit) or die('Error: '.mysql_error());

There is the actual query. Searchword is just the word you type into the seach box, type football into the search box, and football would appear there.

#6

Ah, now I see. Sorry.

OK, well using your table names and stuff, this is how I would build search data. Each word seperated by a space is treated as a new keyword. So for example, if BOO and FOO were entered in the search box, the search would be for both BOO and FOO.

This is also based on your search action being something like:

search.php?action=search

Maybe you can work around this, to get what you want:

  $search=false;

  if ((isset($_GET['action'])) & ($_GET['action']=="search"))
  {
  	$order       = $_POST['order'];
        $textsearch   = trim($_POST['textsearch']);
  
        if ($textsearch)
        {
        	//----------------------------------------------------------------
                //Seperate keywords and build search data
                //Assign variable for filter
                //----------------------------------------------------------------

                $k               = split(" ", $textsearch);
                $stringcount     = 0;

                switch ($order)
                {
                	case "A":
        	        $orderby = "ORDER BY title";
        	        break;

        	        case "Z":
        	        $orderby = "ORDER BY title DESC";
        	        break;

        	        case "Plays":
        	        $orderby = "ORDER BY hits DESC";
        	        break;
        	        
        	        case "Rating":
        	        $orderby = "ORDER BY score DESC";
        	        break;
                }

                foreach ($k as $d)
                {
                	$search_words[]   = $d;
                        $stringcount      = $stringcount+1;
                }
                for ($i=0; $i<$stringcount; $i++)
                {
                	if ($i)
                        {
                        	$search_string .= "OR category LIKE '%" . $search_words[$i] . "%' OR author LIKE '%" . $search_words[$i] . "%' ";
                        }
                        else
                        {
                        	$search_string .= "category LIKE '%" . $search_words[$i] . "%' OR author LIKE '%" . $search_words[$i] . "%'";
                        }
                }

                $build_search_data = "WHERE (" . $search_string . ")" ;

                $query = "SELECT * FROM title $build_search_data $orderby";
                $result = mysql_query($query) or die(mysql_error());

                if (mysql_num_rows($result)>0)
                {
                	$search = true;
                }
                else
                {
                	$no_results = true;
                }
        }
        else
        {
        	echo "No Keywords entered!";
        }	
  }

  if ($search)
  
  {
  
  //show search results
  
  }
  
  if ($no_results)
  {
  
  //no results found
  
  }
#7

Thanks for all of your help, it works enough now for me to be able to finish it off. Thanks!

#8

You`re Welcome. :)

Sponsor our Newsletter | Privacy Policy | Terms of Service