使用 PHP 将 MySQL 查询转换为 JSON [英] Convert MySQL Query into JSON using PHP
问题描述
我有一个关于使用 MySQL 查询将我的数据转换为 JSON 对象的问题.我的查询正在转换为 JSON 对象，但它没有按照我想要的方式工作.
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.
我的数据库中有多个表，我想使用日期作为 X 轴和值作为 Y 轴在图表上绘制图表.我目前正在按日期加入表格.但是，某些表可能每天有多次提交，而其他表可能没有.目前，我的查询仅显示数据提交到所有 4 个表的日期的结果.
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.
我还想以 0-10 的比例绘制信息.4 个表中的三个只有 0-10 的值，所以我每天取每个值的平均值.包含 nf_sugars 和 nf_total_carbohydrates 的营养表具有更大的数字，我将使用标准化将它们转换为 0-10 的比例.现在，我只是尝试每天获得 SUM，并会在这部分工作后完成其余的计算.但是，我当前正在运行的查询给出的结果远高于我的数据库中实际数字的 SUM.
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.
任何帮助将不胜感激！这是我目前用于创建 JSON 对象的 PHP.作为旁注，我确实成功连接到了我的数据库，只是没有在这里包含它.
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 - 查询成功返回一个 JSON 对象.我的问题是我写的查询没有以正确的方式输出数据.我需要查询来从多个表中选择信息，有些每天提交多次，有些只提交一次或不提交.
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 - 我正在考虑另一种处理此问题的方法是将多个 SELECT 语句组合到一个 JSON 对象中，但我不确定如何执行此操作.
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.
推荐答案
由于联接，总和大于预期.想象一下某个日期出现在一个 track_nutrition 记录和两个 track_fatigue 记录中，那么连接将使第一个表中的数据与第一个 track_fatigue 合并一次记录，然后再次使用第二个记录.因此相同的 nf_sugarsvalue 将在总和中计算两次.这种行为也会影响平均值.
The sum is larger than expected because of the joins. Imagine that a certain date occurs in one track_nutrition record and two track_fatigue records, then the join will make that the data from the first table is once combined with the first track_fatigue record, and then again with the second record. Thus the same nf_sugars value will be counted twice in the sum. This behaviour will also affect the averages.
因此，您应该先执行聚合，然后再执行连接.
You should therefore first perform the aggregations, and only then perform the joins.
其次，确保您捕获所有数据，即使在某个日期并非所有表都有值，您应该使用完整的外部联接.这将保证每个表中的每条记录会在结果中找到方法.现在，MySQL 不支持这种全外连接，所以我使用额外的子选择从 4 个表中选择所有不同的日期，然后将它们与其他聚合数据左连接":
Secondly, to ensure you catch all data, even if for a certain date not all tables have values, you should use full outer joins. This will guarantee that each record in each table will find its way in the result. Now, MySQL does not support such full outer joins, so I use an extra sub-select to select all different dates from the 4 tables and then "left join" them with the other aggregated data:
SELECT dates.date,
IFNULL(average_ticnum_n, 0) as average_ticnum
IFNULL(average_fatiguenum_n, 0) as average_fatiguenum
IFNULL(average_stressnum_n, 0) as average_stressnum
IFNULL(sum_nf_sugars_n, 0) as sum_nf_sugars
IFNULL(sum_nf_total_carbohydrate_n, 0) as sum_nf_total_carbohydrate
FROM (
SELECT DISTINCT user_id,
date
FROM (
SELECT user_id,
date
FROM track_ticseverity
UNION
SELECT user_id,
date
FROM track_fatigue
UNION
SELECT user_id,
date
FROM track_stress
UNION
SELECT user_id,
date
FROM track_nutrition
) as combined
) as dates
LEFT JOIN (
SELECT user_id,
date,
AVG(ticnum) as average_ticnum_n
FROM track_ticseverity
GROUP BY user_id,
date) as grp_ticseverity
ON dates.date = grp_ticseverity.date
AND dates.user_id = grp_ticseverity.user_id
LEFT JOIN (
SELECT user_id,
date,
AVG(fatiguenum) as average_fatiguenum_n
FROM track_fatigue
GROUP BY user_id,
date) as grp_fatigue
ON dates.date = grp_fatigue.date
AND dates.user_id = grp_fatigue.user_id
LEFT JOIN (
SELECT user_id,
date,
AVG(stressnum) as average_stressnum_n
FROM track_stress
GROUP BY user_id,
date) as grp_stress
ON dates.date = grp_stress.date
AND dates.user_id = grp_stress.user_id
LEFT JOIN (
SELECT user_id,
date,
SUM(nf_sugars) as sum_nf_sugars_n,
SUM(nf_total_carbohydrate) as sum_nf_total_carbohydrate_n
FROM track_nutrition
GROUP BY user_id,
date) as grp_nutrition
ON dates.date = grp_nutrition.date
AND dates.user_id = grp_nutrition.user_id
WHERE dates.user_id = 1
ORDER BY dates.date;
请注意，当没有数据时，您将在某些列中获得 0 值特定日期.如果您更喜欢获取 NULL，请从这些列中删除 Nvl()在上面的查询中.
Note that you will get 0 values in some of the columns when there is no data for that
particular date. If you prefer to get NULL instead, remove the Nvl() from those columns
in the query above.
然后，为了在 0 - 10 的范围内标准化所有数据，您可以查看最大值找到每种类型的值并将其用于转换，或者如果您事先知道每种类型的范围是什么，那么最好使用该信息，并且也许也可以在 SQL 中编码.
Then, to normalize all data on a 0 - 10 scale, you could look at the maximum found for each type of value and use that for a conversion, or if you know beforehand what the ranges are per type, then it is probably better to use that information, and maybe code that in the SQL as well.
然而，在实际使用的图形中组合值总是看起来有点奇怪不同的尺度.人们很容易用这样的图表得出错误的结论.
However, it always looks a bit odd to have values combined in a graph that actually use different scales. One might easily jump to wrong conclusions with such graphs.
这篇关于使用 PHP 将 MySQL 查询转换为 JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
