Problem with 'LIKE' operator

[Gavski]

This is a function that searches an address DB, compares the $name with 'LIKE' and returns the address details.
If no address is found, I want to go to another function.

In my form, if I leave $name blank it works, but if I put a $name that is not in the database, $result still returns a value and doesn't seem to recognize that there is no record??

any ideas, thanks.


<?php

function getsupp()
{
global $name,$address1,$address2,$address3,$address4,$pcode,$email;

mysql_select_db("picture_cards");

$result = mysql_query("SELECT *
FROM supplier
WHERE name LIKE('$name%')") or die(mysql_error());

if($result ){
while($row = mysql_fetch_array($result))
{
$name = $row['name'];
$address1 = $row['address1'];
$address2 = $row['address2'];
$address3 = $row['address3'];
$address4 = $row['address4'];
$pcode = $row['pcode'];
$email = $row['email'];

}//endwhile


}//endif
}// endfunc getsupp()

?>

[smerny]

Can you give an example value of $name and what the resulting record's name field is?

[cyberRobot]

$result will only contain false if the query failed. Otherwise it returns a result set which will evaluate to true. Even if there were no matches for the query, it still returns a result set. The query was successful; there just weren't any matches.

 

You could test if any rows were returned.

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

More information about mysql_num_rows() can be found here:

http://www.php.net/manual/en/function.mysql-num-rows.php

[Barand]

DON'T use global. Have your function return the found results.

 

Also you want to avoid other side effects of calling your function. If you have to select another database (other than the default) inside the function then you should restore the database connection before returning.

 

e.g.

function getsupp($name)
{
    // get the current database
    $result = mysql_query("SELECT DATABASE()");
    list($currentDB) = mysql_fetch_row($result);
    
    mysql_select_db("picture_cards");
    $records = array();
    $result = mysql_query("SELECT name, address1, address2, address3, address4, pcode, email
        FROM supplier
        WHERE name LIKE('$name%')") or die(mysql_error());

    if($result ){
        while($row = mysql_fetch_assoc($result))
        {
            $records[] = $row;
        }//endwhile
    }//endif
    
    // restore current database connection
    mysql_select_db($currentDB);
    
    // return array of found records
    return $records;
}// endfunc getsupp()

$res = getsupp('fred');
if (res) {
    // process the array of results returned in $res
}

[Gavski]

Thanks Cyber Robot, Managed to do this from your help and it works fine.

 

$num_results = mysql_num_rows($result);

if($num_results > 0)

returns 0 if no records found.

 

Also Barand, I have gone through my function and amended it (I kind of knew globals were a bad thing), now running like a well oiled address book, 

thanks for all help and tips