How to use more than 1 quotation using getRows method and .implode in sql Query, using PHP-CodePudding

I'm trying to get all records from db using getRows method, to achieve that I need to implode special characters.

In previous function, to get $ids I used:

foreach ($ids as &$id) $id = (int)$id;
        $rows = $this->db->getRows('SELECT name, id FROM database WHERE id IN ('.implode(',', $ids).')');
        if (count($rows)) {
            foreach ($rows as $row) 
            {
                $ret[$row['id']] = $row['name'];
            }
        }

but in my next function I need to use name to search for records.

Because name is in single quotes 'name' I tried making it like this:

foreach ($names as $name) $id=(int)$name;
        $rows = $this->db->getRows('SELECT name, is_active FROM database WHERE name IN ('.implode(',',$names).')');
        if(count($rows))
            {
                foreach ($rows as $row)
                {
                    $ret[$row['name']] = $row['is_active'];
                }
            }

it doesnt solve the problem, it just crashes. So I tried changing it a bit with separating it with double quotes:

$rows = $this->db->getRows("SELECT name, is_active FROM database WHERE name IN (" .implode(',',$names) .")");

still getting same error database query error.

and I dont know really what to do next. I believe I cant pass that many quotes inside of a implode. Query should look like this: SELECT name, is_active FROM database WHERE name IN ('name1', 'name2', 'name3')

I tried to follow PHP: implode - Manual with same error results.

CodePudding user response：

Switching between single or double quotes like you did, doesn't change the fact that you did not add any quotes around the individual name values at all. These quotes are the string delimiters the PHP syntax requires, but you have not added any quotes around the names in your implode yet, which the SQL syntax requires.

And implode only inserts the separator between the values - so the quote character before the first, and the quote character after the last item, still need to be added.

You want something like

'SELECT name, is_active FROM database WHERE name IN ("'.implode('","',$names).'")'

which will produce

SELECT name, is_active FROM database WHERE name IN ("a","b","c")