Searching a MySQL DB

KITTfan2K

I have a MySQL databse, and I want to set up a system where a user can type in the first few letters of an entry, and the database will, when submit is clicked, show all the entries that start with those letters.

i.e. user types in Des
Database returns Desert, Dessert, Destiny, etc.

I would use code similar to the following:

$SearchVariable = "SELECT * FROM 'table' WHERE 'Name' STARTS WITH $SearchText"

mysql_query ($SearchVariable)

Would that work?
If this works, is there a faster way to do it?

KITTfan2K

gtibok

$search = "SELECT ... WHERE name LIKE '$string%' ...";

% is wildcard

KITTfan2K

Originally posted by gtibok
$search = "SELECT ... WHERE name LIKE '$string%' ...";
% is wildcard [/B]

So, on a PHP/MySQL system, the % symbol does the same as the * does in Windows?

And, in your code, it says "...name LIKE '$str..."

Is it better to use LIKE than =?

KITTfan2K

stolzyboy

you have to use LIKE when using the % wildcard

mogster

Here's a snippet from my very quick and simple search:

$find -> query("select distinct article_id, category_id from article where (title like '%$searchString%' or ingress like '%$searchString%') and published = 'y' and (active_until > now() or active_until = '00000000') order by change_time DESC");
while ($find -> next_record()) {
$category_id = $find -> f("category_id");
$hitArray[] = (int)$find -> f("article_id");
$hitCount++;
}

if ($hitCount > 0) {
$hitArray = array_unique($hitArray);
for ($i = 0; $i < sizeof($hitArray); $i++) {
if ($hitArray[$i] > 0) {
$num++;
}
}
echo "Search for '".$searchString."' returned ".$num." hits:<br><br>

Then do a second request based on the $hitArray[$i] where you specify just what to pick out and display - I'm using title, ingres and picture_id in this case to create a list of articles as the hitlist.

Hope it helps! 😃

knutm