Query database based on url ($_GET[]), not working as hoped

Question

im trying to apply filters to my query based on the pages URL. However it doesn't seem to be returning the correct data from my database.

    if ( $_GET['from'] == '' && $_GET['to'] == '' ) {
    $query = 'SELECT * FROM mhs_dashboard_revenue';
    echo 'none';
} else if ( $_GET['from'] == '' && $_GET['to'] != '' ) {
    $query = 'SELECT * FROM mhs_dashboard_revenue WHERE DateOfData BETWEEN 20150101 AND "$_GET[to]"';
    echo 'to';
} else if ( $_GET['from'] != '' && $_GET['to'] == '' ) {
    $query = 'SELECT * FROM mhs_dashboard_revenue WHERE DateOfData BETWEEN "$_GET[from]" AND 20900101';
    echo 'from';
} else if ( $_GET['from'] != '' && $_GET['to'] != '' ) {
    $query = 'SELECT * FROM mhs_dashboard_revenue WHERE DateOfData BETWEEN "$_GET[from]" AND "$_GET[to]"';
    echo 'both';
}

$result = mysqli_query($connection, $query);

$totalChampagne = 0;
$totalSpirts = 0;
$totalWine = 0;
$total = 0;

while($row = mysqli_fetch_assoc($result)) { ?>

<?php 

    $totalChampagne = $totalChampagne + $row['ChampagneValue']; 
    $totalSpirts = $totalSpirts + $row['SpirtsValue']; 
    $totalWine = $totalWine + $row['WineValue']; 
    $total =  $totalChampagne + $totalSpirts + $totalWine; 

?>

<!--
    <p>Id: <?php //echo $row['ID']; ?></p>
    <p>Date of: <?php //echo $row['DateOfData']; ?></p>
    <p>Champagne: <?php //echo $row['ChampagneValue']; ?></p>
    <p>Spirts: <?php //echo $row['SpirtsValue']; ?></p>
    <p>Wine: <?php //echo $row['WineValue']; ?></p>
    <hr>
-->

<?php } ?>

<p>Champagne Total = &pound;<?php echo $totalChampagne; ?></p>
<p>Spirts Total = &pound;<?php echo $totalSpirts; ?></p>
<p>Wine Total = &pound;<?php echo $totalWine; ?></p>
<p>Total = &pound;<?php echo $total; ?></p>

I've added echo's in each IF to make sure my values are being received and I can see the echo's in the right place based on the url.

The URL looks like - revenue_feed.php?from=20160101&to=20161115, which is a date formatted without the hyphens as when I tested it manually inputting the date hyphens broke it.

I hope it is a simple fix however I can't seem to work it out. All help is greatly appreciated.

Answer 1

For PHP variables to be interpreted, you need to put them inside double quotes (not single quotes as you did for the SQL queries). But in any case, your code is open to SQL injections and should be avoided.

Instead you could use prepared queries, which would give you something like this:

$sql = 'SELECT * FROM mhs_dashboard_revenue WHERE DateOfData BETWEEN ? AND 20900101';

$stmt = mysqli_prepare($connection, $sql);
mysqli_stmt_bind_param($stmt, "s", $_GET[from]);
mysqli_stmt_execute($stmt);