-1

I am trying to sum a colomn based on the IDs selected from a table that i put in a array. For some reasom only the first ID is used in the Where clausule. When I echo the variable all the ids are there. What am i doing wrong?

$counttheid = array();
$stmt3 = $mysqli->prepare("SELECT 
    id 
    FROM account
    WHERE  level <= '5' AND door = ? AND `group_name` = ? AND betaald = 'Yes'");
    $stmt3->bind_param("ss",$usernamesession,$groupname);
    $stmt3->execute();
    $result3 = $stmt3->get_result(); //only works when nd_mysli is set on the server!
    

    while ($rowid = $result3->fetch_assoc())
{
    $counttheid[] = $rowid['id'];
    $countid = implode(',', $counttheid); // contains all the ids !!


}

$sql = "SELECT SUM(mobcash) AS totalcash FROM account WHERE id IN (?)  
     ";
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param("i",$countid);
    $stmt->execute();
    $stmt->bind_result($row['totalcash']);
    while($stmt->fetch()) $sumcash = $row['totalcash'];

    echo $sumcash; // Somhow only the sum of the first ID of the array !!
    
    echo $countid;// all the ids from the array !!
newusertomysqli12
  • 61
  • 7
  • With a comma separated list of numbers in a string, whatever the first number is _will be_ the only one that is valid because of the comma. There needs to be a `?` for EACH value within the IN clause. See [this pdo IN clause example](https://phpdelusions.net/pdo#in) the concept would be the same. – Paul T. Feb 21 '21 at 23:33
  • Hmm ok. Than how do I use the array in the where clausule? – newusertomysqli12 Feb 21 '21 at 23:35
  • Tried it like this. It does not work.. `$in = str_repeat('?,', count($countid) - 1) . '?';` `SELECT SUM(mobcash) AS totalcash FROM account WHERE id IN (".$in.")` – newusertomysqli12 Feb 21 '21 at 23:45
  • https://stackoverflow.com/questions/772913/how-do-you-use-in-clauses-with-mysqli-prepared-statements – Jesse Feb 22 '21 at 03:46
  • Does this answer your question? [How do you use IN clauses with mysqli prepared statements](https://stackoverflow.com/questions/772913/how-do-you-use-in-clauses-with-mysqli-prepared-statements) – Jesse Feb 22 '21 at 03:46

1 Answers1

0

Not only for the in, but the number of bind parameters will need to match as well.

Try with this example for the code from the while to the execute:

while ($rowid = $result3->fetch_assoc())
{
    $counttheid[] = $rowid['id'];
    // $countid = implode(',', $counttheid); // contains all the ids !!
}

$in = str_repeat('?,', count($counttheid) - 1) . '?';
$types = str_repeat('i', count($counttheid));
$sql = "SELECT SUM(mobcash) AS totalcash FROM account WHERE id IN ($in)";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param($types, ...$counttheid);
$stmt->execute();

At the bind_param, the part with ...$counttheid, the ... portion is the argument unpacking operator.

Paul T.
  • 4,703
  • 11
  • 25
  • 29
  • Gives me the folowing error. Uncaught mysqli_sql_exception: No data supplied for parameters in prepared statement in – newusertomysqli12 Feb 22 '21 at 00:18
  • Tried it like this: `while ($rowid = $result3->fetch_assoc()) { $counttheid[] = $rowid['id']; $countid = implode(',', $counttheid); } $in = str_repeat('?,', count($counttheid) - 1) . '?'; $sql = "SELECT SUM(mobcash) AS totalcash FROM account WHERE id IN ($in)"; $stmt = $mysqli->prepare($sql); $types = str_repeat('s', count($counttheid)); $stmt->bind_param($types,...$counttheid); $stmt->execute(); $stmt->bind_result($row['totalcash']); while($stmt->fetch()) $sumcash = $row['totalcash']; ` – newusertomysqli12 Feb 22 '21 at 00:24
  • I'm not sure what we may have different? Try this [PHPize](https://phpize.online/?phpses=eabe15c9d65533177551831a769c683d&sqlses=01100bd56e086e4efb39e60bc3670571&php_version=php7&sql_version=mysql57) code. Run the SQL first, then run the PHP. – Paul T. Feb 22 '21 at 00:44
  • Well in this example, which works as you pruposed. The array is like this $counttheid = [1, 2, 3]; but this is hardcoded, how do I replace that 1,2,3 array for the selected values from the table? I guess that is where it goes wrong than.. – newusertomysqli12 Feb 22 '21 at 08:17
  • Ok, i have tried to change your example. See link below. What I do not understand is, how come the echo shows id 1,2,4,5 etc. To make it work i should do [1,2,3]. But when I replace the [1,2,3] for the variable that contains 1,2,3,4,5 etc it only takes the first one in this case.. [link]https://phpize.online/?phpses=dbf897735a295f5b2f610bdee6ce5212&sqlses=2334053f9a675dc226b47c9bbbcfc4af&php_version=php7&sql_version=mysql57[/link] – newusertomysqli12 Feb 22 '21 at 08:44
  • Updated example.. https://phpize.online/?phpses=db9d94e3b3c98e9da19b9bd0a15c7303&sqlses=2334053f9a675dc226b47c9bbbcfc4af&php_version=php7&sql_version=mysql57 getting realy frustrated how to make this work.. tried all sorts of examples but either it wont work or just keeps using the first id it selects.. – newusertomysqli12 Feb 22 '21 at 10:02
  • How come my question gets closed! I do not see how that answer is helping me? Do consider me not being a profesional developer, i do not know how to use those answers for my problem?? – newusertomysqli12 Feb 22 '21 at 12:39
  • I did not close the question, but I did update the [PHPize](https://phpize.online/?phpses=b1b6d3fe45f09f66b6014f7220fc6c91&sqlses=01100bd56e086e4efb39e60bc3670571&php_version=php7&sql_version=mysql57) to not have a hard-coded array, and still gives the same output. Run the SQL first, then the PHP. – Paul T. Feb 22 '21 at 15:26