2 Replies - 5513 Views - Last Post: 05 March 2011 - 07:59 AM Rate Topic: -----

#1 Slice  Icon User is offline

  • sudo pacman -S moneyz


Reputation: 244
  • View blog
  • Posts: 716
  • Joined: 24-November 08

Using PHP to search multiple MySql columns

Posted 04 March 2011 - 03:30 PM

Hey guys. I'm running a very basic MySql database with 6 fields and have a working form using PHP to search the database. Here's what I have:

form:
 <form name="search" method="post" action="<?=$PHP_SELF?>">
 Search for: <input type="text" name="find" /> in 
 <Select NAME="field">
 <Option VALUE="DvdTitle">Title</option>
 <Option VALUE="Star1">Actor</option>
 <Option VALUE="Genre">Genre</option>
 <Option VALUE="Year">Year</option>
 </Select>
 <input type="hidden" name="searching" value="yes" />
 <input type="submit" name="search" value="Search" />
 </form>

<div>



php:
<? 
//get information
$field = @$_POST['field'] ;
$find = @$_POST['find'] ;
$searching = @$_POST['searching'] ;

 //once submitted  
 if ($searching =="yes") 
 { 
 echo "<h2>Results</h2><p>"; 
 


 //blank search returns all results
 
 mysql_connect("********", "********", "*********") or die(mysql_error()); 
 mysql_select_db("filmcollection") or die(mysql_error()); 
 
 //filter search term 
 $find = strtoupper($find); 
 $find = strip_tags($find); 
 $find = trim ($find); 
 
 //search database 
 $data = mysql_query("SELECT * FROM dvds WHERE upper($field) LIKE'%$find%'"); 
 
 //display the results 
 while($result = mysql_fetch_array( $data )) 
 { 
 echo "<div class='result'><b>";
 echo $result['DvdTitle']; 
 echo "</b><br/>"; 
 echo $result['Star1']; 
 echo "<br/>"; 
 echo $result['Star2']; 
 echo "<br/>"; 
 echo $result['Star3']; 
 echo "<br/>"; 
 echo $result['Genre'];
 echo "<br/>";
 echo $result['Year']; 
 echo "<br/>"; 
 echo "</div>"; 
 } 
 
 //number of results or error 
 $anymatches=mysql_num_rows($data); 
 if ($anymatches == 0) 
 { 
 echo "Sorry, but we can not find an entry to match your query<br><br>"; 
 } 
 
 //the search term 
 echo "<br><b>Searched For:</b> " .$find; 
 } 
 ?> 




Now this is just a small project which I'm using to practice on while I learn.

So the problem is, I want to be able to search Star1, Star2 and Star3 with one search term. So that the user can search one actor and it searches all three columns.

I tried:
<Option VALUE="Star1,Star2,Star3">Actor</option>


but I just get a server error message when I try to load the page.

Sorry if this is a really trivial problem but like I said, I'm still learning. :)

EDIT: Sorry not a server error message, It just returns "Sorry, but we can not find an entry to match your query".

This post has been edited by Slice: 04 March 2011 - 03:35 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Using PHP to search multiple MySql columns

#2 VolcomMky  Icon User is offline

  • D.I.C Regular

Reputation: 74
  • View blog
  • Posts: 315
  • Joined: 13-May 09

Re: Using PHP to search multiple MySql columns

Posted 04 March 2011 - 03:47 PM

Check if there is a , in the value and if so do a explode and then loop through the values appending onto your current SQL Query with OR

$sql = 'SELECT * FROM dvds';
$hasWhere = false;
if(strpos($_POST['field'],','))
{
   $fields = explode(',',$_POST['field']);
   foreach($fields as $col)
   {
      if($hasWhere)
      {
         $sql .= ' OR UPPER(' . $col . ') LIKE "%'.$find.'%"';
      }
      else
      {
        $sql .= ' WHERE UPPER(' . $col . ') LIKE "%'.$find.'%"';
        $hasWhere = true;
      }
   }
}
else
{
   $sql .= ' WHERE UPPER(' . $_POST['field'] . ') LIKE "%'.$find.'%"';
}

This post has been edited by VolcomMky: 04 March 2011 - 03:50 PM

Was This Post Helpful? 0
  • +
  • -

#3 Slice  Icon User is offline

  • sudo pacman -S moneyz


Reputation: 244
  • View blog
  • Posts: 716
  • Joined: 24-November 08

Re: Using PHP to search multiple MySql columns

Posted 05 March 2011 - 07:59 AM

View PostVolcomMky, on 04 March 2011 - 03:47 PM, said:

Check if there is a , in the value and if so do a explode and then loop through the values appending onto your current SQL Query with OR

$sql = 'SELECT * FROM dvds';
$hasWhere = false;
if(strpos($_POST['field'],','))
{
   $fields = explode(',',$_POST['field']);
   foreach($fields as $col)
   {
      if($hasWhere)
      {
         $sql .= ' OR UPPER(' . $col . ') LIKE "%'.$find.'%"';
      }
      else
      {
        $sql .= ' WHERE UPPER(' . $col . ') LIKE "%'.$find.'%"';
        $hasWhere = true;
      }
   }
}
else
{
   $sql .= ' WHERE UPPER(' . $_POST['field'] . ') LIKE "%'.$find.'%"';
}


Thanks for the reply. I've tried this but I don't really understand how it works so I think I'm doing something wrong. Should this be instead of my
$data = mysql_query("SELECT * FROM dvds WHERE upper($field) LIKE'%$find%'");




and changing the fetch to:
$result = mysql_fetch_array( $sql ))


Sorry but I'm a complete beginner with PHP.

This post has been edited by Slice: 05 March 2011 - 08:01 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1