Check if last 10 rows duplicated-CodePudding

I'm trying to code a php script, which can find if the last 10 rows from mySQL database are the same. And if the values are same, then the script could sent an email.

I can fetch the last 10 rows.

$sql = ("SELECT ID, DayTime,Temperature FROM AllinOne ORDER BY ID DESC LIMIT 10");
    
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
  // output data of each row
  while($row = mysqli_fetch_assoc($result)) {
    echo "id: " . $row["ID"]. " - DayTime: " . $row["DayTime"]. " - Temperature " . $row["Temperature"]. "<br>";
  }
} else {
  echo "0 results";
}

Last 10 results:

Now, I'm trying to check if last 10 rows in "Temperature" column are the same (as we can see).

I use:

$sql2 = "SELECT Temperature, COUNT(*) as duplicates FROM AllinOne GROUP BY Temperature ORDER BY ID DESC LIMIT 10";
$result2 = $conn->query($sql2);

if ($result2->num_rows > 0) 
{
  // output data of each row
  while($row2 = $result2->fetch_assoc()) 
  {
    echo $row2["duplicates"]. "<br>";
  }
}

but I can't take back the same results (i can't understand the final result).

$row2 results

Could you help me???

CodePudding user response：

Execute the following query and grab the result:

SELECT
    COUNT(*) AS count_rows,
    MIN(Temperature) AS min_temperature,
    MAX(Temperature) AS max_temperature
FROM (
    SELECT *
    FROM t
    ORDER BY DayTime DESC
    LIMIT 10
) AS x

Then send an email if count_rows = 10 and min_temperature = max_temperature.

CodePudding user response：

You just need to fix your second query like:

$sql2 = "SELECT Temperature, COUNT(*) as duplicates 
    FROM (
        SELECT ID, DayTime, Temperature FROM AllinOne ORDER BY ID DESC LIMIT 10
    ) LastData
    GROUP BY Temperature";

Here you can test PHP code with queries