Can I have a PHP variable in a SQL query statement?

Under my settings area of my code, I want to define the amount of weeks that I reference in my SQL query.

$sql="SELECT * FROM resources	WHERE reso_date >= DATE_SUB(CURDATE(), INTERVAL 14 WEEK)";

I want to write code something similar to this:

$sql="SELECT * FROM resources	WHERE reso_date >= DATE_SUB(CURDATE(), INTERVAL " . $resourceWeeks . " WEEK)";

I know the above won’t work with the variable placed within the statement. Is there a way this can be accomplished with PHP?

Have a look at prepared statements.

I am familiar with prepared statements, but I am not certain of what can and can’t be passed through them.

If I can do the following then all is good:

$sql=“SELECT * FROM resources WHERE reso_date >= DATE_SUB(CURDATE(), INTERVAL ? WEEK)”;

I don’t think that placeholders in prepared statements will work on anything other than colunm values.

I suppose to make the variabe “safe” it could be cast as an integer.

1 Like

I’m just a newbie coder, but I also thought that you could only pass column values.

I have been testing the above number replacement with ?, and it does appear to be working. I will go with this, unless there are any concerns that could come up with doing this.

I just tried it and the placeholder does work on the INTERVAL value.
I’m surprised by this as most things other than column values don’t work with placeholders.
Learnt something.

1 Like

Generally any value works, but any SQL keyword or anything defined as part of a schema doesn’t. So you can’t use column or table name or use a placeholder to swap between > and <

3 Likes

more specifically - understand that any string you put in will be wrapped in quotes - so you can only substitute things where quotes would be allowed.

To use Tom’s example - if I said WHERE column1 :comparitor column2, and then tried to give it <, PHP parses it into a string, slaps quotes around it, and my SQL looks like WHERE column1 "<" column2, to which the SQL engine on the other side goes ‘you what?’

You can replace integers and doubles fine, because they get handled as such, you can stick quoted strings in wherever you could use a quoted string. (Table/database names are NOT quoted strings…)

1 Like

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