Using PHP Variables in MySQL

Question

I'm just wondering why this query returns nothing in PHP on the browswer, while it returns a good amount of items when used in the MySQL Workbench.

I am getting dates back by echoing both of these and then using the same dates and getting items returned in the MySQL Workbench.

$_POST['startdate']
$_POST['enddate']

So I am unsure why it doesn't work in the query. There are no PHP or MySQL errors/warnings that appear.

SELECT 
        mantis_category_table.name, 
        mantis_bug_history_table.bug_id, 
        FROM_UNIXTIME(mantis_bug_table.date_submitted, "%m-%d-%Y") AS DATE2, 
        FROM_UNIXTIME(min(mantis_bug_history_table.date_modified), "%m-%d-%Y") AS FirstOfdate_modified,
        ROUND((min(mantis_bug_history_table.date_modified)- mantis_bug_table.date_submitted)/ 86400, 1) as day_difference
    FROM (mantis_bug_table INNER JOIN mantis_bug_history_table 
    ON 
        mantis_bug_table.id = mantis_bug_history_table.bug_id) 
    INNER JOIN 
        mantis_category_table ON mantis_bug_table.category_id = mantis_category_table.id 
    WHERE
        FROM_UNIXTIME(mantis_bug_table.date_submitted, "%m-%d-%Y")
            BETWEEN '.$_POST['startdate'].' AND '.$_POST['enddate'].'
    GROUP BY 
        mantis_category_table.name, mantis_bug_table.category_id, mantis_bug_table.date_submitted, mantis_bug_table.status, mantis_bug_history_table.bug_id, mantis_bug_history_table.field_name, mantis_bug_history_table.new_value 
    HAVING 
        (((mantis_bug_table.status)<>90) AND ((mantis_bug_history_table.field_name)="status") AND ((mantis_bug_history_table.new_value)="50")) 
    ORDER BY 
        mantis_category_table.name, mantis_bug_table.date_submitted'

The trouble seems to be this portion of the query. Is this invalid in MySQL? Is there a better way to do it?

    WHERE
        FROM_UNIXTIME(mantis_bug_table.date_submitted, "%m-%d-%Y")
            BETWEEN '.$_POST['startdate'].' AND '.$_POST['enddate'].'

Thanks.

Have you echoed out the query with the POST variables and run it using MySQL Workbench? — Michael O'Brien, Jan 05 '15 at 23:41
`BETWEEN '".$_POST['startdate']."' AND '".$_POST['enddate']."'` give that a *whirl* — Funk Forty Niner, Jan 05 '15 at 23:48
@Fred-ii- - It was actually more like this: `BETWEEN "'.$_POST['startdate'].'" AND "'.$_POST['enddate'].'"` - But if you write it as an answer, I'll accept it. Thanks! — UndefinedReference, Jan 05 '15 at 23:55

score 3 · Answer 1 · edited May 23 '17 at 11:57

First of all, you should always escape user input before passing it to a query, or better yet, use prepared statements. See this related question.

Second, you must make sure that the date values you receive from PHP are in the same format as those stored in your database. Since you are storing a unix time in your column, you could use strtotime to convert your posted dates into a unix time suitable for direct comparison to the column values, or you could use date_format to convert them to a valid date format like you are attempting in your current comparison.

score 1 · Accepted Answer · edited May 23 '17 at 10:26

As per OP's wish:

Concatenate your values like this:

BETWEEN "'.$_POST['startdate'].'" AND "'.$_POST['enddate'].'"

as you had missing quotes which are needed since we're dealing with string values.

Dates can contain hyphens (and colons) such as 2015-01-05 22:00 which will throw an SQL error.

However, I would like to point out that using this type of query is open to SQL injection.

Use mysqli with prepared statements, or PDO with prepared statements, they're much safer.

Using PHP Variables in MySQL

2 Answers2