I have a table with data and a column named 'week'. Everytime an user post something to the database, this is added to the column 'week':
$week = Date('W');
So everything inserted in this week have the number '13' in the week-column. Now I would like a query that select everything from this week and post on my site, so I wrote a variable like the one above and wrote:
$sql = 'SELECT * FROM my_table WHERE week = $week';
But it echos an error: Unknown column '$week' in 'where clause'.
It works fine, if I write SELECT * FROM my_table WHERE week = 13; but I would like it to automatically select the current week number.
Be careful to SQL Injection and change this
$sql = 'SELECT * FROM my_table WHERE week = $week';
in
$sql = 'SELECT * FROM my_table WHERE week = '.$week;
you have to use double quotes for the string:
$sql = "SELECT * FROM my_table WHERE week = $week";
If not, $week will not substituted.
You should use prepared statements to prevent SQL injection.
Why not simply do
$sql = "SELECT * FROM my_table WHERE week = WEEK(NOW())";
Just a note: SELECT * is not good and very bad practice. Select only what you need.
Please use this, it is safer vs SQL injection:
$week = $mysqli->escape_string($week);
$sql = "SELECT * FROM my_table WHERE week = '" . $week . "'";
$retval = $mysqli->query($sql);
You need the mysqli object set as connection to your database for this to work. After that you can fetch the rows from $retval.
Assuming your date column is an actual MySQL date column:
SELECT * FROM my_table WHERE date > DATE_SUB(NOW(), INTERVAL 1 WEEK);