1 reply to this topic

[jaydee]

Right,

I need to get 7 days of data from a mysql database and be able to have

$res1 = Amount of signups this day
$res2 = Amount of signups this day

Will need to use mktime(0,0,0); so it works from midnight, I've tried to do it a few times but with the maths and working out what query to use etc it's so hard!

At the moment, I have an array of all the 'start' times which is what I use to be the minimum time field value in the DB, then in the foreach loop I use the 86400 seconds with the start time to create my end time (so between start and end, grab all results, count them, that's result for day 1, echo it, move to day 2.

The query is

$sql = mysql_query("SELECT * FROM `t2_users` WHERE `reflvl1` = '$username' OR `reflvl2` = '$username' AND `signuptime` > '$value' AND `signuptime` < '$prev'");

That's what I think the problem may be, I've experienced this problem a few times, constructing mysql statements properly when you want more than 1 separate WHERE operator. Basically, I want to select from t2_users ALL users who have reflvl1 or reflvl2 set to the logged in users username. But as well as that, I need to select only the ones who's signup time was between the times stated.

Any help appreciated :)

[Orjan]

your problem is with your ANDs and ORs.

WHERE `reflvl1` = '$username' OR `reflvl2` = '$username' AND `signuptime` > '$value' AND `signuptime` < '$prev'

this is true if reflvl1 = '$username' totally independent as ANDs is evaluated before ORs, which means the very first or is evaluated last as

WHERE `reflvl1` = '$username' OR (`reflvl2` = '$username' AND `signuptime` > '$value' AND `signuptime` < '$prev') 

The solution is to change precedence with parenthesizes as in math.

WHERE (`reflvl1` = '$username' OR `reflvl2` = '$username') AND `signuptime` > '$value' AND `signuptime` < '$prev' 

This will now be true if username is in reflvl1 or reflvl2 at the same time as signuptime shall be larger than $value and smaller than $prev

Edited by Orjan, 07 November 2011 - 11:04 PM.
misplaced parantesis