1

I'm currently trying to get rows from my MySQL DB in a specific order.

Therefore, I found a query like this useful:

SELECT * FROM tbl_items WHERE id IN (130,129) ORDER BY FIELD(ID, 130,129)

When I run this directly in MySQL, everything is working fine.

Now in PHP, I'm using the database framework Medoo.

What I do there is running this query:

$asservate = $database->query("SELECT * 
                                FROM tbl_items 
                                WHERE ID in (:ids) 
                                ORDER BY FIELD(ID,:ids)",
                            [":ids" => intval($items)])
                        ->fetchAll();

What this query results in, when I echo the last executed query, is the following:

SELECT * FROM tbl_items WHERE ID in ('129,130') ORDER BY FIELD(ID,'129,130')

So the values are entered as strings and that seems to be the problem. The result is, that I only get row number 129 returned, no other one.

Is there any idea how I could force medoo to not enter the values with ' or how I could force MySQL to still execute the query, even with the ' there?

Thanks in advance!

EDIT:

Here's the code for the query-function of Medoo:

public function query($query, $map = [])
{
    if (!empty($map))
    {
        foreach ($map as $key => $value)
        {
            switch (gettype($value))
            {
                case 'NULL':
                    $map[ $key ] = [null, PDO::PARAM_NULL];
                    break;

                case 'resource':
                    $map[ $key ] = [$value, PDO::PARAM_LOB];
                    break;

                case 'boolean':
                    $map[ $key ] = [($value ? '1' : '0'), PDO::PARAM_BOOL];
                    break;

                case 'integer':
                case 'double':
                    $map[ $key ] = [$value, PDO::PARAM_INT];
                    break;

                case 'string':
                    $map[ $key ] = [$value, PDO::PARAM_STR];
                    break;
            }
        }
    }

    return $this->exec($query, $map);
}
nameless
  • 1,483
  • 5
  • 32
  • 78
  • Would be useful to see the code for `->query()` as it is obviously not vanilla PHP code. But it is probably due to the parameter being assumed to be strings. You will need to make sure you are using a `bindParam()` that gives the correct datatype [see Manual page and example 1](http://php.net/manual/en/pdostatement.bindparam.php) – RiggsFolly Feb 19 '19 at 09:49
  • @RiggsFolly I just edited my question and copied the `query`-function from Medoo in here. I don't think I can use bindParam() in Medoo querys... – nameless Feb 19 '19 at 09:52
  • Ok so the auto datatype detection is not detecting that your param is a number – RiggsFolly Feb 19 '19 at 09:54
  • @RiggsFolly seems like, yes. and the problem is, that it's not a single number, but integer, integer, integer, .... – nameless Feb 19 '19 at 09:55
  • Your query can be just used `select()` to generate for that. It's supported for array of ids. Why use `query()` for? – Angolao Mar 04 '19 at 19:19
  • were you ever able to solve this problem? having the exact same issue.. – Ben Dec 10 '21 at 01:54

0 Answers0