Search database

[Mutley]

Is it possible to make a form, then when you type a keyword in like "eggs" it searches all the fields or certain fields for "eggs" then displays any row containing this keyword in a table?

Would it be as simple as a form field and a SELECT . FROM . using a variable?

[joquius]

I suppose you could just do SELECT FROM `table` WHERE `field1` LIKE '%eggs%' OR ...

[The Little Guy]

you would need to set the fields to Fulltext and then you could do somthing like this

Then maybe something like this would work:
[code]
<?php
$sql = mysql_query("SELECT field1,field2,
MATCH(field1,field2)AGAINST('%$var%' IN BOOLEAN MODE)
AS score
FROM table_name
WHERE
MATCH(field1,field2)AGAINST('%$var%' IN BOOLEAN MODE) ORDER BY score");
?>
[/code]

[boby]

Here is a more detailed example of what you are (hopefully) looking for:
[code=PHP]<?php
...

//Define your search keyword(s)
$search = 'eggs';

//Escape special chars for use in a SQL statement
$search_word = mysql_real_escape_string ($search);

//Build SQL query
$sql = "SELECT SQL_CALC_FOUND_ROWS * FROM `db_table` WHERE `field_1` LIKE '%{$search_word}%' OR `field_2` LIKE '%{$search_word}%' OR `field_3` LIKE '%{$search_word}%'";

//Run SQL query
$result = mysql_query ($sql);

//Check if query was successfull
if (!$result)
{
  echo "Could not successfully run query ({$sql}) from DB: " . mysql_error();
  exit;
}

//Get number of found rows
$count = mysql_fetch_assoc (mysql_query ('SELECT FOUND_ROWS() as `total`'));

//Check if we have results
if ($count['total'] > 0)
{
  echo '<table>';

  //Loop through each found row
  while ($row = mysql_fetch_assoc ($result))
  {
      echo '<tr>
              <td>'.$row['field_1'].'</td>
              <td>'.$row['field_2'].'</td>
              <td>'.$row['field_3'].'</td>
            </tr>';
  }

  echo "</table>";
}
else
{
  //No results found, display error msg
  echo 'No results found!';
}

...
?>[/code]

The use boolean search and fulltext fields like "[b]The Little Guy[/b]" suggested in the post above, you will need at least MySQL v4.0.1 to add fulltext indexes (since v3.23.23) and the boolean modifier available. I found out there are some providers that have it disabled for a reason or another. A boolean search will give you "better" results but usually less, I would use it over the regular search option.
http://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html

Boby

[Mutley]

How would I do it from a form field though? You've given me a variable like $search = 'eggs'; how do I get the form to use it?

[trq]

[code]
$search = $_POST['searchfield'];
[/code]

[Mutley]

Thanks thorpe, woah, boby, that works REALLY well, very impressed. Thanks a lot!

How would I take/SELECT other database fields though to show? Like if I wanted to search just the "product" field it comes out like:
| product name | product |