Database Search

[MstrBob]

Okay, in my never ending quest to learn through trial and error, I'm working on a script in which one of its functions is to save names and emails to my database. This I've done, and even managed to figure out how to display all of them together. However, I'm scratching my head with one detail, the search function. Simple script which allows you to search by either a name, or email. I'm basically using a while loop and if statements to compare the entry field with the database values. Well, first, here's with what I've dreamed up so far:


<?PHP
require("db.php");

$query="SELECT * FROM hn_emails";
$result=mysql_query($query);
$num=mysql_numrows($result);
$i=0;

while ($i < $num) {

$pre_name=mysql_result($result,$i,"name");
$pre_email=mysql_result($result,$i,"email");
list($pre_name1, $pre_name2, $pre_name3, $pre_name4) = split('[ ]', $pre_name);
list($pre_email1, $pre_email2, $pre_email3) = split('[, ]', $pre_email);

if($sfield == "name")
{
if($sname == $pre_name || $sname == $pre_name1 || $sname == $pre_name2 || $sname == $pre_name3 || $sname == $pre_name4)
{
include "header.txt";
echo("<P>" . $pre_name . " " . $pre_email . "</p>");
include "footer.txt"; exit;
}
}

if($sfield == "email")
{
if($semail == $pre_email || $semail == $pre_email1 || $semail == $pre_email2 || $semail == $pre_email3)
{
include "header.txt";
echo("<P>" . $pre_name . " " . $pre_email . "</p>");
include "footer.txt"; exit;
}
}


++$i;
}
mysql_close();

if ($i == $num && $sfield == "email" && ($semail != $pre_email || $semail != $pre_email1 || $semail != $pre_email2 || $semail != $pre_email3))
{
include "header.txt";
echo("<p>We're sorry, but there is no such email address in the database.</p>");
include "footer.txt";exit;
}
if ($i == $num && $sfield == "name" && ($sname != $pre_name || $sname != $pre_name1 || $sname != $pre_name2 || $sname != $pre_name3 || $sname != $pre_name4))
{
include "header.txt";
echo("<p>We're sorry, but there is no such person in the database.</p>");
include "footer.txt";exit;
}

?>


Now the script DOES work. My only issue with it is that its case sensitive, something I wish to eliminate. Also, boolean searches aren't possible (at least I don't think). I can't help but feel I may be going in the wrong direction with this, is there an easier method for breaking up my strings to make them searchable? Also, more importantly, how can I make it NON case sensitive?

[Jona]

Convert all the strings (search terms and data being searched) to lowercase, then search, then use the original value. The way you're doing it, that's the first way that comes to mind. BTW, why not use the LIKE clause of MySQL to search? It's a lot less code.

[MstrBob]

I dont seem to sound stupid, but the fact is, when it comes to PHP/MySQL, I pretty much am. I've been working with and manipulation the examples from the MySQL manual, but I cant get much anything from it? Could some blessed sould PLEASE, PLEASE, PLEASE help me with this? :confused: Its been driving me insane for hours... :(

[Jona]

$query = "SELECT * FROM `table` WHERE `field` LIKE '%". $searchterm ."%' OR `field2` LIKE '%". $searchterm ."%'";

[MstrBob]

Thank you oh so so so so sooooo much. :D Why couldn't the manual have put it that simply? Anywho, it works mighty fine now, thanks a bunch!

[Jona]

Happy to help. :)