CODEWITHSUNDEEP
phpmysqldatabasecount
Onah Onally Sunday
Onah Onally Sunday

Reputation: 71

Count the number of rows and compare to a given number

I want to count rows on my database and use it to check a value on my database

$result =mysqli_query($cn,"SELECT * FROM set_ballot");
while($row = mysqli_fetch_array($result)) {
$room_number = $row['room_number'];
}
$rows= mysqli_num_rows($result);
if ($rows==$room_number) {
echo "room full";
}

i want the room number to be fetch from database if ($rows==5) is working.

I guess my if statement is wrong.

I would like to check if the number of rows in my database is the same with the room_number; it should echo "room full". When I use a value in place of $room_number it works, but when I use $room_number it is not working.

Upvotes: 1

Views: 1721

Answers (1)

Funk Forty Niner
Funk Forty Niner

Reputation: 74217

Just as I mentioned in comments, you would need to use MySQL's aggregate COUNT() function in order to achieve this, and to count all the rows in your table, then comparing to a given number.

$result = mysqli_query($cn,"SELECT COUNT(*) AS number FROM set_ballot");
$num = mysqli_fetch_array($result);
$count = $num["number"];
// echo("$count");

if($count == 60){
   echo "Rooms are full. ";
}
else{
   echo "Rooms are not full. ";
}

You mention that the number will be coming from user/admin input, so you could also assign a variable to it to replace "60".

$admin_number = 60;

The query would now read as:

$admin_number = 60;

$result = mysqli_query($cn,"SELECT COUNT(*) AS number FROM set_ballot");
$num = mysqli_fetch_array($result);
$count = $num["number"];
// echo("$count");

if($count == $admin_number){
   echo "Rooms are full. ";
}
else{
   echo "Rooms are not full. ";
}
  • I believe that this is what you're looking to do, without knowing what the rest of your database looks like.

Edit:

Addendum to what I mentioned in comments, and to improve on my answer.

You could add an extra column called "taken" for example and use a 0/1 flagging method, then count those in a WHERE clause along with COUNT() and using HAVING/COUNT(column).

What I think you may be presently doing is that, you're deleting rows and adding them later on, and this could prove to be inefficient and losing your data, then having to rebuild your rows after.

Here is what I mean:

Sidenote: Both $count_number and $admin_number variables need to match as an integer.

$count_number = 60; // count if the number of rows in "taken" has a 1 flag

$admin_number = 60; // the number that the admin enters

$counter = mysqli_query($cn,"SELECT COUNT(*) AS number 
                             FROM set_ballot WHERE taken = 1 
                             HAVING COUNT(taken) = $count_number");

$num = mysqli_fetch_array($counter);
$count = $num["number"];
// echo("$count");

// if($count == 60){

if($count == $admin_number){
   echo " Yep! There are $count_number rooms taken. ";
}
else{
   echo " Nope, some are available. ";
}

Sub-edit:

Actually, after thinking about it for a bit, there is a slightly better method without having to use 2 variables as I shown you above, by simply using the same variable being $count_number:

$count_number = 60;

$counter = mysqli_query($cn,"SELECT COUNT(*) AS id 
                             FROM set_ballot WHERE taken = 1 
                             HAVING COUNT(taken) = $count_number");
$num = mysqli_fetch_array($counter);
$count = $num["id"];
// echo("$count");

// if($count == 60){

if($count == $count_number){
   echo " Yep! There are $count_number rooms taken. ";
}
else{
 echo " Nope ";
}

References:

Footnotes:

There is probably another way to do this, but this is what I came up with, given my skills in MySQL.

If anyone knows of a better/more efficient method, please leave me a comment.

Upvotes: 2

Related Questions