mysqli_query return false instead of 1 row

I do not understand why mysqli_query return false, I seach a lot on internet but I have not found anything.

My code :

$M1 = "SET @Month = month(TIMESTAMPADD(month, -1, CAST(now() AS DATE)));    
Set @firstDay = CONCAT('01','/', @month, '/',year(now())); SET @lastDay = 
last_day(TIMESTAMPADD(month, -1, CAST(now() AS DATE))); SELECT count(*) 
AS M1 FROM APPEL where CAST(D_CREATION AS DATE) BETWEEN @firstDay and 
@lastDay";

$connexion = cnx();
if ($connexion) 
{
  $result = mysqli_query($connexion, $M1);
  if($result)
  {
    $row = mysqli_fetch_assoc($result); // This is Line 55
    $_SESSION['ArrayMois'][0] = implode($row);
  }
  else
  {
    $_SESSION['ArrayMois'][0] = 0;
  }   
}

The error is :

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

This query always returns false but if I execute it in PHPMyAdmin I have one row returned.

Answer

1) MySQL reporting log locations

2) Read this answer for how to generate and read MySQL error reports.

For your code:

$row = mysqli_fetch_assoc($result) 
      or error_log("MYSQL Error on line ". __LINE__. 
         " :: ".print_r(mysqli_error($connection),true));

Update:

You state:

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

What your error means:

It means the Query did not work as expected therefore:

$result = mysqli_query($connexion, $M1) 
          or error_log("MYSQL Error on line ". __LINE__. 
             " :: ".print_r(mysqli_error($connection),true));

---

The Issues:

You are running multiple separate SQL queries in one MySQL_query query handler, which by default will not be allowed in most instances. Maybe you have set up your SQL to accept concatenated querys, but generally that’s unwise and less likely.

Alternative. You can create a PROCEDURE, FUNCTION or similar.

You are also not wrapping your column names in backticks, which is highly advisable.

---

Fxing your SQL:

Currently you have:

SET @Month = month(TIMESTAMPADD(month, -1, CAST(now() AS DATE)));  

Set @firstDay = CONCAT('01','/', @month, '/',year(now())); 

SET @lastDay = last_day(TIMESTAMPADD(month, -1, CAST(now() AS DATE))); 

SELECT count(*) AS M1 FROM APPEL where CAST(D_CREATION AS DATE) 
       BETWEEN @firstDay and @lastDay";

This can be written much more concisely as:

SELECT COUNT(*) as M1 FROM APPEL WHERE CAST(`D_CREATION` AS DATE) BETWEEN
(LAST_DAY(CURDATE() - INTERVAL 1 MONTH) + INTERVAL 1 DAY) AND LAST_DAY(CURDATE())

This will list all results between the first and last days of the current month. I am unclear from your code what you’re exactly trying to reach but it appears you may want the previous month’s listing, in which case you can use:

SELECT COUNT(*) as M1 FROM APPEL WHERE CAST(`D_CREATION` AS DATE) BETWEEN
(LAST_DAY(CURDATE() - INTERVAL 2 MONTH) + INTERVAL 1 DAY) 
AND LAST_DAY(CURDATE() - INTERVAL 1 MONTH)