CODEWITHSUNDEEP
phpmysqlpdosum
Foolish Coder
Foolish Coder

Reputation: 405

How to SUM rows values according to another rows which contains same values?

I am trying to get number of gusts based on a date User could be able to search number of guests for a date by entering a date

my form is: 

<form action="index.php" method="post" class="booking_reference">
    <input type="text" placeholder="date" required name="search_text">
    <span class="arrow">
        <input type="submit" value="CHECK" name="search" class="search_button">
    </span>
</form>

And my action section is :

if (isset($_POST['search'])){
$serch_text = $_POST["search_text"]; 
$con=mysqli_connect("localhost","root","","guest");
    // Check connection
    if (mysqli_connect_errno()) {
      die ("Failed to connect to MySQL: " . mysqli_connect_error());
    }       
    $searching_date = $serch_text;
    $searchroute = "select * from guest_tbl where date = '$searching_date'";
    $result = $con->query($searchroute);
    $row = $result->fetch_assoc();
    if ($row == 0) { echo '<div style="color: red;">No one found for the date: <b>' . $serch_text . '</b>. Please refine your date</div>' ; 
    } else {        
        echo '<h3>Total guest for the day : '. $serch_text .'</h3>';        
        $total_guests = mysqli_query($con, 'SELECT date SUM(nmbr_of_guests) FROM guest WHERE date = "'.$serch_text.'"'); // selecting and summing the value of all the 'nmbr_of_guests' rows in same date - $serch_text     
        echo $total_guests;
        mysqli_close($con);
    }
}

I have following rows in my database:

Database

Expecting results is:

  1. if I enter 2015-06-30 in the search field $echo $total_guests should echo 5 - (because of 2+3)
  2. if I enter 2015-06-25 in the search field $echo $total_guests should echo 6 - (because of nmbr_of_guests column have just 6 for the date)
  3. if I enter Any other date in the search field, it should echo No one found for the date: Any other date. Please refine your date.

3rd is fine I am getting that text in red line when the date is not found.

But when I enter 2015-06-30 or 2015-06-25 I am getting just blank. That means $total_guests; returns nothing / blank.

how can make it that $total_guests; show the SUM of nmbr_of_guests rows values for the date (5 or 6 in this case) ??

Note: it's an old site. I will replace all the MySQLi to PDO when I finish this. It is big file and it will take time to replace all the lines where found MySQLi.

Upvotes: 1

Views: 111

Answers (2)

Zafar Malik
Zafar Malik

Reputation: 6844

comma is missing from date and sum...query should be-

 $total_guests = mysqli_query($con, 'SELECT date, SUM(nmbr_of_guests) FROM guest WHERE date = "'.$serch_text.'"'); // selecting and summing the value of all the 'nmbr_of_guests' rows in same date - $serch_text

Update

Your code-

echo '<h3>Total guest for the day : '. $serch_text .'</h3>';        
        $total_guests = mysqli_query($con, 'SELECT date SUM(nmbr_of_guests) FROM guest WHERE date = "'.$serch_text.'"'); // selecting and summing the value of all the 'nmbr_of_guests' rows in same date - $serch_text     
        echo $total_guests;
        mysqli_close($con);

Try to check as per below-

    $total_guests = mysqli_query($con, 'SELECT SUM(nmbr_of_guests) FROM guest WHERE date = "'.$serch_text.'"'); // selecting and summing the value of all the 'nmbr_of_guests' rows in same date - $serch_text     

echo '<h3>Total guest for the date of '. $serch_text .' are '.$total_guests.'</h3>';        
    mysqli_close($con);

Upvotes: 2

Karthik Keyan
Karthik Keyan

Reputation: 426

Can you please try like this

 $total_guests = mysqli_query($con, 'SELECT date,nmbr_of_guests FROM guest_tbl WHERE date = "'.$serch_text.'"');

Assign the one variable "0" and count the value

$total = 0;
foreach ($total_guests as $guests){
$total = $total + $guests->nmbr_of_guests;
}
echo $total;

Upvotes: -1

Related Questions