-1

I am trying to use the selected id's as an array a other statement. It seems it is not counting all the result as it is much lower that it is.. I have tried to find my answer on google but none of the options are working for me or i do not know how to use them in my case. There are no errors and i have error log on!

Here is my code, 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);
}

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

I am no profesional developer just started learning this, any help is welcome!

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
newusertomysqli12
  • 61
  • 7
  • What's the purpose of `foreach($dataid as $rowid);`? You have severe problems with your code and it seems to be incomplete –  Feb 21 '21 at 20:59
  • Well, I thought this is needed to use the IDs in my query. Problem is, i tried so much to get the result. What are you missing in the code? – newusertomysqli12 Feb 21 '21 at 21:03
  • If you are only starting to learn PHP then you should learn PDO instead of mysqli. PDO is much easier and more suitable for beginners. Start here https://phpdelusions.net/pdo & https://websitebeaver.com/php-pdo-prepared-statements-to-prevent-sql-injection – Dharman Feb 21 '21 at 21:26
  • That should mean the whole website i am working in should be rewriten. All I now need to know is how to put the ID result in an array and use that array in the where statement of the second select statement. – newusertomysqli12 Feb 21 '21 at 21:28
  • Does this answer your question? [Can I bind an array to an IN() condition?](https://stackoverflow.com/questions/920353/can-i-bind-an-array-to-an-in-condition) – sticky bit Feb 21 '21 at 22:02
  • See the duplicate. However you may also consider using a join to directly get the second result without that extra and potentially unnecessary roundtrip. – sticky bit Feb 21 '21 at 22:04
  • Thanks for the reactions. These are all pdo examples. I am using Mysqli. I am trying the following, which seems to work but it look like not all the id's are used to make the count. I will update the question – newusertomysqli12 Feb 21 '21 at 22:17

1 Answers1

0

Since you have edited the question, my original answer is no longer relevant.

I suggest for you to simplify your two queries into a single query. In your first query you select a bunch of ids and in the second query you sum a different value from the same table using the ids. You can just to that in one query:

SELECT SUM(mobcash) AS totalcash
FROM account
WHERE level <= '5'
  AND door = ?
  AND `group_name` = ?
  AND betaald = 'Yes';

Original answer

You use $result->fetch_all(MYSQLI_ASSOC), meaning each row from the result set will be an associative array with the column names as the keys and the cell values as values. That is also the case, if you only select one column.

That means for this example table

 id | name | balance 
----+------+---------
  1 | acc1 |   12.34
  2 | acc2 |    1.23

your variable $dataid will have the following value (for the simplified query SELECT id FROM account):

$dataid = [
  [
    "id": 1
  ],
  [
    "id": 2
  ]
];

To get more familiar with PHP, you could write some foreach loops yourself, but you can also use the built-in PHP function array_column (php.net: array_column):

$ids = array_column($dataids, "id");

From an SQL perspective I would also suggest for you to learn about nested queries, since you could avoid this PHP logic altogether.

cadast
  • 101
  • 1
  • 6
  • Thanks. I have allready edited the question where i try it a other way. Problem now is that not all the ids seems to be taken in account. What the sum statement for the total results goes. I have 25 more of these to work this code in. I dont know other ways to count the diferend results in one query, this is just one of many. So putting it in one query is prefereble but i have no idea where to begin. – newusertomysqli12 Feb 21 '21 at 22:25
  • @newusertomysqli12 I have updated my answer - try to emulate on a piece of paper which records are selected (and when) in the two queries vs the single query to get a better understanding of how this works. – cadast Feb 21 '21 at 22:37
  • Thanks. The thing is, the select query is based on the filter a user uses. There are 10 diferend select querys with if statements. So i need the sum the change depending on the filter and so the select query one uses. The answer you gave me is the one i allready use, i need the id part to make it dynamical based on the select. That is why i need the array and IDs to do the sum – newusertomysqli12 Feb 21 '21 at 22:38
  • I'm not entirely sure I understand your situation, but you could try to put the filters into the single query? Otherwise you could use a nested query, where you just put your first query into the `IN ()` of the second query. This might be slow - refer to [this question](https://stackoverflow.com/questions/6135376/mysql-select-where-field-in-subquery-extremely-slow-why) to optimize. I recommend you look into nested queries, as they are incredibly useful. There are also a lot of ways you can rewrite the WHERE ... IN statement, as these can be really annoying/slow. – cadast Feb 21 '21 at 22:48
  • I understand your suggestions. I have a statistics on the database that counts and sums up diferend things based on diferend select statements. To avoid a long list of querys etc i just ask the simple question. How do i array the id in the select statement. So the question based on the code above i need answered. How come the query only uses 1 id to sum while the array and the implode contain all the ids? – newusertomysqli12 Feb 21 '21 at 22:54
  • For your understanding. filter 1. select id from account where betaald = 'yes' // this gives all the rows with betaald yes and the sum should count the colomn mobcash. filter 2. select id from account where betaald = 'no' and the sum should count the colomn mobcash for the no clausule. and more filters are like that. And besides that sum example there are 25 more counts and so on.. based on that single yes and no query – newusertomysqli12 Feb 21 '21 at 22:59
  • Ah okay, I see why you‘d want two queries. In that case my answer is useless ^^ You can adapt the answer sticky bit linked - construct the string in ˋbind_paramˋ for your parameters and use the spread operator to supply the list of ids as single arguments – cadast Feb 21 '21 at 23:08
  • That are all pdo examples. I have google this and come agros these pages. I cant figur it out. I have all the results in the variable but It uses only one in the select query. What am i doing wrong ? – newusertomysqli12 Feb 21 '21 at 23:17
  • Only the first ID is used ! – newusertomysqli12 Feb 21 '21 at 23:18