Search for a record in database using LIKE

[slimline77]

Hi all,
This is my first post in the forum and I'm really hoping someone can help me. I've been trying to do this all day and I'm getting nowhere.
Basically I want an admin to be able to search for a single user in site_users sql table using a search box. I want the admin to be able to search users by name, username, email or whatever and have the results returned in a form that can be edited.
I know I need to use an sql LIKE statement but every code I've tried just dosen't seem to work. I'm new at php and I've been able to work my way through but this simple database search is defeating me. If anyone can give me simple php code that I can build on I would be eternally grateful. So if someone wants to do their good deed for the day, helping me would definately be it.
Thanks in advance for any help.
PS I know I should give some code as it might makes things clearer but unfortunately this code I've used returns every user in the table instead of one.

<form method=post action="search2.php"> 

Search For: 
<p> 
User Name: <input type=text name=user size=25 maxlength=25> 
<p> 
Name: <input type=text name=name size=25 maxlength=25> 
<p> 
Email: <input type=text name=email size=25 maxlength=25> 
<p> 
<p> 
County: <input type=text name=county size=25 maxlength=25> 
<p> 
<input type=submit> 
</form> 


<?php
$db = mysql_connect('localhost', 'root', '') or
    die ('Unable to connect. Check your connection parameters.');

mysql_select_db('dvdff2', $db) or die(mysql_error($db));


if ($username == "") 
{$username = '%';} 

if ($name == "") 
{$name = '%';} 

if ($email == "") 
{$email = '%';} 

if ($county == "") 
{$county = '%';} 


