[Slice]

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

[VolcomMky]

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

[Slice]

VolcomMky, 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