[SOLVED] search query to two tables not working....

[CodeMama]

I can display all the results from the two tables but my search isn't working so I can't get it to say display all the "A" records....

    // Run query on submitted values. Store results in $SESSION and redirect to restaurants.php
    
    $sql = "SELECT name, address, inDate, inType, notes, critical, cviolations, noncritical FROM restaurants, inspections WHERE restaurants.name <> '' AND restaurant.ID = inspection.ID";
    if ($searchName)
    {
     $sql .= "AND restaurants.name LIKE '%". mysql_real_escape_string($name) ."%' ";
    
    if(count($name2) == 1)
    {
        $sql .= "AND restaurants.name LIKE '%". mysql_real_escape_string($name2[1]) ."%' ";
    }
    else
    {
        foreach($name2 as $namePart)
        {
        $sql .= "AND restaurants.name LIKE '%". mysql_real_escape_string($namePart) ."%' ";
        }
    }
    }
    if ($searchAddress) {
        $sql .= "AND restaurants.address LIKE '%". mysql_real_escape_string($address) ."%' ";
    }
   
    
    
    $sql .= "ORDER BY restaurants.name;";
    
        
    $result = mysql_query($sql);
    

[kickstart]

Hi

 

Think this:-

 

    if(count($name2) == 1)

    {

        $sql .= "AND restaurants.name LIKE '%". mysql_real_escape_string($name2[1]) ."%' ";

    }

 

should be:-

 

    if(count($name2) == 1)

    {

        $sql .= "AND restaurants.name LIKE '%". mysql_real_escape_string($name2[0]) ."%' ";

    }

 

First occurance of an array is 0 not 1.

 

Also when you loop round you probably want OR instead of AND for each check on restaurants.name

 

All the best

 

Keith

[rhodesa]

if that doesn't work, echo $sql, and figure out where the statement differs from what you expect

 

also, @CodeMama, you should know to use mysql_error() by now

    $result = mysql_query($sql) or die(mysql_error());