how do I search a whole database?

Question

So I wrote up this PHP code to search a database for either male or females. Here it is:

mysql_select_db("randomData",$connect);
$query = "SELECT * FROM randomTable WHERE Gender='" . $gender ."'";
$results = mysql_query($query);
while($row = mysql_fetch_array($results)){
echo $row['Name'] . " " . $row['Surname'] . "
" . $row['Email'] . "
" . "
";
}

This code works perfectly well for what I intended. But it only allows you to search by male or female because I have it searching the column that indicates gender. If I search "female" it will yield the names Jennifer and Julie along with their emails and last names. But if I try searching the actual name "jennifer" nothing comes up. How can I search the entire database for any key work and it will return anything associated with it? Also how do you distinguish how easily it will make a match? for example if I put in "jennif" would that be enough to yield Jennifer's info?

Answer 1

$query = "SELECT * FROM randomTable WHERE Name='" . $name ."'";



or try this for part of a name (you might need to play around with this syntax i haven't tested it myself)



$query = "SELECT * FROM randomTable WHERE Name LIKE '%" . $name ."%'";

Answer 2

What you actually have is a string command

$query = "SELECT * FROM randomTable WHERE Gender='" . $gender ."'";

The variable $query is just a string.

As you say you are currently searching the column Gender for the variable $gender

So of course you cannot have the variable $gender contain the name "Jennifer" because the column Gender does not contain a name.



But as I have already said $query is just a string.

So you can do this

$query = "SELECT * FROM randomTable WHERE " . $column . "='" . $findvalue ."'";



But you have to make sure that the variable $column contains the correct column name.



Have fun.

Answer 3

There are a few things to take into account:



1) make sure your strings (e.g., $name) don't contain excess characters -- in particular, extra white space can potentially ruin your search results. Remove all redundant white space from your search parameter. White space are spaces, tab characters, new lines, line feeds, etc. Redundant white space are white space at the beginning and end of your string, and consecutive white space in the middle of your string. You can use this function:



function removeRedundantWhitespace($string)

{

$string = trim(str_replace(chunk_split(" \t\r\n", 1), ' ', $string));



do {

$string = str_replace(' ', ' ', $string, $count);

} while ($count);



return $string;

}



2) the percent sign (%) is used as a wild card character. Replace any spaces in your search string for a percent sign plus a space.

That way, looking for 'John Kennedy'

(which becomes 'John% Kennedy' as a search term)

will also find 'John F. Kennedy'.



3) and finally: standardize case while matching. That is, make both the field you're looking in (in your database) AND the term you're looking for, the same case. Like this:



$name = 'John F. Kennedy';

$query = "SELECT * FROM names WHERE

LOWER(name) = LOWER('" . $name ."')";



This way 'John' will match 'john'.



BTW: note that it's best to do both case changes inside your query. If your search term is stored in a multi byte string, a difference in implementation in strtolower (in PHP) and LOWER (in MySQL) can create some hard to spot bugs if you write your code like this:



$name = strtolower('John F. Kennedy');

$query = "SELECT * FROM names WHERE LOWER(name) = '" . $name ."'";



So don't do it like that.



You can use both LOWER or UPPER; if most of the values in a column are upper case, then UPPER will result in a faster query, because the database will have fewer characters to change... This is only noticeable in large datasets, though!

Answer 4

WHERE names LIKE 'jennif%'; / or WHERE names = 'jennif%'; {if i remember correctly}