using COUNT() in PHP

Question

I am working on "Bus Reservation Website" that allowed the user to book trips from city to another. I have 128 rout every day. Each rout bus capacity is 44 seat. So i want that when the user click on confirm booking the system will calculate the seats booked on the same day the user selected for the same routs. so if it fully booked message

Sorry no seat available

will be displayed. else the booking will be confirm and number of seats left will be displayed too.

I generated this table in order to count the seats each day and rout: Date table . and my booking table is Booking. Any how, i tried this code to calculate the seat number but it didn't work:

<?php 
@session_start();
$username = "root";
$password = "";
$server="localhost";
$database = "Mydatabase";
$table = "booking";


@mysql_connect($server,$username,$password);
mysql_select_db($database);

if(isset($_POST['ok']))
{
  //values from my html page
$dept = $_POST['tdep'];
$arr = $_POST['tarr'];
$date = $_POST['ddate'];
$tcode=$_POST['tcode'];
$userid = $_SESSION['login'];
$num =$_POST['num'];
$confirmation = uniqid();


//booking details that will be stored in booking table
$sql = "INSERT INTO $table VALUES('$confirmation','$tcode', '$date', '$num', '$userid', '$price', '$dept','$arr' )";
$query=mysql_query($sql);

//date,rout and number of seats will be inserted to date table
$sql = "INSERT INTO date (tcode,sno,date) VALUES('$tcode', '$num','$date' )";
$query=mysql_query($sql);


//calculating number of seats 
$sqld="SELECT COUNT(sno) FROM date WHERE date='$date' AND tcode='$tcode'";
$queryd=mysql_query($sqld);
if ($queryd>44)
 echo "No Available Seats left";
else
 echo "seats Available= ".$queryd;


//decarsing number of seats in routs table
$sql3 = "UPDATE long_dis SET seat=seat-'$num' WHERE Tcode='$tcode'";
$query3=mysql_query($sql3);


//confirmation message
echo "<h3><font color='red'>Booking is successfully Confirmed for national id :".$userid."</font></h3>";
echo "<h4><font color='blue'>The total cost=  ".$price." OMR </br>And your confirmation code is: ". $confirmation."</font></h4>";
  } 
?>

Thank you for your help in advance,

First, define "it didn't work", it's kind of vague. Second: I hope this is just a project you are working on to learn, if it's something that you really plan to use you should read the big notice on red background on the manual page for functions like mysql_query, telling you not to use them. Third: you should really ready about escaping if you'll stick with this path. Fourth: why are you counting the existing rows `after` you already insert the new one? — mishu, Mar 25 '16 at 09:08
It seems that you `really` have to read the manual page about the mysql_query function (http://php.net/mysql_query) because aside from the big notice, you should also see what's the return value for this function. — mishu, Mar 25 '16 at 09:11
thank you for your comment, Actually i am just learning. i had read so many about it and still didn't get the code. i replaced the counting to before inserting the new one so thank you for that doubt. — RAKH, Mar 25 '16 at 09:16

score 2 · Accepted Answer · edited May 23 '17 at 11:45

$queryd=mysql_query($sqld);
if ($queryd>44)

Find problem there :) Read this

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

It returns a resource, not the number of rows. Find the proper function that returns the number of rows in a result.

While in that quest, also read this How can I prevent SQL injection in PHP?

score 0 · Answer 2 · answered Mar 25 '16 at 09:36

Firstly you have to fetch the data.

and secondly your if condition of greater than 44 will not work since total of seats that can be booked is 44 your total cannot be greater than 44. hence check for greater than equal to 44.

//calculating number of seats 
$sqld="SELECT COUNT(sno) as seatAvialable FROM date WHERE date='$date' AND tcode='$tcode'";
$queryd=mysql_query($sqld);
$resultd=mysql_fetch_assoc($queryd);
if ($resultd['seatAvialable']>=44)
    echo "No Available Seats left";
else
    echo "seats Available= ".$queryd;

score 0 · Answer 3 · answered Mar 26 '16 at 19:34

I actually found that that i should use SUM() not COUNT(). Small codes means big deal!

anyhow, i will list my answer in case anyone would like to benefit.

<?php
    $result = mysql_query("SELECT SUM(sno) AS value_sum FROM $table WHERE date='$date' AND tcode='$tcode'"); 
    $row = mysql_fetch_assoc($result); 
    $sum = $row['value_sum'];



    if ($sum<44)
    {
    echo "<h3><font color='red'>Booking is successfully Confirmed !!</font>";

    }

    else if($sum>=44)

    echo "<h3><font color='red'>SORRY,".$sum." of 44 seat been already booked for the selected date and Rout!!</font></h3>"; 

?>

using COUNT() in PHP

3 Answers3