1

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.

Improve this question
3
  • 1
    Is your query successfully returning data? whats the problem? What is the output, explain properly. And what are the headers you are using? Commented Oct 31, 2015 at 17:38
  • Yes, the query is successfully returning data. The problem is that it is only returning data for dates that all four tables have info, instead of showing data from all dates even if one table has to data. It is also outputting a SUM for nf_sugars and nf_total_carbohydrate that is higher than the actual sum of the numbers in my database. I am not sure if it has something to do with the fact that I am joining the tables based on date even though some tables have multiple submissions per day and others do not. Commented Oct 31, 2015 at 17:44
  • Check your query on raw SQL and see if it is correct, check it on command line or PHPMyAdmin or something like that. You said it is only returning "data for dates" that means it is not returning correctly. Commented Oct 31, 2015 at 17:49

3 Answers 3

Reset to default
1

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.

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;

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.

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Thank you so much for your comment! When running this through PHPMyAdmin I get the following error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULL OUTER JOIN
Indeed, I now realise that MySql does not support FULL OUTER JOIN. I have adapted my answer to avoid its use.
I really appreciate your help. When running that I am getting the following error: #1248 - Every derived table must have its own alias
Now I am getting: #1054 - Unknown column 'sum_nf_sugars' in 'field list'
Corrected. There was a sum_nf_sugars_sum in there that should not have the _sum part at the end.
|
1

I will prefer to use this way (assuming all other things are working fine e.g query is working fine)

$query = mysqli_query($conn, $myquery);

if ( ! $query ) {
    echo mysqli_error($conn);  //You need to put $conn here to display error.
    die;
}

$data = array();

while($row = mysqli_fetch_assoc($query)) {
    $data[] = $row;
}

echo json_encode($data);     

mysqli_close($conn);
Improve this answer

Comments

0

if you are using PDO (PHP Data Objects) for database operations; then following code can be used to create the JSON from PDO.

$array = $pdo->query("SELECT * FROM employee")->fetchAll(PDO::FETCH_ASSOC);
echo json_encode($array);

For Multiple Select we need to try it like that way :

$array = $pdo->query("SELECT 1; SELECT 2;");
$array->nextRowset();
var_dump( $array->fetchAll(PDO::FETCH_ASSOC) );
Improve this answer

2 Comments

Thanks for the comment. I need to select from multiple tables. Is it possible to run multiple selections and return a single JSON Object using this method?
Can you show the example with more than one select statement? I am not sure how I would connect them.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.