how to insert date using php into mysql query?

Question

what i'm trying to do is get date from html input, execute a query using that date. but i can't seem to figure out what is the problem, i'm doing everything right (or am i?)

Here is some code index.php

<head>
<script>
function showReport(str) {

        var xmlhttp = new XMLHttpRequest();
        xmlhttp.onreadystatechange = function() {
            if (this.readyState == 4 && this.status == 200) {
                document.getElementById("report").innerHTML = this.responseText;
            }
        };
        xmlhttp.open("GET", "/reports.php?q=" + str, true);
        xmlhttp.send();
    }

</script>
</head>
<body>

<p><b>Start typing a name in the input field below:</b></p>
<form> 
Select Date: <input type="date" onchange="showReport(this.value)">
</form>
<p>Results: <span id="report"></span></p>
</body>
</html>

here is the ajax handler reports.php


// get the q parameter from URL


$servername = "localhost";
$username = "root";
$password = "";
$dbname = "chaska";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
$input_date=$_REQUEST['q'];
$date=date("Y-m-d",strtotime($input_date));
$sql = "SELECT `tec_sale_items`.product_name, `tec_sales`.date, sum(`tec_sale_items`.quantity) AS sum FROM `tec_sale_items` LEFT JOIN `tec_sales` ON `tec_sale_items`.sale_id = `tec_sales`.id WHERE DATE(`tec_sales`.date) = $date AS DATE group by `tec_sale_items`.product_name, DATE(`tec_sales`.date)";
$result = $conn->query($sql);
echo $date;
if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "product: " . $row["product_name"]. " - Quantity: " . $row["sum"]. " ". "<br>";
    }
} else {
    echo "0 results";
}
$conn->close();
?>

The query returns nothing. When I replace $date AS DATE by CURRENT_DATEthe query executes fine but I want a specific date to work as well

score 2 · Answer 1 · answered Jul 23 '19 at 10:12

The following is illegal SQL syntax for two reasons; it's missing quotes around the $date variable (which is a string), and you try to give it an alias (all you're doing is comparing two values, so aliasing makes little sense here).

WHERE DATE(`tec_sales`.date) = $date AS DATE

You should also be using a prepared statement with MySQLi, as shown below. Using a prepared statement means that you no longer need to worry about quoting your variables.

<?php 
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "chaska";

// Create connection
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
$input_date = $_REQUEST['q'];
$date = date("Y-m-d",strtotime($input_date));
$sql = "SELECT `tec_sale_items`.product_name, 
               `tec_sales`.date, 
               sum(`tec_sale_items`.quantity) AS sum 
        FROM `tec_sale_items` 
        LEFT JOIN `tec_sales` 
            ON `tec_sale_items`.sale_id = `tec_sales`.id 
        WHERE DATE(`tec_sales`.date) = ?
        GROUP BY `tec_sale_items`.product_name, DATE(`tec_sales`.date)";
$stmt = $conn->prepare($sql);
$stmt->bind_param("s", $date);
$stmt->execute();
$stmt->bind_result($product_name, $date, $sum);
if ($stmt->fetch()) {
    do {
        echo "product: ".$product_name. " - Quantity: " . $sum. " <br>";
    } while ($stmt->fetch());
} else {
    echo "0 results";
}
$stmt->close();

score 1 · Accepted Answer · answered Jul 23 '19 at 10:08

1

WHERE DATE(`tec_sales`.date) = $date

Use single quotes around date value, otherwise it is evaluated as arithmetic operation - 2019-07-23 = 2012 - 23 = 1989.

Correct condition:

WHERE DATE(`tec_sales`.date) = '$date'

There is no risk of sql injection, because input value is parsed by strtotime.

answered Jul 23 '19 at 10:08

Naktibalda

13,705
5
35
51

Just because this value is safe, it does not mean you can inject it into SQL query. You should aim to use prepared statements 100% of the time. – Dharman Jul 23 '19 at 12:21

Brindha Baskaran · Answer 3 · 2019-07-23T10:13:26.563

0

what you did is right

Select Date: <input type="text" onchange="showReport(this.value)" >

but use text instead of date

edited Jul 23 '19 at 10:13

answered Jul 23 '19 at 10:08

Brindha Baskaran

185
2
16

how to insert date using php into mysql query?

3 Answers3