Mahbs Mahbs - 29 days ago 9
PHP Question

No result found in query

I'm getting an error in my query, and I'm unable to detect the source of the problem.

Here is the query:

$query = "SELECT useraccount.Username, tariff.Name as tariffs,
sum(energyconsumption.ElecEnergy)
FROM useraccount
INNER JOIN tariff
ON useraccount.tariffs = tariff.id
INNER JOIN energyconsumption
ON energyconsumption.User = useraccount.id
WHERE Date = CURRENT_DATE
GROUP BY useraccount.Username, tariff.Name as tariffs";


Following the query I've code that stores the output in an array:

$result = mysqli_query($conn,$query);
$r = array();
if($result->num_rows){

while($row = mysqli_fetch_array($result)){
array_push($r, array( 'Username' => $row['Username'],
'TariffName' => $row['tariffs'], 'ElecConsump' => $row['ElecEnergy']


));

}

} echo json_encode(array('results' => $r));


Im getting an error in the following line: if($result->num_rows)

This is the output when executing the query:

Notice: Trying to get property of non-object in C:\xampp\htdocs\Project\Client\newone.php on line 22
{"results":[]}

Please note:

This was the output i intially had:

{"results":[{"Username":"absc868","TariffName":"s1","ElecConsump":"2000"},
{"Username":"absc868","TariffName":"s1","ElecConsump":"1900"}]}


But with this new query I have written above, I am trying to produce this output
I am trying to produce the following output:
= {"results":[{"Username":"absc868","TariffName":"s1","ElecConsump":"3900"}

That being, a result set that only has 1 entry, for username, tariff and elecconsump, rather than 2 entries for username, tariff and elecconsump

Thank you once again to all those who have read and contributed to this thread

Answer

One problem is the as in the GROUP BY. I would recommend that you use table aliases:

SELECT ua.Username, t.Name as tariffs, SUM(ec.ElecEnergy)
FROM useraccount ua INNER JOIN
     tariff t
     ON ua.tariffs = t.id INNER JOIN
     energyconsumption ec
     ON ec.User = ua.id
WHERE Date = CURRENT_DATE
GROUP BY ua.Username, t.Name;