I have a question about using a MySQL Query to convert my data into a JSON Object. The Query I have is converting to a JSON Object, but it is not working the way I would like.
I have multiple tables in my database that I would like to graph on a chart using the date as the X axis and the values as the Y axis. I am currently joining the tables by date. However, some tables may have multiple submissions per day while others may not have any. Currently, the Query I have is only showing results for dates that data was submitted to all 4 tables.
I would also like to graph the information on a scale of 0-10. Three of the 4 tables only have values from 0-10 so I am taking the average of each value per day. The nutrition table, which holds nf_sugars and nf_total_carbohydrates has larger numbers that I will be using normalization to convert them into a 0-10 scale. For now, I am just attempting to get the SUM per day and will complete the rest of the calculation after this part is working. However, the query I am currently running is giving me results that are much higher than the SUM of the actual numbers in my database.
Any help would be greatly appreciated! Here is the PHP I am currently using to create the JSON Object. As a side note, I did successfully connect to my database, I just did not include that here.
$myquery = "SELECT track_ticseverity.date,
AVG(track_ticseverity.ticnum) as average_ticnum,
track_fatigue.date,
AVG(track_fatigue.fatiguenum) as average_fatiguenum,
track_stress.date,
AVG(track_stress.stressnum) as average_stressnum,
track_nutrition.date,
((SUM(track_nutrition.nf_sugars) ) ) as sum_nf_sugars,
((SUM(track_nutrition.nf_total_carbohydrate) ) ) as sum_nf_total_carbohydrate
FROM track_ticseverity
INNER JOIN track_fatigue
ON track_ticseverity.date=track_fatigue.date
INNER JOIN track_stress
ON track_fatigue.date=track_stress.date
INNER JOIN track_nutrition
ON track_stress.date=track_nutrition.date
WHERE track_ticseverity.user_id=1
AND track_fatigue.user_id=1
AND track_stress.user_id=1
AND track_nutrition.user_id=1
GROUP BY track_ticseverity.date";
$query = mysqli_query($conn, $myquery);
if ( ! $query ) {
echo mysqli_error(s);
die;
}
$data = array();
for ($x = 0; $x < mysqli_num_rows($query); $x++) {
$data[] = mysqli_fetch_assoc($query);
}
echo json_encode($data);
mysqli_close($conn);
EDIT - The Query is successfully returning a JSON object. My issue is that the query I wrote does not output the data in the correct way. I need the query to select information from multiple tables, some with multiple submission per day and others with only one or no submissions.
EDIT2 - I am thinking another way to handle this is to combine multiple SELECT statements into a single JSON Object, but I am not sure how to do this.