Is this MySQL query valid?

Question

So I came across this when I visited a friend. He is trying to show in a table data from his created DB.

This is the part where he asks for the Start and End dates:

<form method="post">
    <input type="date" name="Date1" value="<?php echo $_POST['StartD'] ?>" style="position:absolute; top:2em; left:7em;">
    <p style="font-family:Calibri; color:green; font-size:15px; position: absolute; top: 1em; left: 18em;">to</p>
    <input type="date" name="Date2" value="<?php echo $_POST['EndD'] ?>" style="position:absolute; top:2em; left:23em;"><br />
    <input type="submit" id="Sbtn" style="cursor: pointer;" name="search_projects" value="Search">
</form>

This is now his MySQL query:

$sql= "SELECT * FROM statisticData WHERE accessDate LIKE '%" . $_POST['StartD'] . "%' OR accessDate LIKE '%" . $_POST['EndD'] . "%' ORDER BY accessDate ASC";

Whenever he chooses the Star date: 09-13-13 and an End date: 09-13-13, the dates such as 09-12-13 also appears in the table. I told him to go and ask the professionals and he says he's too shy. So I'm asking instead.

Here's his code:

<div class="scrollableTable">
<center><table class="font1" border="3px solid white" width="70%">
    <thead> 
    <tr id="tblRow">
        <th id="tblData" width="20%">Access Date</td>
        <th id="tblData" width="15%">ID number</td>
            <th id="tblData" width="35%">Name</td>
        <th id="tblData" width="20%">Keyword</td>
    </tr>
    </thead>
   <?php
  if ($_POST['search_projects']){

  $con= mysql_connect("...","...","...") or die ('Error: ' . mysql_error()); 
   mysql_select_db("...");
   $sql= "SELECT * FROM statisticData WHERE accessDate LIKE '%" . $_POST['StartD'] . "%' AND  accessDate LIKE '%" . $_POST['EndD'] . "%' ORDER BY accessDate DESC";
   $result= mysql_query($sql); 

    while($row= mysql_fetch_object($result))
    { 
    $Date=$row->accessDate; 
    $ID=$row->IDnum;
    $Name=$row->Sname; 
            $Key=$row->keyWord;

    echo "<tr>"; 
    echo "<td>" . $Date.  " </td>" . " <td>" . $ID.  " </td>" . " <td>" . $Name.  " </td>". " <td>" . $Key.  " </td>"; 
    echo "</tr>"; 

    }
    echo "</table>";
    echo "</center>";
    }
 ?>
 </div>

He wants to allow the user to browse the contents of the database using these date inputs.

start and end date input

The problem is even when I choose the start and end dates, all the contents are shown in the table. His query does not filter the results at all. I told him to sleep it off, but he just keeps bugging me. So what do you think should he do to finally get some sleep?

