CODEWITHSUNDEEP
phpsqlsql-like
Garret Kaye
Garret Kaye

Reputation: 2562

How to use like in an in sql statement

Hi I'm using PHP to try and retrieve data from a database while looping through an array inside the in statement like this.

$sql2 = "SELECT * FROM pixacour_pixacourt.UserCaseMessages WHERE FavorIDs IN  (" ;
    $count = 0;
foreach($followingArray as $value)
{
    if($count==count($followingArray)-1)
    {
        $sql2 .= "LIKE";
        $sql2 .= "%'".$value."'%";
    }
    else
    {
        $sql2 .= "LIKE";
        $sql2 .= "%'".$value."'%,";        
    }
    ++$count;
}
$sql2 .= ")";

I get this error, that says

"trying to get property of non object"

I can't figure out what is going on any suggestions would be much appreciated thank you for your time.

Upvotes: 1

Views: 63

Answers (1)

Tim
Tim

Reputation: 485

You should not be using LIKE in an IN clause but you do need to comma delimit elements. No need to keep track of the count, either. foreach will cease when the array has been traversed and you can trim off the trailing comma.

$sql2 = "SELECT * FROM pixacour_pixacourt.UserCaseMessages WHERE FavorIDs IN  (" ;
foreach($followingArray as $value)
{
        $sql2 .= "'".$value."', ";        
}
$sql2 = rtrim($sql2,',');
$sql2 .= ");";

If this fails, like Gordon says, echo $sql2 and the syntax error will probably be clear.

If you do indeed need to use LIKE and wildcard matching, you could append multiple OR clauses, one for each $value.

$sql2 = "SELECT * FROM pixacour_pixacourt.UserCaseMessages WHERE " ;
$whereClause = "";
foreach($followingArray as $value)
{
        $whereClause .= " OR FavorIDs LIKE '%".$value."%' ";        
}
$whereClause = ltrim($whereClause, " OR");
$sql2 .= $whereClause . ";";

UPDATE 1/9/15

I realized there's a bug in this code in that when $followingArray is empty, we'd receive a MySQL syntax error because the query ends with "WHERE". Here's a new version which resolves that bug:

$sql2 = "SELECT * FROM pixacour_pixacourt.UserCaseMessages" ;
if(count($followingArray) >= 1) {
    $sql2 .= " WHERE";
}
$whereClause = "";
foreach($followingArray as $value)
{
        $whereClause .= " OR FavorIDs LIKE '%".$value."%' ";        
}
$whereClause = ltrim($whereClause, " OR");
$sql2 .= $whereClause . ";";

Upvotes: 1

Related Questions