MySQL Count in PHP while loop only returns one result then Null

Question

Ok, so I have some MySQL tables as follows:

Buildings
Building-ID    Building-Name
===========----=============
1              Building-1
2              Building-2
3              Building-3
4              Building-4


Building-1
Mroom    State
=====----======
1        Booked
2        Empty
3        Empty
4        Empty

Building-2
Mroom    State
=====----======
1        Booked
2        Empty
3        Empty
4        Empty

And a query in PHP as follows (Ignore the hard coded while, I've simplified the code a bit):

$sql = "select * from Buildings";
$result = mysql_query ($sql) or die(mysql_error());
while ($row = mysql_fetch_array($result)) 
{ 
$building[] = $row['ward_name'];
}    


$v1 = 0;
while ($v1 < 4)
{
$sql = "SELECT COUNT(*) FROM `$building[$v1]` WHERE state = 'Empty'";
$result = mysql_query($sql) or die(mysql_error());
$count = mysql_result($result, 00);

var_dump($count[$v1]);
$v1 = $v1 + 1;
}

To my way of thinking this should create an array of the buildings contained in the "Buildings" table, start a loop, load the building name from the array and provide a row count for the table of how many rows contain "Empty" in the state column. What it actually does is provide a count for the first table and then provides "NULL" for the rest.

I'd appreciate any help you can give me.

Cheers!

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial. — h2ooooooo
– h2ooooooo, Commented Feb 27, 2013 at 15:08
Start debugging. First: what's the value of $building after the first loop? — Fabian Schmengler
– Fabian Schmengler, Commented Feb 27, 2013 at 15:10

Thomas F · Accepted Answer · 2013-02-27 15:21:38Z

1

What about changing your data model?

Table buldings can be kept as is:

Buildings
Building-ID    Building-Name
===========----=============
1              Building-1
2              Building-2
3              Building-3
4              Building-4

New table:

Rooms
Building-ID Mroom State
===========-=====-=====
1           1     1
1           2     0
2           1     0

State 0 = Empty, State 1 = Booked

Then use a join with group by:

select count(*) from buildings b inner join rooms r on r.bid = b.id where r.state = 0 group by b.id;

Then you will get a row for each building with the count of empty rooms. You won't need a table for each building.

answered Feb 27, 2013 at 15:21

Thomas F

731 silver badge8 bronze badges

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

Comments

Adder · Accepted Answer · 2013-02-27 15:11:09Z

0

This does noit make sense:

$count = mysql_result($result, 00);

var_dump($count[$v1]);

you mean to write:

$count[$v1] = mysql_result($result, 00);

var_dump($count[$v1]);

Also do not use several tables with names matching columns of other tables. You can use one table with a primary key that spans two columns instead, for example create primary key on($buildingid,$roomid) so that the table has columns $buildingid,$roomid, and $state.

answered Feb 27, 2013 at 15:11

Adder

5,8982 gold badges35 silver badges60 bronze badges

Comments

Ghigo · Accepted Answer · 2013-02-27 15:13:54Z

0

mysql_result() returns a string, not an array. Modify the code and check that now it works as expected.

var_dump($count);

answered Feb 27, 2013 at 15:13

Ghigo

2,3621 gold badge20 silver badges20 bronze badges

Collectives™ on Stack Overflow

MySQL Count in PHP while loop only returns one result then Null

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related