[how to get date between two dates in mysql?](http://stackoverflow.com/a/11827201/342740) and [Check if the current date is between two dates?](http://stackoverflow.com/a/13563494/342740) and [MySQL: query between two dates?](http://stackoverflow.com/questions/3822648/mysql-query-between-two-dates) — Prix, Sep 27 '13 at 07:50
Don't concatenate strings into your SQL, use parametized queries instead to avoid SQL injection — xlecoustillier, Sep 27 '13 at 07:52
Just tell your friend that, none is born pro, I read questions more than I answer, also ask if i can't figure it out and that's why i'm learning everyday and getting better than good every day, so your friend is harming to himself, `IMO` and yes I'm not that mush of an expert according to my reps, learning never ends. What i've learned from `1995-2008`, StackOverflow taught me more than that in last two years, so learn how to learn. — The Alpha, Sep 27 '13 at 07:55
@SheikhHeera nicely said, SO/SE is a powerful tool if used right. — Prix, Sep 27 '13 at 07:57
@Prix, A perfect place to learn, experts from all over the world. — The Alpha, Sep 27 '13 at 07:58
I'll be sure to tell him about everyone's suggested revisions and also about what you said @SheikhHeera. — , Sep 27 '13 at 08:07

score 1 · Accepted Answer · 2013-09-30T14:12:31.187

1

People problem is in names:

$sql= "SELECT * FROM statisticData WHERE accessDate LIKE '%" . $_POST['Date1'] . "%' OR accessDate LIKE '%" . $_POST['Date2'] . "%' ORDER BY accessDate ASC";

also:

<form method="post">
<input type="date" name="Date1" value="<?php echo $_POST['Date1'] ?>" style="position:absolute; top:2em; left:7em;">
<p style="font-family:Calibri; color:green; font-size:15px; position: absolute; top: 1em; left: 18em;">to</p>
<input type="date" name="Date2" value="<?php echo $_POST['Date2'] ?>" style="position:absolute; top:2em; left:23em;"><br />
<input type="submit" id="Sbtn" style="cursor: pointer;" name="search_projects" value="Search">
</form>

Update:

We have to convert time for MySQL
And use BETWEEN statement

<?php
    if ($_POST['search_projects']){

    $startDate = date("Y-m-d", strtotime($_POST['Date1']));
    $endDate   = date("Y-m-d", strtotime($_POST['Date2']));

    $con= mysql_connect("...","...","...") or die ('Error: ' . mysql_error()); 
    mysql_select_db("...");
    $sql= "SELECT * FROM statisticData WHERE accessDate BETWEEN '$startDate' AND '$endDate' ORDER BY accessDate DESC";
    $result= mysql_query($sql); 
    ...

edited Sep 30 '13 at 14:12

answered Sep 27 '13 at 07:52

Just called my friend and told him to try your suggestions and corrections. He said nothing appears after changing the query and the ones in
. I told him there may be something wrong with his table. Could it be the table? @GeorgePHP
– Sep 27 '13 at 08:41
I think problem is in query and maybe also in table, anyway rest is ok from this view. Just can you also post table structure? and the real goal your friend's trying to achieve – Sep 27 '13 at 11:15
Hello again, I just added the information you asked for. Thank you for the help @GeorgePHP. Really appreciate it. – Sep 30 '13 at 13:13
Wow, that was fast. I'll call Mat NOW. He was online just a minute ago. Probably fell asleep in front of the computer again. – Sep 30 '13 at 14:21
Haha you guys seem too funny and wonderful feeling of humor, cheers ;) Let me wake you up anytime – Sep 30 '13 at 14:24
He's not answering, probably snoring away. I'll be sure to show this to him. Thank you for that quick response @GeorgePHP. – Sep 30 '13 at 14:33
OK. no prob. Anyway if it does not fix the problem. Mail me to george.garcha at gmail. With files and, if you can export table from MySQL would hasten time needed... – Sep 30 '13 at 14:53
It's me Mat, d sleepless dude. So any-who, d table is now working properly. Thanks much @GeorgePHP. When I tried it out, I accidentally punched LJ's arm due to d joy of finally seeing d results I've been trying to get. She's a girl so deffo not good. I seriously need to have my own SO account so I won't be ask'n her to ask the pros. for me. Thanks 'gain. – Oct 01 '13 at 06:15
Ouch, that's too much English for me, for today. But anyway I'm glad to help you. Call me anytime... – Oct 01 '13 at 06:24

score 0 · Answer 2 · edited May 23 '17 at 12:30

0

Use BETWEEN.

$sql= "
    SELECT * 
    FROM statisticData 
    WHERE accessDate BETWEEN '{$_POST['StartD']}' AND '{$_POST['EndD']}
    ORDER BY accessDate ASC
";

And please read How can I prevent SQL injection in PHP?

edited May 23 '17 at 12:30

Community

1
1

answered Sep 27 '13 at 07:51

Glavić

42,781
13
77
107

'sup @Glavić, it's the dude with the wrong query and all. I changed my query with yours and I get this error. Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in /home/.../public_html/SysUsers.php on line 60 What d'you think the prob is here? Thanks dude... – Sep 30 '13 at 13:30
@jr.greenDefender: then you probably have sql error. Run mysql_query like `$result = mysql_query($sql) or die(mysql_error()); ` – Glavić Sep 30 '13 at 13:46

score 0 · Answer 3 · answered Sep 27 '13 at 07:52

0

it looks good Try to use this one

$sql= "SELECT * FROM statisticData WHERE accessDate LIKE '" . $_POST['StartD'] . "' OR accessDate LIKE '" . $_POST['EndD'] . "' ORDER BY accessDate ASC";

OR use

$sql= "SELECT * FROM statisticData WHERE accessDate BETWEEN '" . $_POST['StartD'] . "' AND '" . $_POST['EndD'] . "' ORDER BY accessDate ASC";

answered Sep 27 '13 at 07:52

Mubin

4,325
5
33
55

What is the difference? – Yogesh Suthar Sep 27 '13 at 07:53
removed %% from both sides of date Or you can also use 2nd method too – Mubin Sep 27 '13 at 07:55

score 0 · Answer 4 · answered Sep 27 '13 at 07:53

0

used this

$sql= "SELECT *
FROM `statisticData`
WHERE (accessDate BETWEEN '".$_POST['StartD']."' AND '" . $_POST['EndD'] . "') ORDER BY accessDate ASC";

answered Sep 27 '13 at 07:53

Shakti Patel

3,762
4
22
29

score 0 · Answer 5 · answered Sep 27 '13 at 07:54

In my opinion it is a bad practice for three reasons:

1) You have an SQL-injection vulnerability here - you must escape your input

2) If you use datetime format in your DB, but want to select by date without time - you should convert type with functions like DATE()

3) Using LIKE statement is much more slower than exact comparison, which is for your needs is BETWEEN statement. And LIKE is mostly for just string-like data types

score 0 · Answer 6 · answered Sep 27 '13 at 07:55

is the data type of accessDate column a datetime, or is it a string? if it is datetime then try the following:

'SELECT * FROM `statisticData`
 WHERE  (accessDate BETWEEN ' . $_POST['StartD'] . ' AND ' . $_POST['EndD']) .
'ORDER BY accessDate ASC'

it should work, i'm now sql guru

Is this MySQL query valid?

6 Answers6