-3

Good Afternoon, Can someone please point us in the right direction and or kindly explain. I am trying to ECHO / PRINT SQL Query results on a webpage ad will later use to create Charts/Graphs.

For some reason i cannot get my head around alias; Example:

SELECT asset_detail, COUNT(asset_detail) AS TOTAL  
FROM `WHS` 
WHERE appeared >= now() - interval 21 day 
GROUP BY asset_detail 
ORDER BY 2 DESC 
LIMIT 10;

I am unable to print the TOTAL bit from above Query;

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

$sql = "SELECT asset_detail, COUNT(asset_detail) AS TOTAL  
        FROM `WHS` 
        WHERE appeared >= now() - interval 21 day 
        GROUP BY asset_detail 
        ORDER BY 2 DESC 
        LIMIT 10";
$result = mysqli_query($conn, $sql);

// Associative array
$row = mysqli_fetch_assoc($result);
echo "Rows found :" . $result['TOTAL']; 

// Free result set
mysqli_free_result($result);

mysqli_close($con);
?>

I am after a total to top 10 assets for rolling 21 days, asset name and count with a view to showcase this information in a graphical form at a later date. I have tried multiple ways of writing the Echo, Print but unable to get results.

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
KH4W4R
  • 1
  • 1
  • Do you mean that you only ever see the First result from the query? – RiggsFolly Aug 30 '23 at 16:07
  • I do not see any results from this query, if I remove the TOTAL and or simplify the query, i can see results. I am trying to print the COUNT of a column. – KH4W4R Aug 30 '23 at 16:09
  • I need help with this section: $row = mysqli_fetch_assoc($result); echo "Rows found :" . $result['TOTAL']; – KH4W4R Aug 30 '23 at 16:10
  • Only this bit: – KH4W4R Aug 30 '23 at 16:12
  • 2
    **`echo "Rows found :" . $row['TOTAL'];`** Just a bit of a TYPO. You loaded a variable called `$row` not a variable called `$result` probably good old Copy/Paste without looking at what you are doing – RiggsFolly Aug 30 '23 at 16:13
  • 1
    You may also need a loop to process all the rows in the resultset – RiggsFolly Aug 30 '23 at 16:17
  • 1
    This typo should have caused a warning to appear (which would have helped to alert you to the cause of the problem). If not, check if PHP error reporting / logging is enabled. – ADyson Aug 30 '23 at 16:33

0 Answers0