$result = mysql_query ("SELECT * FROM site_users 
WHERE username LIKE '%$username%' 
OR name LIKE '%$name%'
OR email LIKE '%$email%' 
OR county LIKE '%$county%'  

"); 

if ($row = mysql_fetch_array($result)) { 

do { 
PRINT "<b>Username: </b> "; 
print $row["username"]; 
print (" "); 
print ("<br>"); 
PRINT "<b>Name: </b> "; 
print $row["name"]; 
print ("<p>"); 
PRINT "<b>Email: </b> "; 
print $row["email"]; 
print ("<p>"); 
PRINT "<b>County: </b> "; 
print $row["county"]; 
print ("<p>"); 
} while($row = mysql_fetch_array($result)); 
} else {print "Sorry, no records were found!";} 
?>

[bluewalrus]

Umm something like this maybe, I dont use mysql. Also this assumes only admins can access this page.

<form method="post" action="search2.php"> 
Search For: 
<p> 
User Name: <input type="text"name="user" size="25" maxlength="25">
<p> 
Name: <input type="text"name="name" size="25" maxlength="25"> 
<p> 
Email: <input type="text"name="email" size="25" maxlength="25"> 
<p> 
<p> 
County: <input type="text"name="county" size="25" maxlength="25"> 
<p> 
<input type=submit> 
</form> 
<?php
$db = mysql_connect('localhost', 'root', '') or die ('Unable to connect. Check your connection parameters.');
mysql_select_db('dvdff2', $db) or die(mysql_error($db));
$result_init = false;
if (isset($_POST['user'])  && $_POST['user'] != "") {
$username = $_POST['user'];
    if ($result_init) {
        $result =. " or  username LIKE '%$username%' ";
    } else {
        $result = "SELECT * FROM site_users WHERE username LIKE '%$username%'";
        $result_init = true;
    }
}
if (isset($_POST['name'])  && $_POST['name'] != "") {
$name = $_POST['name'];
    if ($result_init) {
        $result =. " or  name LIKE '%$name%' ";
    } else {
        $result = "SELECT * FROM site_users WHERE name LIKE '%$name%'";
        $result_init = true;
    }

} 
if (isset($_POST['email'])  && $_POST['email'] != "") {
$email = $_POST['email'];
    if ($result_init) {
        $result =. " or  email LIKE '%$email%' ";
    } else {
        $result = "SELECT * FROM site_users WHERE email LIKE '%$email%'";
        $result_init = true;
    }
} 
if (isset($_POST['county'])  && $_POST['county'] != "") {
$county = $_POST['county'];
    if ($result_init) {
        $result =. " or  county LIKE '%$county%' ";
    } else {
        $result = "SELECT * FROM site_users WHERE county LIKE '%$county%'";
        $result_init = true;
    }
} 
$result = mysql_query ($result);
if ($row = mysql_fetch_array($result)) { 
    do {
    ?>
        <b>Username: </b>
    <?php
        print $row["username"] . " ";
    ?>
        <br><b>Name: </b>
    <?php
        print $row["name"]; 
    ?>
    <p><b>Email: </b> 
    <?php
        print $row["email"]; 
    ?>
    <p><b>County: </b>
     <?php
        print $row["county"]; 
    ?>
    <p> 
    <?php
    } while($row = mysql_fetch_array($result)); 
} else {
    print "Sorry, no records were found!";
} 
?>

[djr33]

Long post, so here's a summary: use AND instead of OR. (Referring to your original code.)


-----

Bluewalrus, that's a good way to approach it. One suggestion: make sure that there is a semicolon ending the query. After all of the "initializing" just add $result .= ';';


(BTW, this is completely irrelevant, but the standard format for a query is to call the query text $query, then the return value from mysql_query() $result. Then loop through those as $row. Of course it doesn't really matter, but calling the query $result is slightly illogical.)


slimline, the code above is a good way to start. I believe it'll work (though I just skimmed it).
Here's the problem with your original approach:
You are searching for all of those fields and allowing them to be blank. Since 'OR' allows only one of the conditions to match, it's the "weakest link" that is always filtering to give you all of the users. So you're searching for a username with 'slimline' OR a name that has an empty string OR an email that has an empty string, etc.
By default EVERY string has an empty string in it: that's like adding 0 to an integer: 1 has infinite 0s added to it. And 'Hello World' has infinite empty strings within it.
In SQL using LIKE and %, you will then find every result because LIKE '%' is in fact saying LIKE 'ANYTHING'.


So now looking at this, I think I have a much simpler answer. Of course this assumes there isn't another problem.

You are trying to limit by ALL of the input [username, email, ....], not just by one or another.

Just use AND instead of OR and I think your original query will work.

Bluewalrus's approach is fine as well and in fact will be a little more efficient in the search (though just very slightly, probably not noticable unless you have thousands and thousands of users).
But you don't need anything that complex, since the only problem is the use of OR (non-limiting) versus AND (limiting).


However, you are right on the edge of getting into something very complex for SQL, so the idea of dynamically building queries in PHP is a good one, so you can learn something from that.

Hope this helps, and if just switching it to 'AND' doesn't fix it (and bluewalrus's code doesn't work), then post back with what the code now does.

By the way, here's a great tutorial for PHP and MySQL. It's clear, to the point, and has relevant examples. It's a good place to start/continue.
(Note: the layout is weird: click 'view all articles', then go to the last page [page 2 currently] and work backwards through them-- they go from oldest to newest increasing in complexity.)
http://php-mysql-tutorial.com

[slimline77]

Hi bluewalrus and djr33,

Thank you so much for replying. I used your code bluewalrus and it worked perfectly, just had to remove the dots after the equals sign shown below and it returned one result from the database.

$result = . " or  username LIKE '%$username%' "; 

I checked out those tutorials that you recommended djr33 and they look good, again thank you both very much, it's hard being a php noobie but with people like you who are willing to help us, it makes it so much easier to learn. I might have one or two more problems, as I'm trying to finish my site, if I do, I'll definately be posting on this helpful forum.

Best wishes to you both.

[djr33]

I'm glad that all worked. Did you also try your original code with AND instead of OR? It's not important if it works now, but especially for maintenance simpler is usually better/easier.