Reputation: 93

Ajax PHP query to retrieve data based on date

I'm trying to retrieve data through drop-down using Ajax & PHP. Select option values work fine when paired with database column name but when I change it to be date specific, it doesn't work. Please go through my code below & suggest the changes required.

HTML:

<form>
<select name="users" onchange="showUser(this.value)">
  <option value="">Select a person:</option>
  <option value="1">July</option> // I doesn't work if when I change the value to "\'2015-07-01\' AND \'2015-07-31\'"
  <option value="2">August</option> // I doesn't work if when I change the value to "\'2015-08-01\' AND \'2015-08-31\'"
  <option value="3">September</option> // I doesn't work if when I change the value to "\'2015-09-01\' AND \'2015-09-30\'"
  <option value="4">October</option> // I doesn't work if when I change the value to "\'2015-10-01\' AND \'2015-10-31\'"
  </select>
</form>
<br>
<div id="txtHint"><b>Person info will be listed here...</b></div>

Javascript

function showUser(str) {
    if (str == "") {
        document.getElementById("txtHint").innerHTML = "";
        return;
    } else { 
        if (window.XMLHttpRequest) {
            // code for IE7+, Firefox, Chrome, Opera, Safari
            xmlhttp = new XMLHttpRequest();
        } else {
            // code for IE6, IE5
            xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
        }
        xmlhttp.onreadystatechange = function() {
            if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
                document.getElementById("txtHint").innerHTML = xmlhttp.responseText;
            }
        }
        xmlhttp.open("GET","getleads.php?q="+str,true);
        xmlhttp.send();
    }
}

PHP:

<?php
$q = intval($_GET['q']);

/*Comments Start, I even tried if else with the select values being only numeric 
if($q==1)
{
$q == "\'2015-07-01\' AND \'2015-07-31\'";
} 
elseif ($q==2)
{
$q == "\'2015-08-01\' AND \'2015-08-31\'";
} 
elseif ($q==3)
{
$q == "\'2015-09-01\' AND \'2015-09-30\'";
}
} elseif ($q==4){
$q == "\'2015-10-01\' AND \'2015-10-31\'";
} else ($q== )
{
$q == "*";
}    
Comments End*/

$con = mysqli_connect('localhost','user','password','database');
if (!$con) {
    die('Could not connect: ' . mysqli_error($con));
}

mysqli_select_db($con,"ajax_demo");
$sql="SELECT * FROM table WHERE ID = '".$q."'";
$result = mysqli_query($con,$sql);

echo "<table>
<tr>
<th>Name</th>
<th>Number</th>
<th>Email</th>


</tr>";
while($row = mysqli_fetch_array($result)) {
    echo "<tr>";
    echo "<td>" . $row['frm_name'] . "</td>";
    echo "<td>" . $row['frm_mobile'] . "</td>";
    echo "<td>" . $row['frm_email'] . "</td>";

    echo "</tr>";
}
echo "</table>";
mysqli_close($con);
?>

I even tried by removing the back slashes from the dates. Please help !!

Upvotes: 0

Answers (2)

PRANAV

Reputation: 629

i have tried the same code there is issue in your select query please try this one.. and debug..

first import this table 

    CREATE TABLE IF NOT EXISTS `fromtable` (
      `id` int(15) NOT NULL,
      `frm_name` varchar(25) NOT NULL,
      `frm_mobile` varchar(25) NOT NULL,
      `frm_email` varchar(25) NOT NULL
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

    --
    -- Dumping data for table `fromtable`
    --

    INSERT INTO `fromtable` (`id`, `frm_name`, `frm_mobile`, `frm_email`)   
    VALUES
    (1, 'abc', '987654120', '[email protected]'),
    (2, 'pqr', '985472160', '[email protected]');

keep html page as it is

getleads.php

         <!DOCTYPE html>
         <html>
         <head>
         <style>
         table {
          width: 100%;
          border-collapse: collapse;
         }
        table, td, th {
        border: 1px solid black;
        padding: 5px;
        }

        th {text-align: left;}
        </style>
        </head>
        <body>

       <?php
        $q = intval($_GET['q']);
        $con = mysqli_connect('localhost','root','','dbname');
        if (!$con) {
           die('Could not connect: ' . mysqli_error($con));
        }

         mysqli_select_db($con,"ajax_demo");
         $sql="SELECT * FROM fromtable WHERE id = '".$q."'";
        //echo $sql;exit;
         $result = mysqli_query($con,$sql);
        $r = mysqli_query($con,$sql)
        or die("Error: ".mysqli_error($con));

          echo "<table>
         <tr>
         <th>Name</th>
         <th>Number</th>
         <th>Email</th>
         </tr>";
          while($row = mysqli_fetch_array($result,MYSQLI_ASSOC)) {
           echo "<tr>";
           echo "<td>" . $row['frm_name'] . "</td>";
            echo "<td>" . $row['frm_mobile'] . "</td>";
           echo "<td>" . $row['frm_email'] . "</td>";
            echo "</tr>";
          }
        echo "</table>";
        mysqli_close($con);
       ?>
    </body>
    </html>

Upvotes: 1

jeroen

Reputation: 91734

You should not inject variables in your sql queries; now it doesn't really matter as you hard-code the strings on the server-side, but if you change your format to accept values from the form, you would open up your query to sql injection.

You should use a prepared statement instead; then you don't need any quotes and if you change anything in the future, you will not be adding any bugs by accident.

Apart from that, your resulting sql is wrong, check out what happens when you uncomment your string assignments:

SELECT * FROM table WHERE ID = ''2015-07-01' AND '2015-07-31''

Both the quotes are wrong and the AND syntax is wrong. And that is assuming that table is not your real table name.

You probably want something like:

SELECT * FROM your_table WHERE your_date_field BETWEEN ? AND ?

Where you could bind any valid formatted date to the question marks.

Note that mysql also has a MONTH() function that would make your query a lot easier in this specific case.

Upvotes: 1

Ajax PHP query to retrieve data based on date

Answers (2)

Related Questions