I am getting loose results from FROM_UNIXTIME

[Monkuar]

YEAR(FROM_UNIXTIME(date)) =  2012  AND MONTH(FROM_UNIXTIME(date)) = 05 

 

Okay, So this query is selecting the year and month to 2012 and may.

 

I have a date column in my database, it's called "date" with the values of unixtimestamp, I have like 20-30rows.

 

The problem is, this query works, but I see results from july and august too? how can I get a more refined result?

 

 

[Barand]

What is the whole query?

[PFMaBiSmAd]

Are you sure your column isn't a Mysql TIMESTAMP data type?

[Monkuar]

Are you sure your column isn't a Mysql TIMESTAMP data type?

 

It's a int (10), just storing a regular timestamp.  Should I make it a timestamp data type?

 

 

Query is:

 

SELECT gl.*, m.username,m.star, m2.username as fromname,m2.star as fromstar from gold_logs gl left join users m on (gl.to_id=m.id) left join users m2 on (gl.from_id=m2.id) WHERE to_id=271 OR from_id=271 AND YEAR(FROM_UNIXTIME(date)) = 2012 AND MONTH(FROM_UNIXTIME(date)) = 05 ORDER BY date DESC LIMIT 0, 35

 

It's selecting all the gold_logs that are sent to the user, with the from_id = 271 and showing all the gold_logs sent out from the user with to_id = 271.

 

 

And screenshot of the columns.

 

 

That just shows that I am left joining the  to_id and from_id to the "users" table, as you can see.

 

If I try GROUP BY to_id, it works perfect and only shows from August! But only shows to_id data, not the to_id and from_id that I need.  So you're wondering, well why don't you do to_id,from_id? Well I tried that, and it goes back to my main problem, shows.. but shows other dates as well.

 

 

EDIT: I know storing html in there is horrible, but please this is just development. Don't shoot me on that, lol

[Monkuar]

 

 

(bj = 0 AND to_id={$pun_user['id']} OR from_id={$pun_user['id']}) AND YEAR(FROM_UNIXTIME(date)) = {$newdate['2']}  AND MONTH(FROM_UNIXTIME(date)) = {$newdate['0']} 

 

using ( ) in the query fixes it? Wat the hell?

 

How does using parentheses fix this problem?

[xyph]

You need to group conditionals when using a combination of AND and OR, or the engine will have to make assumptions.

[Barand]

... exactly!

 

A AND B OR C

 

is this

 

A AND (B OR C)

 

or

 

(A AND B) OR C