CODEWITHSUNDEEP
phpmysql
cmplieger
cmplieger

Reputation: 7371

simple php - Mysql search not working properly

I have this code:

<?php
// Make a MySQL Connection
$dbhost = 'xxx';
$dbuser = 'xxx';
$dbpass = 'xxx';
$dbname = 'xxx';

    $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
    mysql_select_db($dbname);

// Retrieve all the data from the "example" table
$result = mysql_query("SELECT * FROM clients WHERE FNAME='".$_POST['clientsearch']."' OR LNAME='".$_POST['clientsearch']."' OR MAIL='".$_POST['clientsearch']."' OR TEL='".$_POST['clientsearch']."'"")
or die(mysql_error());  

// store the record of the "example" table into $row
$row = mysql_fetch_array( $result );
// Print out the contents of the entry 

echo "FName: ".$row['FNAME'];
echo "LNAME: ".$row['LNAME'];
echo "FName: ".$row['MAIL'];
echo "LNAME: ".$row['TEL'];

?>

The goal is to search my mysql database to find the result of $_POST['clientsearch'] in one of the fields and return the lines that have that word in it (it is always 1 word)

If I use this:

$result = mysql_query("SELECT * FROM clients WHERE FNAME='".$_POST['clientsearch']."'"")

it seems to work. but it only searches in the FNAME column, not all of them. Also I only get the first result back. not all.

I'm starting php/mysql so I'm a little lost and don't know all functions yet. Could someone explain how I could fix my code up?

Thanks a lot for your help :)

Upvotes: 1

Views: 219

Answers (2)

user17753
user17753

Reputation: 3161

For starters, you'll need to loop through each row in your result set if you're expecting more than 1 row. I illustrate how to do this with your original code.

<?php
// Make a MySQL Connection
$dbhost = 'xxx';
$dbuser = 'xxx';
$dbpass = 'xxx';
$dbname = 'xxx';

    $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
    mysql_select_db($dbname);

// Retrieve all the data from the "example" table
$result = mysql_query("SELECT * FROM clients WHERE FNAME='".$_POST['clientsearch']."' OR LNAME='".$_POST['clientsearch']."' OR MAIL='".$_POST['clientsearch']."' OR TEL='".$_POST['clientsearch']."'"")
or die(mysql_error());  

// Print out the contents of the entry for each row in result 
while( $row = mysql_fetch_array( $result, MYSQL_ASSOC ) ) { 
    echo "FName: ".$row['FNAME'];
    echo "LNAME: ".$row['LNAME'];
    echo "FName: ".$row['MAIL'];
    echo "LNAME: ".$row['TEL'];

}

?>

Take a look in the PHP documentation on mysql_real_escape_string for starters on the injection stuff.

Also, as others stated you may be looking for the LIKE instead of = SQL syntax. Also, look into the % wild card for LIKE.

Upvotes: 1

halfer
halfer

Reputation: 20335

Not an answer to the question, but hopefully helpful. Try writing code like this:

$cs = mysql_escape_string($_POST['clientsearch']);
$result = mysql_query("
    SELECT
        *
    FROM clients
    WHERE
        FNAME='$cs'
        OR LNAME='$cs'
        OR MAIL='$cs'
        OR TEL='$cs'
");

Exactly how you indent is up to you. This approach helps a great deal with readability, and hence also debugging :)

Upvotes: 0

Related Questions