Value not saving in array

Question

I have an array which have this structure:

$queues[n] Array (
[id] => integer
[idClient] => integer
[name] => string
[people] => integer )

Which is populated with:

$query = "SELECT clients.idClient AS 'idClient', queues.idQueue AS 'idQueue', queues.name AS 'name' FROM clients, queues WHERE clients.idClient = queues.client";
    $queues = null;

    $result = mysql_query($query);
    if ($result){
        while ($queue = mysql_fetch_assoc($result)){
            $queues[] = array ("idClient" => $queue['idClient'], "id" => $queue['idQueue'], "name" => $queue['name'], "people" => 0);
        }
    }

Each 'n' value match a queue from Database and people, by default is set to 0.

After populating the array I query the database again with each queue to other table to obtain the number of people in queue with a query like this:

SELECT COUNT(*) FROM peoplequeued WHERE queue ='".$queue['name']."'

And then:

$result = mysql_query($query);
        if ($result){
            $num_people = mysql_fetch_row($result);
            $queue['people'] = $num_people[0];
        }

And something strange happens here. If I echo the $queue['people'] in the foreach, it shows fine the value it got but if I preview the full array before returning it, it's back to 0.

What could be happening?

Just a hint: If you have wrong code, don't write here what you see as the right code. Just copy/paste the whole relevant sequence. There may be traps you didn't notice, and didn't place them here. The other hint: Do it all in one single query. — Alex
– Alex, Commented Jun 6, 2011 at 8:49
As a side note: I recommend looking into PDO and prepared statements. Besides a performance gain you will also gain a lot in recurity. The query you show is typically vounerable to SQL Injection. — Arend
– Arend, Commented Jun 6, 2011 at 8:50
@AlexanderMP there is no more code to paste, just foreachs. @Arend this code can't be injected since you aren't able to change in anyways, the query. — Antonio Laguna
– Antonio Laguna, Commented Jun 6, 2011 at 8:53
@Antonio, that's exactly my point. Those foreach statements may be the actual cause, but we are unable to determine that because we can't see them. You admitted you had a mistake in your code, but didn't let us figure out where this error is. — Alex
– Alex, Commented Jun 6, 2011 at 8:56

binaryLV · Accepted Answer · 2011-06-06 08:55:32Z

5

My guess is that you're looping through $queues with foreach loop like this:

foreach ( $queues as $queue ) {
    $result = mysql_query($query);
    if ($result){
        $num_people = mysql_fetch_row($result);
        $queue['people'] = $num_people[0];
    }
}

If so, there's no "link" between $queue and $queues[$n], i.e., by modifying $queue, you do NOT modify $queues.

If this is the case, you should either use $queue as reference variable, or modify $queues[$n] with $n being an index.

foreach ( $queues as &$queue ) { // add & so $queue is a reference to an element in $queues
    $result = mysql_query($query);
    if ($result){
        $num_people = mysql_fetch_row($result);
        $queue['people'] = $num_people[0];
    }
}
unset($queue); // drop the reference, otherwise you might have unexpected results after modifying $queue outside the loop

... or ...

foreach ( $queues as $n => $queue ) { // store index of "current" element in $n
    $result = mysql_query($query);
    if ($result){
        $num_people = mysql_fetch_row($result);
        $queues[$n]['people'] = $num_people[0]; // change $queue to $queues[$n]
    }
}

Alternatively, I would advise you to think about getting all data in a single statement. It looks like something like this might work for you:

select
    baseTable.id,
    baseTable.idClient,
    baseTable.name,
    peopleCount.count as people
from
    baseTable
    left join (
        select
            count(*) as count,
            queue
        from
            peoplequeued
        group by
            queue
    ) as peopleCount on peopleCount.queue = baseTable.name

answered Jun 6, 2011 at 8:55

binaryLV

9,1422 gold badges43 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Antonio Laguna Over a year ago

Thanks a lot for that, I thought they were linked, that is not a copy but a pointer. Thanks :D

binaryLV Over a year ago

They are linked, if you add & before the variable. But be careful with this - variable stays defined and linked to the last element of an array, if you do not unset() it after the loop. Also, I have updated my answer with an alternative solution that would allow you to get rid of multiple queries - doing DB queries in a loop is very bad practice.

Antonio Laguna Over a year ago

I have to try and adapt it because it's not working with this error: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN (SELECT COUNT(*) AS 'count', cola FROM relcolaext GROUP BY cola)" but thanks for your help. It's always good to improve the code and performance.

binaryLV Over a year ago

I created baseTable table that consists of id, idClient and name columns and peoplequeued table that consists of id, queue and name fields. With such tables, that query works without any syntax error. If you have where clause, be sure to write it after the join, i.e., select ... from some_source left join another_source where .... Also, I'd advise to change FROM clients, queues WHERE clients.idClient = queues.client part to a join.

Alex · Accepted Answer · 2011-06-06 08:53:32Z

3

It sounds like you're probably using a foreach loop to cycle through your $queues array, like this:

foreach($queues as $queue) {

}

Each $queue variable generated is a copy, and changes to it aren't saved to the $queues variable. To save changes as you're intending, you need to do something like:

foreach($queues as $k => $queue) {
    $queue['people'] = 10;
    $queues[$k] = $queue;
    // or, more efficiently...
    $queues[$k]['people'] = 10;
}

In PHP5 you can also reference. See http://uk.php.net/manual/en/control-structures.foreach.php

answered Jun 6, 2011 at 8:53

Alex

3,0993 gold badges27 silver badges48 bronze badges

1 Comment

Antonio Laguna Over a year ago

Thanks for your answer too. I didn't know that of PHP

Collectives™ on Stack Overflow

Value not saving in array

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related