Select * from this week

Question

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.

Possible duplicate of [How can I prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Tatranskymedved, Mar 29 '17 at 10:55

score 4 · Answer 1 · answered Mar 29 '17 at 09:45

4

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.

answered Mar 29 '17 at 09:45

JustOnUnderMillions

3,741
9
12

score 1 · Answer 2 · answered Mar 29 '17 at 09:35

1

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.

answered Mar 29 '17 at 09:35

Jens

67,715
15
98
113

you guys forgot the ' ' for week '$week' – Gert Mar 29 '17 at 09:38
2

@Gert Looks like the `week` column is an integer so the quotes are not necessary – RiggsFolly Mar 29 '17 at 09:39
@Gert: No. It Looks like week is of type int not of type character – Jens Mar 29 '17 at 09:39

score 0 · Accepted Answer · answered Mar 29 '17 at 09:34

0

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;

answered Mar 29 '17 at 09:34

valbrux

320
1
12

How would this protect against [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – RiggsFolly Mar 29 '17 at 09:41
This won't protect from SQL Injection, but that's the way if you don't want to use prepared statements – valbrux Mar 29 '17 at 09:42
That did it! Thank you! :) I will take a look at prepared statements! – Daniel Jensen Mar 29 '17 at 09:43
No worries.Yeah that's a more sopisthicated way to accomplish this – valbrux Mar 29 '17 at 09:44

score 0 · Answer 4 · answered Mar 29 '17 at 09:50

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.

score 0 · Answer 5 · answered Mar 29 '17 at 09:51

0

Assuming your date column is an actual MySQL date column:

SELECT * FROM my_table WHERE date > DATE_SUB(NOW(), INTERVAL 1 WEEK);

answered Mar 29 '17 at 09:51

Harshil Patel

298
2
8

Select * from this week

5 Answers5