Select data within a date range using form

Question

I would like to search a mysql database for a list of products that have been inserted between a date range. Say I have a table called product which has: id, name, brand, and datecreated, I can do this in mysql:

select id, name, brand, datecreated 
  from product 
 where datecreated between '2013-09-12' and '2013-10-10'
 order by datecreated desc;

This will obviously return the desired result either in Mysql or within php.

However I would like to define the date range in the select statement with a form which asks the user for the to and from dates. Not sure what the method will be either ?

    <form action="?" method="">
        <div id="dates">
            <label for="searchdates">Enter your dates here:</label>
            <br>
            <label for="fromdate">From:<textarea id="fromdate" name="fromdate" rows="1" cols="5"></textarea></label>
            <lablel for="todate">To:<textarea id="todate" name="todate" rows="1" cols="5"></textarea></label>
        </div>
        <div><input type="submit" value="Search"/></div>
    </form>

So what I am unsure of is how the user defined date range from the form can be added/inserted to the select statement to return the data within the defined date range?

Cheers Volterony

Nick Coons · Accepted Answer · 2014-01-29 16:14:43Z

1

The user-submitted values would come in as $_POST['fromdate'] and $_POST['todate'], or $_GET['fromdate'] and _$GET['todate'], depending on whether your form method above was post or get (I'd generally recommend post in case there is a specific reason for using get, like wanting to bookmark the page).

So your code to handle this could look something like this (assuming $pdo is your database PDO object):

$fromdate = new DateTime( $_POST['fromdate'] );
$todate   = new DateTime( $_POST['todate'] );

$query = <<<SQL
  SELECT id, name, brand, datecreated
    FROM product
   WHERE datecreated BETWEEN :fromdate AND :todate
   ORDER BY datecreated desc
SQL;

$result = $pdo->prepare( $query );
$result->execute( array(
    ":fromdate" => $fromdate->format( "Y-m-d" ),
    ":todate"   => $todate->format( "Y-m-d" );
));

$rows = $result->fetchAll( PDO::FETCH_ASSOC ):

$rows now contains an array of all of the rows of your table that match the specified criteria.

answered Jan 29, 2014 at 16:14

Nick Coons

3,6921 gold badge21 silver badges22 bronze badges

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

2 Comments

Volterony Over a year ago

Nick, thanks for your response. I am not using PDO for this particular situation, however your example will prove very useful when I use PDO in future.

Nick Coons Over a year ago

In either case, you'll want to format the date properly before using it with your query. @danmullen's answer that you've accepted will only work if the user submits the date in the format "Y-m-d", and will break when they enter it in a more common format, like "n/d/Y".

danmullen · Accepted Answer · 2014-01-29 16:14:42Z

0

The exact syntax depends on how you're interacting with your database (MySQLi, PDO, etc.). You basically need to take the POSTed form values, do some straightforward validation to make sure that the user hasn't entered anything dangerous, then insert the values into the query:

$from = mysqli_real_escape_string($_POST['fromdate']);
$to = mysqli_real_escape_string($_POST['todate']);
$sql = "select id, name, brand, datecreated from product where datecreated between '$from' and 
'$to' order by datecreated desc";

The action on the form would be whichever page you place the above code in - it could be the same page as the form. In that case, the action would be <?php echo basename($_SERVER['PHP_SELF']); ?> and the method would be POST.

answered Jan 29, 2014 at 16:14

danmullen

2,5003 gold badges22 silver badges28 bronze badges

1 Comment

Volterony Over a year ago

I appreciate your response to this question danmullen. Now that it has been explained, I can see what is required so I'll give this a try. Thanks for your help.

Collectives™ on Stack Overflow

Select data within a date range using form

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related