Somya Arora Somya Arora - 4 months ago 9
MySQL Question

Query returns empty result in PHP,but working fine otherwise

Here is the sql query ,which is working fine when tested on phpmyadmin.
Below is the screenshot.

SQL query working on phpmyadmin

Below is the PHP code and the HTML form for it

<?php

header('Content-type=application/json;charset=utf-8');

include("connection.php");
session_start();
if($_SERVER["REQUEST_METHOD"] == "POST")
{

$event_date1= mysqli_real_escape_string($con,$_POST['event_date1']);
$event_date2= mysqli_real_escape_string($con,$_POST['event_date2']);



$query = ("SELECT topic1 as topic from feedbackform_db where topic1 is not null AND event_date between $event_date1 AND $event_date2
union SELECT topic2 as topic from feedbackform_db where topic2 is not null AND event_date between $event_date1 AND $event_date2
union SELECT topic3 as topic from feedbackform_db where topic3 is not null AND event_date between $event_date1 AND $event_date2
union SELECT topic4 as topic from feedbackform_db where topic4 is not null AND event_date between $event_date1 AND $event_date2
union SELECT topic5 as topic from feedbackform_db where topic5 is not null AND event_date between $event_date1 AND $event_date2
union SELECT topic6 as topic from feedbackform_db where topic6 is not null AND event_date between $event_date1 AND $event_date2
union SELECT topic7 as topic from feedbackform_db where topic7 is not null AND event_date between $event_date1 AND $event_date2
union SELECT topic8 as topic from feedbackform_db where topic8 is not null AND event_date between $event_date1 AND $event_date2 ");



$response=mysqli_query($con,$query);

if($response)
echo "true";
else
echo "false";


// set array
$array = array();

// look through query
while($row = mysqli_fetch_assoc($response))
{

// add each row returned into an array
$array[] = $row;

// OR just echo the data:
echo $row['topic']; // etc
}

$data['details']=$array;
echo json_encode($data);

print_r($array);
// echo json_encode($array);
}

mysqli_close($con);

?>



<html>
<head>
<title>client welcome Page</title>

<style type = "text/css">

body \{

font-family:Arial, Helvetica, sans-serif;

font-size:14px;

\}

label \{

font-weight:bold;

width:100px;

font-size:14px;

\}



.box \{

border:#666666 solid 1px;

\}

</style>



</head>



<body bgcolor = "#FFFFFF">



<div align = "center">

<div style = "width:300px; border: solid 1px #333333; " align = "left">

<div style = "background-color:#333333; color:#FFFFFF; padding:3px;"><b>Login</b></div>



<div style = "margin:30px">



<form action = "" method = "post">


<label>event_date1:</label><input type = "text" name = "event_date1" class = "box" /><br/><br />
<label>event_date2:</label><input type = "text" name = "event_date2" class = "box" /><br/><br />






<input type = "submit" value = " Submit "/><br />

</form>



<div style = "font-size:11px; color:#cc0000; margin-top:10px"></div>



</div>



</div>



</div>


</body>

</html>


When I am running this on localhost.Result is empty and no error.
But the query is running as its showing "true" for response variable

This is the result

true{"details":[]}Array ( )

Why I am getting no values in array ?

Answer

Your php code cannot possible duplicate what you have in phpmyadmin: the escape() call does NOT quote values for you, so you're generating the equivalent of

SELECT ... event_date between 2016-06-03 and 2016-06-08

note the lack of quotes around your dates. That means they're NOT dates as far as MySQL is concerned, they're mathematical subtraction operations, and you're doing the equivalent of:

SELECT ... event_date between 2007 and 2002

Since that's an impossible condition to fulfill (between must be smaller AND larger, and you have larger AND smaller), you get 0 rows, you still get a VALID result handle, and end up with an empty array.

Your should have

 $query = ("SELECT [...snip..] between '$event_date1' AND '$event_date2'

(note the ' quotes) for every one of your member select queries.

Comments