Question 234942 in PHP Question posted in
Official manual can be found here.

Php – List-Array in Where clause MySQL

I have fetched ids from account table as below:-var_dump($rows_i);

array(3) { [0]=> array(1) { ["id"]=> string(1) "2" } [1]=> array(1) { ["id"]=> string(1) "4" } [2]=> array(1) { ["id"]=> string(1) "5" } }

I am trying to fetch all debit amounts for the ids in the list per below SQL query:-

COALESCE((SELECT sum(amount) amnt FROM journal where debit_account_id in (".implode(",",$rows_i).")),0)

Am getting Warning and Fatal Error per below:

Warning: Array to string conversion in F:xampphtdocs…………….

Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘Array’ in ‘where clause’ in F:xampphtdocs………………

Tags:

2

Answers


  1. 0

    As has been pointed out in the comments:

    Firstly, $rows_i is an array of arrays; you can extract the individual id values using array_column:

    $ids = array_column($rows_i, 'id');

    You could then use that array inside the implode in your query.

    PHP demo on 3v4l.org

    Secondly, you could get all this data in one query. For example:

    SELECT COALESCE(sum(amount), 0) AS amnt
FROM journal j
JOIN account a ON j.debit_account_id = a.id
WHERE <the condition you used to generate $rows_id>

    Example demo on dbfiddle

    Login or Signup to reply.
  2. 0

    Check this out:

    <?php

// Example array of ids
$rows_i = [
    ['id' => 'f232ba33q21423'],
    ['id' => '5eftdasdafewrq'],
];

$ids = implode("','", array_column($rows_i, 'id'));
$sql = "SELECT COALESCE((SELECT SUM(amount) amnt FROM journal WHERE debit_account_id IN ('{$ids}')),0)";
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search