Mysql Empty Result When Used Variables In The Query

PHP
,
1

This query works fine -

$sql = "SELECT * FROM `pricing_data` WHERE `Label`='BTC' AND `Timestamp` LIKE '2018-01-19 00:00:%' LIMIT 1" ;

But this doesn’t ;

$date =  date("Y-m-d ",strtotime("-1 days", time()))." 00:00:";
$to = "BTC" ;  
$sql = "SELECT * FROM `pricing_data` WHERE `Label`='$to' AND `Timestamp` LIKE '$date%' LIMIT 1" ;

Any Solution ??

2

I don’t see any difference in the dump of the second query other than an extra space between the date and time.

1 Like
3

Does the extra colon on the end of the time make any difference?

$date =  date("Y-m-d ",strtotime("-1 days", time()))." 00:00:";
                                                            ^
4

The whole approach is just wrong so forget about it. NEVER EVER put variables in a query. You need to use Prepared Statements.

5

I get that the LIKE is to match date times regardless of seconds values.

The syntax doesn’t look right to me, though I may be thinking more of problems I’ve encountered doing similar with prepared statements.

In particular, I have a feeling “$date%” should be more like “$date . '%'”

I’m also thinking that a MySQL datetime function could be used to advantage here, but because I have never needed to disregard seconds I can’t think of how that could be done if it is a possibility.

I agree that this is a good rule of thumb and best practice. And a definate when a query involves user supplied input. But because this query is using script supplied values I think it should be relatively safe as far as security risk goes. What would be more of a concern is ensuring data integrity. Perhaps not an issue for SELECT queries, but yes, prepared statements are the way to go even if not absolutely needed. Once using them becomes a routine habit a slip up won’t bite.

6

@SamA74 is right, I just removed the space and the query works like a charm now.

7

That’s not possible here. atleast in my case I have to use variables

8

Errm, it is absolutely possible. The query statement uses placeholders and the variable values get assigned to the placeholders.

9

Ah yes, didn’t think of that.

10

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.