one or two querys?

__berfuzz

At the moment I have:

$result = array();

$query = "SELECT * FROM resultat WHERE user = $user AND kontroll = 'ok!' AND time BETWEEN $start AND $end";

$sql = mysql_query($query) or die('MySQL error: ' . mysql_error()); 

while($resultat = mysql_fetch_array($sql)){
    $time = $resultat['time'];
    $week = date('W', $time);
    $day = date('w', $time);
echo $week;   //I've tried to write different if(statements) with out any luck.
echo $day;    //If a new week is set I'd like to set 0 for each day in that week without resetting the ones with a result.
    $result[$week][$day]++;
}

The simples thing to do seems to be using two guerys. First get the weeks were there is anything registered. Then set 0 for each day in the weeks appering. Now query to get the results in the array,result[week][day]...

Piranha

You should never use a query in a loop. The result is that there is not two calls to the database, it could easily be hundreds and it takes time.

__berfuzz

No, I ment more like this:
1. query 1. get every week within a timespan given by timestamp
2. use weeks given in 1:th query to set the array with zero for each day before results are looped.
3. query 2. Loop results in the right place in the array.

Piranha

I'm sorry, but I don't understand what you mean. Maybe someone else does and can help you out.

__berfuzz

Well, theres people on a crusade against bad code and then theres my kind. I tend to go for things that work, simple and plain. The issue here is if I should have two querys when I should be able to to it with one.

two querys

query_one = SELECT week FROM table where... // getting week from timestamp
while-loop{week = array(0,0,0,0,0,0,0); //setting all days(result) in the weeks given to zero
query_two = SELECT * FROM table where... //getting days and weeks from timestamp
while-loop{ // using week-arrays from first sql-while-loop to set a two-dim array.}

one query

query = SELECT * FROM table where... // getting
while-loop{
//see to it that days with no results is zero.}

At the moment I use the first one, trying to get number two to work. On my new years holiday... Wich reminds me: Happy new year folks!!!

nemonoman

You can use a single SQL statement to acheive your goal of counting the results by day.

SELECT DATE_FORMAT(time, '%Y %m %d'), COUNT(*)
FROM resultat
WHERE user = $user
AND kontroll = 'ok!'
AND time BETWEEN $start AND $end
GROUP BY DATE_FORMAT(time, '%Y %m %d')

I can't figure out what your 'week' variable is attempting to do, but it will be easier to achieve using the highly sophisticated options of the DATE_FORMAT function.

In my example, by the way, you can also change the SELECTed DATE_FORMAT to display in one format, and the GROUP BY DATE_FORMAT to create counting using another format.

bradgrafelman

Also note that string values in MySQL must be surrounded by quotes, just as they do in PHP.

__berfuzz

Well the problem showed out to be how I used timestamp.

$query = "SELECT * FROM resultat WHERE user = $user AND kontroll = 'ok!' AND time BETWEEN $start AND $end";

$sql = mysql_query($query) or die('MySQL error: ' . mysql_error()); 

while($resultat = mysql_fetch_array($sql)){
    $time = $resultat['time'];
    $week = date('W', $time);
    $day = date('w', $time); // fixa så att 0 är måndag och 6 är söndag
	if ($day == 0){ // fixa söndag 0 blir 7
		$day = 7;
	}
    $result[$week][$day]++;
}

Now weeks place days in different arrays and it is easy to echo it out in a neat little table. You could probably say its like a calendar for the month picked by the visitor.