-2

I need help to be able to sum "total_attendance table column" by user id weekly starting from Sunday and echo it out.

This week's output should override last week's output, while next week's output should override this week's output on and on like that

Below is the code i already wrote for this

I got this error.

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in ….

Beyond the error, please i need a working code.

Thanks as always

<?php $result = mysqli_query($con, "SELECT SUM(totalattn) FROM 
attendance GROUP BY yearweek(date, 0) WHERE user_id='$user_id'");

 while ($row = mysqli_fetch_assoc($result)) {
 $totalattn = $row['totalattn']; 
 echo $totalattn;
 }                            
?>
Laolu Aladejana
  • 77
  • 1
  • 6

1 Answers1

1

The GROUP BY clause requires you specify the field by which to group as part of the SELECTclause. Also, the WHEREclause needs to be placed before the GROUP BY clause 

<?php $result = mysqli_query($con, "SELECT yearweek(date, 0), SUM(totalattn) FROM 
attendance  WHERE user_id='$user_id' GROUP BY yearweek(date, 0)");

Always follow this order: 

SELECT something
FROM some place
JOIN with some other place
WHERE conditions are met
GROUP BY something you selected which you'd want to aggregate

Also, remember to escape your query to prevent injections and all kinds of nasty

Javier Larroulet
  • 3,047
  • 3
  • 13
  • 30
  • 1
    `The GROUP BY clause requires you specify the field by which to group as part of the SELECTclause` Have any documentation for that? – Devon Bessemer Aug 30 '18 at 00:06
  • @javier, in order of yours, i reconstructed the query in this manner, yet i still got the same error. – Laolu Aladejana Aug 30 '18 at 00:11
  • @Devon That's generally an SQL requirement, although MySQL may not enforce it, depending on the version/mode. https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html Still, without seeing the actual error, pointing to this as the cause seems a bit speculative. WHERE before GROUP BY, on the other hand... – Don't Panic Aug 30 '18 at 00:16
  • No, you didn't add the most important part.. the `SELECT`clause must contain the field by which you intend to group... try `SELECT yearweek(date, 0), SUM(totalattn) FROM...` and group by yearkweek(date, 0) – Javier Larroulet Aug 30 '18 at 00:17
  • Am i to group by column name? because i have date timestamp as a column? – Laolu Aladejana Aug 30 '18 at 00:18
  • You need to group by "something". Since your desired result is to get the SUM of `totalattn` by week, you need to have a column with the week to group by... – Javier Larroulet Aug 30 '18 at 00:21
  • @JavierLarroulet I have reconstructed according to your last comment, i still got the same error. – Laolu Aladejana Aug 30 '18 at 00:23
  • what error are you getting from the mysql_server? (not the PHP error you quoted in the question) – Javier Larroulet Aug 30 '18 at 00:27
  • @JavierLarroulet This is the error from mysql server: #1054 - Unknown column 'date' in 'field list' – Laolu Aladejana Aug 30 '18 at 00:33
  • My date column was attdate but now i have changed it to date, i no longer get that error. Thanks for that clue @JavierLarroulet. But now i get this error: Notice: Undefined index: totalattn in /Applications/XAMPP/xamppfiles/htdocs/soap/attendance.php on line 215 – Laolu Aladejana Aug 30 '18 at 00:37
  • that means 'totalattn' is not an index for the `$row` array.. Since you're using `while` you need to specify the index with a control variable (you may define $i = 0 before entering the while loop, change `$row['totalattn']` to `$row[$i]['totalattn']` and increment $i by 1 right after echoing. If all else fails, try outputting the whole array with print_r so you can see the exact array structure and indexes so you know the exact way to access the data – Javier Larroulet Aug 30 '18 at 00:55
  • 1
    @JavierLarroulet. With your help, i was able to solve the problem. it works now. Thanks a lot. – Laolu Aladejana Aug 30 '18 at 01:03