PHP return empty value from Mysql Query

Question

I use the same query to return one integer value between two dates which is a sum of column

SQL Query:

SELECT SUM(TransactionAmount) FROM `sales` WHERE TransactionTime BETWEEN '2020-12-15' AND '2020-12-16'

In this PHP Code:

<?php
require 'config/db.php';

if(isset($_POST["searchinDate-btn"])){
$start_date = $_POST['startdate'];
$end_date = $_POST['enddate'];

$totalRev = "SELECT SUM(TransactionAmount) FROM `sales` WHERE TransactionTime BETWEEN '$start_date' AND '$end_date'";

$totalRevQ = mysqli_query($conn, $totalRev);

    $rev = mysqli_fetch_assoc($totalRevQ);

      $revenue = $rev[0]; 

 }

?>

(Additional) HTML Code:

<form action="Dashboard.php" method="POST">
      <div class="dateselect"> 
        <label for="start-date">Start Date: </label>
          <input type="date" id="start-date" name="startdate" value="">
        <label for="start-date"> - - End Date: </label>
          <input type="date" id="end-date" name="enddate" value="">
          <button  style="margin-bottom: 3px;" type="submit" name="searchinDate-btn" class="mbtn btn btn-primary">Show</button>  
      </div>
</form>
<p> <?php echo $revenue; ?> </p>

The Variable revenue return nothing for some reason! Note: Both start date and end date and the query result actually return results but I think there is a problem with showing the result of the query which will be one integer value like 562 or something else! ( I need the value of the row number 0 not the count of the rows)

UPDATE: The wrong was in passing date type from PHP to compare it with timestamp field type at the database.

to convert date to time stamp I use this PHP Code:

<?php
    require 'config/db.php';
    
    if(isset($_POST["searchinDate-btn"])){
    $start_date = $_POST['startdate'];
    $StartDate = date("Y-m-d H:i:s", strtotime($start_date));
    $end_date = $_POST['enddate'];
    $EndDate = date("Y-m-d H:i:s", strtotime($end_date + '24 hours'));
    
    $totalRev = "SELECT SUM(TransactionAmount) AS Revenue FROM `sales` WHERE TransactionTime BETWEEN '$StartDate' AND '$EndDate'";
    
    $totalRevQ = mysqli_query($conn, $totalRev);
    
    $rev = mysqli_fetch_assoc($totalRevQ);
    
    $revenue = $rev['Revenue'];
    
    }
    ?>

As an aside, see about sql injection and the importance of prepared and bound queries — Strawberry, Jan 17 '21 at 00:35
I use such type of security in authuncation controller, if there is no established session by login or registration it will refuse to authuncate also I use bind_param for both login and registration form is that enhough ? — Hussein, Jan 17 '21 at 09:38
I use this: if(!isset($_SESSION['id'])){ header('location: Login.php'); exit(); } — Hussein, Jan 17 '21 at 15:40
@Dharman no this return a count of rows number, I need the value of the row number 0. — Hussein, Jan 17 '21 at 15:42
It's the same principle. You need to use the correct function or use an SQL alias. Please read my answer carefully — Dharman, Jan 17 '21 at 15:42
The wrong was in passing date type from PHP to compare it with timestamp field type at the database, thank you. — Hussein, Jan 17 '21 at 22:22

score 0 · Answer 1 · answered Jan 17 '21 at 00:48

0

Maybe adding an alias to the sum in the query:

$totalRev = "SELECT SUM(TransactionAmount) AS totalAmount FROM `sales` WHERE TransactionTime BETWEEN '$start_date' AND '$end_date'";

Then changing $rev = mysqli_fetch_assoc($totalRevQ) to $rev = mysqli_fetch_array($totalRevQ), in order to get the value with $revenue = $rev["totalAmount"] :D

Also, as Strawberry said, don't forget to protect that query from SQL injection, it's never good to trust user input!

answered Jan 17 '21 at 00:48

Luis Lezama

11
1

Unfortunately this not fix my problem I use your solution before you answer but it does not works, it is kind of weird problem ! – Hussein Jan 17 '21 at 15:44
@Hussein Run the SQL in the console or something like phpmyadmin and see the output. – Luis Lezama Jan 17 '21 at 20:46
The wrong was in passing date type from PHP to compare it with timestamp field type at the database, thank you. – Hussein Jan 17 '21 at 22:22

score 0 · Accepted Answer · answered Jan 17 '21 at 16:28

Because you used mysqli_fetch_assoc function you need to set a name for the result (I this name to "AmountSum" in my example):

<?php
require 'config/db.php';

if(isset($_POST["searchinDate-btn"])){
$start_date = $_POST['startdate'];
$end_date = $_POST['enddate'];

$totalRev = "SELECT SUM(TransactionAmount) AS AmountSum FROM `sales` WHERE TransactionTime BETWEEN '$start_date' AND '$end_date'";

$totalRevQ = mysqli_query($conn, $totalRev);

$rev = mysqli_fetch_assoc($totalRevQ);

$revenue = $rev['AmountSum'];

}
?>

The wrong was in passing date type from PHP to compare it with timestamp field type at the database, thank you. — Hussein, Jan 17 '21 at 22:22

PHP return empty value from Mysql Query

2 Answers2