0

I am using this to query from my table:

$res = $link -> query("SELECT count(*) FROM sales WHERE status='OK'") or die($mysqli->error);
$num_rows = mysqli_fetch_row($res)[0];
$numberOfSales = $num_rows;

In table sales I also have a column named date datetime.

I want it to only returns the number of rows where date is the same as the current week number. So if the date column cell has value 2020-08-03 16:25:26, that converted to week number is 32. I have been looking at strftime("%V",, but not sure how to proceed. Any tips?

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Erik Auranaune
  • 1,384
  • 1
  • 12
  • 27
  • It is a very bad idea to use `die(mysqli_error($conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) – Dharman Aug 05 '20 at 22:33

1 Answers1

0

Consider and index-friendly expression such as:

select count(*) 
from sales 
where 
    status='OK' 
    and datetime >= current_date - interval weekday(current_date) day

Expression current_date - interval weekday(current_date) day dynamically computes the date that corresponds to the first day of the current week (starting on Monday).

If you may have dates in the future, then you can add an upper limit:

where 
    status='OK' 
    and datetime >= current_date - interval weekday(current_date) day
    and datetime <  current_date + interval (7 - weekday(current_date)) day
GMB
  • 216,147
  • 25
  • 84
  • 135
  • So I should just replace my code with your code inside the `$res = $link -> query("` that I have? – Erik Auranaune Aug 05 '20 at 18:44
  • @Kama: yes, indeed. – GMB Aug 05 '20 at 18:55
  • I tried that, and in this code `$num_rows = mysqli_fetch_row($res)[0];` I get error: `Warning: mysqli_fetch_row() expects parameter 1 to be mysqli_result, boolean given in `. – Erik Auranaune Aug 05 '20 at 19:12
  • @Kama: you need to recover the actual error message from mysql. Does the query work if you run it directly against the database (eg with phpmyadmin)? – GMB Aug 05 '20 at 22:48