PHP to search SQL DB beween dates

Question

I have a SQL DB where I store information according to dates and times.

The format the SQL stores in is 2013-04-25 15:13:37

The column name is date_time and is Var

My seach PHP is as follow:

<form action="control_lbsresult.php" method="get">
                <input type="hidden" name="member_msisdn" value="<?=$query?>" 
/>
                <input type="text" name="query" />
                <br />
                <label for="dates"></label>
                <input type="date" name="dates" id="dates" />
                 Start Date<br />
                <label for="datee"></label>
                <input type="date" name="datee" id="datee" />
                 End Date<br />
                <input type="submit" value="Search" />
            </form>

This is the string what it gives in URL

/control_lbsresult.php?member_msisdn=&query=0827910119&dates=2013-04-01&datee=2013-04-
25

This is what i have in my result page:

<?php
$host="localhost"; // Host name 
$username="***"; // Mysql username 
$password="****"; // Mysql password 
$db_name="****"; // Database name 
$tbl_name="lbs_"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// get value of id that sent from address bar
$id=$_GET['id'];

// Retrieve data from database 
$sql="SELECT * FROM $tbl_name WHERE date between  '" . $dates . "' AND '" . $datee . "'    
msisdn='$msisdn'";

It is not filtering between the dates for me and gives me all the results of the MSISDN

I think the problem is in the WHERE string please assist

If your dates are stored as strings mysql won't treat them as dates. You should change that data type to datetime. — John Conde
– John Conde, Commented Apr 25, 2013 at 15:46
Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. — Kermit
– Kermit, Commented Apr 25, 2013 at 15:46
$dates and $datee aren't defined anywhere in your code, and the msisdn part will cause a syntax error in the SQl as well. — Marc B
– Marc B, Commented Apr 25, 2013 at 15:51
Please read up on SQL escaping before you do any more SQL programming. What you're doing here is reckless and could cost you severely. — tadman
– tadman, Commented Apr 25, 2013 at 16:05

John Conde · Accepted Answer · 2013-04-25 15:45:16Z

1

Using BETWEEN with dates can get you unexpected rsults. Try this instead:

SELECT * 
FROM $tbl_name 
WHERE date >=  '" . $dates . "' 
AND date <= '" . $datee . "'

answered Apr 25, 2013 at 15:45

John Conde

220k100 gold badges464 silver badges504 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Kermit Over a year ago

Unlikely OP will get the results they want with a string.

Mike Brant Over a year ago

BETWEEN works totally fine with dates if the value is stored in a proper date, datetime, or timestamp field.

tadman Over a year ago

What do you mean "unexpected results"? BETWEEN is almost always better than x >= y AND x <= z.

Maggie Ackermann Over a year ago

"SELECT * FROM $tbl_name WHERE date >= '" . $dates . "' AND date <= '" . $datee . "' msisdn='$msisdn' I am not defining $dates or $datee as Marc B is saying but how do i draw it from the URL string. It shows me all results still

Maggie Ackermann Over a year ago

Ok here is what I got to work ("SELECT * FROM lbs_ WHERE date >= '" . $dates . "' AND date <= '" . $datee . "' AND msisdn='$query'"); I have changed code saved tested and then discovered I was using the wrong PHP page and not altering the one I was linking to. Thanks all and excuse my tiredness late at night not thinking straight

Sumoanand · Accepted Answer · 2013-04-25 16:13:05Z

1

Use BETWEEN or >= & <=, both should work.

You need to make sure that field type is DATE.

Also, i would suggest to use mysqli.

Following is the sample code for you:

$stmt = $db->prepare("SELECT * FROM tbl_name WHERE date_field between ? AND ?");

$stmt->bind_param("ss",$start_date,$end_date);

$stmt->execute();

edited Apr 25, 2013 at 16:13

answered Apr 25, 2013 at 15:59

Sumoanand

8,9293 gold badges50 silver badges46 bronze badges

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

0

Change the type of your column.

ALTER TABLE tbl MODIFY COLUMN date datetime;
Use BETWEEN.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Apr 25, 2013 at 15:48

Kermit

34.1k13 gold badges89 silver badges121 bronze badges

Collectives™ on Stack Overflow

PHP to search SQL DB beween dates

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related