php – How I can make select from database WHRE in Array-ThrowExceptions

Exception or error:

I need to make a database query that includes several WHERE conditions.

The problem is that I cannot correctly pass the $messages array into the request.

public function Controller(Request $request)
    {
        $firstDate = $request->input('start');
        $secondDate = $request->input('end');
        $messages = $request->input('messages');
        $data = $this->client->executeQuery( "SELECT sum('count') as 'var', 'firstname', 'surname' FROM 'table' WHERE 'surname' IN (?) AND 'time' BETWEEN ? AND ? group by 'surname' ORDER BY 'var' desc limit 10",
            [$messages, $startDate, $endDate]);
        $data = $data ->getData();

        return response()->json($data);
    }

But as a result, I get a mistake:

message: “Bind value type ARRAY for parameter 1 is not supported”

I use tarantool database. Any ideas?

How to solve:

First of all, your syntax is incorrect, it should be

executeQuery('...', $messages, $startDate, $endDate);

instead of

executeQuery('...', [$messages, $startDate, $endDate]);

(please note the redundant [] in your version).

Second, If I understand your question correctly, you need to unfold your $messages array and pass each value as a separate argument to executeQuery(). In addition, you need to dynamically construct your SQL statement with as many placeholders inside IN() as the $message array size. Something like this:

$placeholders = rtrim(str_repeat('?,', count($messages)), ',');
$params = $messages;
$params[] = $startDate;
$params[] = $endDate;

$sql = "... IN ($placeholders) AND 'time' BETWEEN ? AND ? ...";
$result = $this->client->executeQuery($sql, ...$params);

Answer:

Arrays are unsupported in SQL now, so you can’t bind values of array type (exactly what error message says). You can track progress of supporting this feature by subscribing to corresponding issue: https://github.com/tarantool/tarantool/issues/4762

Answer:

try this

$messages = $request->input('messages');
$messages = implode(',',$messages);

then your code

Leave a Reply

Your email address will not be published. Required fields are marked *