Mysql Empty Result When Used Variables In The Query

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 ??

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

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

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

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

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.

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

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

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

Ah yes, didn’t think of that.

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