How to make PHP handeling my "WITH CTE as" SQL?-CodePudding

I added my SQL query into the PHP code, but it choose to echo "0 results". I notice in Sublime Text editor that all the sql code shows in same color (not right), but when I remove the first words "WITH CTE as (" it becomes multi-color in Sublime Text - but the functions fails. In short, how do I make PHP handle my SQL? Thanks.

$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}


$sql = " 
    WITH CTE as  (  SELECT *
       , LEAD(total_weight, 1) OVER(
           ORDER BY weight_date DESC
       ) AS prev_total_weight
       
       , MIN(total_weight) OVER() AS lowest_weight
       , MAX(total_weight) OVER() AS highest_weight
       , FROM_UNIXTIME(weight_date, '%u') AS weight_week
           
             
           , ROW_NUMBER() OVER(
              ORDER BY weight_date DESC
           ) AS RowNum
           

   FROM   (
              SELECT *, weight_start_week   weight_end_week AS total_weight
              FROM YourTable
           ) t
        
          
)
SELECT
    `_ID`, `weight_date`, `weight_start_week`, `weight_end_week`
     weight_end_week 
    ,total_weight
    ,prev_total_weight
    ,lowest_weight
    ,highest_weight
    ,weight_week
    
    
    ,CASE
             WHEN total_weight > prev_total_weight THEN 'greater'
             WHEN total_weight = prev_total_weight THEN 'equal'
             ELSE 'less'
             END AS comparison
   ,RowNum
FROM CTE

";
$result = $conn->query($sql);

if ($result->num_rows > 0) {


  while($row = $result->fetch_assoc()) {
    echo "<table><tbody><tr><td>Latest:</td><td>" . $row["total_weight"]. "</td><td>Percent</td><td>up/down</td></td></tr><tr><td>Lowest:</td><td>" . $row["lowest_weight"].  "</td><td>" . $row["weight_week"]. "</td><td>Date:</td></tr><tr><td>Most:</td><td>" . $row["highest_weight"]. "</td><td>"  . $row["weight_week"]. "</td><td>Date:</td></tr></tbody></table>";

  }
} else {
  echo "0 results";
}
$conn->close();
?>

CodePudding user response：

You may inline the CTE into the main query, and use it as a subquery:

SELECT `_ID`, `weight_date`, `weight_start_week`, `weight_end_week`
       total_weight, prev_total_weight, lowest_weight, highest_weight,
       weight_week,
       CASE ...
FROM
(
    -- place CTE definition here
    SELECT *, ...
           ROW_NUMBER() OVER( ORDER BY weight_date DESC) AS RowNum
    FROM ...
) t;