#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    5
    Rep Power
    0

    PHP | mySQL | JSON | Google Charts


    I have been working on getting PHP to pull from a MySQL database and display in JSON so that google charts can read my data and display a chart. I have actually gotten it to work a few different ways but I am having problems getting my php to output my info in the correct format. I have two issues really. I will start by showing my code.

    Here is the php that pulls from mysql and prints a json table to a page when called:

    PHP Code:
    $sql1 "SELECT res.id, res.rank, res.question_id, u.firstname, u.email, u.username, rp.submitted, rp.survey_id
    FROM 
    {questionnaire_response_rank} res,
    {questionnaire_response} rp,
    {questionnaire_attempts} att,
    {user} u
    WHERE
    res.response_id = rp.id 
    AND 
    att.rid = rp.id 
    AND 
    rp.survey_id = 40
    AND
    res.question_id = 177
    AND
    att.userid = u.id
    GROUP BY
    u.firstname
    "
    ;

    $sql2 "SELECT res.id, res.rank, res.question_id, u.firstname, u.email, u.username, rp.submitted, rp.survey_id
    FROM 
    {questionnaire_response_rank} res,
    {questionnaire_response} rp,
    {questionnaire_attempts} att,
    {user} u
    WHERE
    res.response_id = rp.id 
    AND 
    att.rid = rp.id 
    AND 
    rp.survey_id = 40
    AND
    res.question_id = 177
    AND
    att.userid = u.id
    "
    ;

    $userdata $DB->get_records_sql($sql1, array($USER->id));
    $userdata2 $DB->get_records_sql($sql2, array($USER->id));

    $cols=array();
    foreach(
    $userdata as $r)
    {
            
    $temp=array();
            
    $cols[]=array('label' => $r->firstname'type' => 'string');

    }

    $table['cols']=$cols;

    $rows=array();
    foreach(
    $userdata2 as $s)
    for(
    $s->firstname 0$s->firstname<1$s->firstname++){
    {
            
    $temp=array();
            
            
    $temp[]=array('v' => (float) $s->rank+1);
            
            
    $rows[]=array('c' => $temp);
    }}

    $table['rows']=$rows;

    $jsonTable json_encode($table);
    print (
    $jsonTable); 
    I left the SQL queries in there in case the answere is there.

    This is what it displays to my page:
    Code:
    {"cols":[{"label":"Alex","type":"string"},{"label":"mike","type":"string"},{"label":"Super","type":"string"},{"label":"test","type":"string"}],"rows":[{"c":[{"v":2}]},{"c":[{"v":3}]},{"c":[{"v":5}]},{"c":[{"v":5}]},{"c":[{"v":3}]}]}
    If a person answers a question more than once I want the output to look like this in the JSON
    Code:
    {"cols":[{"label":"Alex","type":"string"},{"label":"mike","type":"string"},{"label":"Super","type":"string"},{"label":"test","type":"string"}],"rows":[{"c":[{"v":2}]},{"c":[{"v":3}]},{"c":[{"v":5}]},{"c":[{"v":5},{"v":2},{"v":3}]},{"c":[{"v":3}]}]}
    as you can see in the red some values have been added to that entry. I believe the foreach statement is forcing all entries (even if they are from the same person) to show up as their own entity.

    Please let me know if you need to see anything else or need any more info.

    The second issue is the code the way I am pulling it now gives this error on screen.
    Invalid column label:firstname

    I am not sure it likes me pulling the data from the database in the columns section.

    Can anyone help me with at least the first issue I am having getting the data to display the way I need it in JSON?

    Thanks in advance
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    11
    Your first query as a bad group-by in it, you can't group by one column and then selecgt a different one because there may be several different values for that other table for each grouped value.

    As both queries are otherwise identlcal i guess that is one of your problems.

    I think it wiuld be easier to help if you jusr var-dump the array, instald of printing the JSON because the JSON is just one long string that doesn't make the structure very easy to read.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    5
    Rep Power
    0
    Originally Posted by Vinny42
    Your first query as a bad group-by in it, you can't group by one column and then selecgt a different one because there may be several different values for that other table for each grouped value.

    As both queries are otherwise identlcal i guess that is one of your problems.

    I think it wiuld be easier to help if you jusr var-dump the array, instald of printing the JSON because the JSON is just one long string that doesn't make the structure very easy to read.
    Thanks for the reply. Let me explain what I was trying to do there and maybe that will help you help me. I used the group by in the first query in order to only have the users names show up one time as I will only want them to show up once on the google chart.

    I didn't want to group the responses at all so that I can show if a user responds more than once. That is why I made the second sql query. Not sure if that is the way to go or not. Admittedly I am a novice with most of this stuff.

    As for doing a var dump I could do that if you would like to see it in that format, but the google charts specifically need it formatted in JSON in order to display properly.

    I hope this makes sense and helps.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    11
    JSON is just a different way to display and/or transmit an array, once you have the PHP array right the JSON verison of that will also be correct.

    I believe the foreach statement is forcing all entries (even if they are from the same person) to show up as their own entity.
    Well, there is nothing in your sonc query or your look to merge any data the belongs to the same user, so I guess the net question is what are you looking for exactly?

    Comments on this post

    • glazebro agrees
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    5
    Rep Power
    0
    Originally Posted by Vinny42
    JSON is just a different way to display and/or transmit an array, once you have the PHP array right the JSON verison of that will also be correct.



    Well, there is nothing in your sonc query or your look to merge any data the belongs to the same user, so I guess the net question is what are you looking for exactly?
    Thanks again for the response and sorry for any confusion. What I want is to get the number data to be linked to a specific user.

    it needs to look like this according to google
    Code:
    {"cols":[{"label":"Alex","type":"string"},                      
                         {"label":"mike","type":"string"},
                         {"label":"Super","type":"string"},
                         {"label":"test","type":"string"}],
               "rows":[{"c":[{"v":2}]},
                          {"c":[{"v":3}]},
                          {"c":[{"v":5}]},
                          {"c":[{"v":5}]},
                          {"c":[{"v":3}]}]}
    and that is actually what I have right now, but two of the row values belong to one of the names in the columns section. The rows are supposed to correspond to the names above. So it should look like this:
    Code:
    {"cols":[{"label":"Alex","type":"string"},                      
                         {"label":"mike","type":"string"},
                         {"label":"Super","type":"string"},
                         {"label":"test","type":"string"}],
               "rows":[{"c":[{"v":2}]},
                          {"c":[{"v":3},{"v":5}]},
                          {"c":[{"v":5}]},
                          {"c":[{"v":3}]}]}
    notice there are 4 columns that are associated with 4 rows. One of the rows has 2 values because that user has filled out a question more than one time.

    I hope this makes sense. I am pretty confused, but I think I just need to figure out how to format it correctly with the php? use a loop or something in order to get row values coming from the same user to show up like the second row above.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    11
    Hmm... so you need to group answers by the same user into the same row.

    You could give the rows an index that idfentifies the user, so you can go back to the same row later to add an answer.

    Assuming that the email defines a unique user, you could do something like:

    PHP Code:

    $rows
    =array(); 
    foreach(
    $userdata2 as $s
    for(
    $s->firstname 0$s->firstname<1$s->firstname++){ 

    if (!isset(
    $rows[$s-email])
    {
            
    $temp=array();       
            
    $temp[]=array('v' => (float) $s->rank+1); 
            
    $rows[$s->email]=array('c' => $temp); 
    }
    else
    {
            
    $temp=array();       
            
    $temp[]=array('v' => (float) $s->rank+1); 
            
    $rows[$s->email]['c']=$temp
    }
    }


    foreach(
    $rows as $arrRow)
    {
      
    $table['rows'][] = $arrRow;

    That is: go through the answers and see if there is already a row for that user. If there is not: create row like you normally would, but use the email as an index so you can find tit again.
    If there is already a record; add the current answer to it.
    At the end you have to loop through the entire set again to get rid of the email key, because you don't want that in the json.

    Comments on this post

    • glazebro agrees
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    5
    Rep Power
    0
    Originally Posted by Vinny42
    Hmm... so you need to group answers by the same user into the same row.

    You could give the rows an index that idfentifies the user, so you can go back to the same row later to add an answer.

    Assuming that the email defines a unique user, you could do something like:

    PHP Code:

    $rows
    =array(); 
    foreach(
    $userdata2 as $s
    for(
    $s->firstname 0$s->firstname<1$s->firstname++){ 

    if (!isset(
    $rows[$s-email])
    {
            
    $temp=array();       
            
    $temp[]=array('v' => (float) $s->rank+1); 
            
    $rows[$s->email]=array('c' => $temp); 
    }
    else
    {
            
    $temp=array();       
            
    $temp[]=array('v' => (float) $s->rank+1); 
            
    $rows[$s->email]['c']=$temp
    }
    }


    foreach(
    $rows as $arrRow)
    {
      
    $table['rows'][] = $arrRow;

    That is: go through the answers and see if there is already a row for that user. If there is not: create row like you normally would, but use the email as an index so you can find tit again.
    If there is already a record; add the current answer to it.
    At the end you have to loop through the entire set again to get rid of the email key, because you don't want that in the json.
    I think this is very close to what I need. one of the values of the rows disappeared with this code though. I will keep playing with it to see if I can get it to display properly but here is what came out:

    Code:
    "rows":[{"c":[{"v":3}]},{"c":[{"v":5}]},{"c":[{"v":5}]},{"c":[{"v":3}]}]}
    I know that one of the emails has two entries associated with it.

    also i changed the code you gave me very slightly but thought I would repost. my editor said there were syntax errors so I did my best to fix those. Not trying to sound like I am complaining about the syntax errors just want to make sure I didn't mess anything up. Thanks again.

    PHP Code:
    $rows=array();
    foreach(
    $userdata2 as $s){
        if (!isset(
    $rows[$s->email]))

            
    $temp=array();        
            
    $temp[]=array('v' => (float) $s->rank+1);  
            
    $rows[$s->email]=array('c' => $temp);  
            

    else 

            
    $temp=array();        
            
    $temp[]=array('v' => (float) $s->rank+1);  
            
    $rows[$s->email]['c']=$temp;  
            
    }} 

    foreach(
    $rows as $arrRow

      
    $table['rows'][] = $arrRow

  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    5
    Rep Power
    0
    Hey I really appreciate the help but I was wrong I think about how it needs to be configured.

    I think in order for the chart to function correctly there would have to be blanks for the people that have not answered so the JSON would look something like this:

    {"c":[{"v":3},{"v":2},{"v":5},
    {"c":[{"v":5},{"v":},{"v":},]}]}';

    the first line would be the first answer given by all three. The second line is the second answer given by the first user in the list. The second and third user in the list haven't answered the question so theirs are blank. They may not even be there at all like this:

    {"c":[{"v":3},{"v":2},{"v":5},
    {"c":[{"v":5},]}]}';

    Sorry for my confusion on how the chart works and I wish I could show it to you but it is in local dev. I understand if you don't want to continue helping since I effed up what my needs are. Thanks for all your help. I will give you a good rating if I can.

IMN logo majestic logo threadwatch logo seochat tools logo