simple php - Mysql search not working properly

Question

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 :)

Please oh please read this about PHP MySQL injection and then read this PHP MySQL Examples it will help you loop your results — Manse
– Manse, Commented Mar 1, 2012 at 19:24
Aside from the above notes on SQL injection, I think it is good practise to space out your code (whatever language it is) so you don't go over a particular margin limit. I use 100 characters, but whatever works for you - it makes it more readable, and means you don't have to horizontally scroll in your IDE (or in an html code block, as per your published question). — halfer
– halfer, Commented Mar 1, 2012 at 19:28
@EscoMaji that fixed the first problem. Search now works :D. Thanks. & ManseUK will read. It's for internal use only so injection isn't really a worry but I of course need to learn about it! thank you for the links! — cmplieger
– cmplieger, Commented Mar 1, 2012 at 19:39

user17753 · Accepted Answer · 2012-03-01 19:34:34Z

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.

halfer · Accepted Answer · 2012-03-01 19:33:32Z

0

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 :)

answered Mar 1, 2012 at 19:33

halfer

20.2k20 gold badges110 silver badges207 bronze badges

Collectives™ on Stack Overflow

simple php - Mysql search not working properly

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related