how to get events from data base depending on month selected

Question

I have events stored in my database in the format 2016-12-05, ie 5th December 2016.

I am using HTML 5 month picker so users can see all events in a particular month.

My question is how can I edit the sql so it can recognise the above date should be selected if they chose December 2016.

HTLM code:

    <form method="post" name="Month" id="Month">
    <input type="month" name="month" id="month"/>
    <input type="submit" value="Select"/>

The PHP code:

    $month = $_POST['month'];
    if (isset($_POST['month'])){
        $sql = "SELECT * FROM training WHERE groupName='$group' && sessDate='$month' ORDER BY sessdate ASC";
    $training_query = mysqli_query($db_conx, $sql);
    }

If I get rid of the month picker stuff it all works fine, $group is established higher up on the page. sessDate is stored as date in the database.

If i remove it all it shows every event ever created.

I am thinking I may need to use ajax or something as when I hit select the page obviously refreshes and the month picker is empty again. My knowledge of ajax and java script is very poor though so any help would be much appreciated.

Possible duplicate of [how to bind multiple parameters to MySQLi query](http://stackoverflow.com/questions/16612251/how-to-bind-multiple-parameters-to-mysqli-query) — devlin carnate, Jun 10 '16 at 18:36
sorry @devlincarnate i'm not sure if i understand the answer in your link. does it mean it will store/bind the month/parameter on the page refresh? — dyer926, Jun 10 '16 at 18:44
@dyer926 - You should use prepared statements with parameter binding. Anything else is vulnerable to SQL injection. — devlin carnate, Jun 10 '16 at 20:43

score 0 · Answer 1 · answered Jun 10 '16 at 18:52

Try to use prepared statements instead of passing variables to query. If $month is a number (for example 11) or the name of the month (November) you will need to use MONTH() or MONTHNAME() in your query. For example SELECT * FROM training WHERE...MONTHNAME(sessDate)=....

score 0 · Answer 2 · answered Jun 10 '16 at 19:00

This script has seen better days. A couple of things here: In your html code you are sharing ids and names for elements. Not good. You're assigning $month before you validate whether it's set or not. You said your dates are stores as 'Y-m-d' but in your query it appears you are just searching for a month

Assuming $sessDate should be formatted as Y-m-d to match what you have in the database

      <?php
         $date = new DateTime()//get current Date
         $bottomDate = $date->format('Y').$sessDate.'01 00:00:00'; //Set to absolute beginning of month 
         $topDate = $date->format('Y').$sessDate.'31 23:59:59'; //Set to absolute end of month, with no month having more than 31 days
         $sql = "SELECT * FROM training WHERE groupName='$group' && sessdate >= '$bottomDate' AND sessdate <= '$topDate' ORDER BY sessdate ASC";


       ?>

score -1 · Answer 3 · answered Jun 10 '16 at 18:34

-1

if you check month element on the browser console;

document.getElementById("month").value

it should give you something like '2016-06'.So you have you filter the records like

"SELECT * FROM training WHERE groupName='$group' && sessDate='".substr($month,0,7) ."' ORDER BY sessdate ASC

answered Jun 10 '16 at 18:34

emrhzc

1,347
11
19

This is completely insecure and should never be done. – devlin carnate Jun 10 '16 at 20:43
Indeed. But what OP asks is not how to secure the app, but to enable it. Even the code in the question is equivalently insecure. – emrhzc Jun 10 '16 at 20:48
You could have offered a solution with prepared statements and parameter binding. You did not. Instead, you suggested one of the most insecure methods for adding parameters to a database query. – devlin carnate Jun 10 '16 at 21:19
No, I did not offer anything but what he asked for, his code already had a concatenated query, I also offered my solution with a concatenated query. The question is irrelevant to this! Are solutions supposed to help OP's more than they ask? I can understand that one can do that, but do all answers have that obligation? – emrhzc Jun 10 '16 at 21:26
Yes. All answers have the obligation to not blindly perpetuate nuclear proliferation. – devlin carnate Jun 10 '16 at 21:55

how to get events from data base depending on month selected

3 Answers3