PHP/SQL Help

FreheitOverture

Ok, so I'm completely confuzzled with this. I'm passing a variable into an SQL query and trying to retrieve all records in that table where the month equals the value of the variable. i.e. if I pass the variable 10 into the query, it should show all records in October.

So far I have this

$query="SELECT * from news WHERE EXTRACT(MONTH FROM TIMESTAMP 'date') = $month 
ORDER BY date DESC LIMIT $start, 4";

however, while I am not getting a syntax error, I should have 3 records displayed and have none.

Any ideas please?

Cheers

norbie

Try it with quote marks used around the variable e.g.

$query="SELECT * from news WHERE EXTRACT(MONTH FROM TIMESTAMP 'date') = [B]" .[/B] $month [B]. "[/B] ORDER BY date DESC LIMIT [B]" .[/B] $start [B]. "[/B], 4";

FreheitOverture

Nope, that didn't work, thanks for trying though

norbie

If you hardcode both variables to something, does it work?

e.g.

$query="SELECT * from news WHERE EXTRACT(MONTH FROM TIMESTAMP 'date') = 10 ORDER BY date DESC LIMIT 0, 4";

It looks like you will need some quote marks around that 10 as well, so try it like this:

$query="SELECT * from news WHERE EXTRACT(MONTH FROM TIMESTAMP 'date') = "10" ORDER BY date DESC LIMIT 0, 4";

If that works, then change your code to:

$query="SELECT * from news WHERE EXTRACT(MONTH FROM TIMESTAMP 'date') = "" . $month . "" ORDER BY date DESC LIMIT " . $start . ", 4";

FreheitOverture

Nope, hardcoding the variables does not work

djandyr

The field "date" is a reserved word within MySQL, this can be permitted but you will need to quote those identifiers with a backtick (`)

[php]$query="SELECT * FROM news WHERE
EXTRACT(MONTH FROM TIMESTAMP (`date`)) = $month
ORDER BY `date` DESC LIMIT $start, 4";[/php]Its good practice to escape any variables you pass into a query, this is for security to prevent any SQL injections. You can use a function available with PHP called mysql_real_escape_string.

Query Test

CREATE TEMPORARY TABLE news_test (date DATETIME);
INSERT INTO news_test VALUES  ('2008-08-01 09:00:00');
INSERT INTO news_test VALUES  ('2008-09-01 10:00:00');
INSERT INTO news_test VALUES  ('2008-10-01 11:00:00');
INSERT INTO news_test VALUES  ('2008-10-02 13:00:00');
INSERT INTO news_test VALUES  ('2008-10-03 17:00:00');
SELECT * FROM news_test WHERE
EXTRACT(MONTH FROM TIMESTAMP (`date`))  = 10
ORDER BY `date` DESC LIMIT 0, 4;

Result :

2008-10-03 17:00:00
2008-10-02 13:00:00
2008-10-01 11:00:00

djandyr

Just thinking you could also use the MONTH() function with MySQL, probably reduce the complexity of the query

SELECT * FROM news WHERE
MONTH(`date`) = 10
ORDER BY `date` DESC 
LIMIT 0, 4