CODEWITHSUNDEEP
phpmysql
Compy
Compy

Reputation: 1177

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

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!

Upvotes: 0

Views: 1354

Answers (3)

Thomas F
Thomas F

Reputation: 73

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.

Upvotes: 1

Ghigo
Ghigo

Reputation: 2332

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

var_dump($count);

Upvotes: 0

Adder
Adder

Reputation: 5868

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.

Upvotes: 0

Related Questions