put JS code in a PHP while loop

Question

i have some JS Code for displaying graph data:

series: [{
                name: 'Year 1800',
                data: [107, 31, 635, 203, 2]
            }, {
                name: 'Year 1900',
                data: [133, 156, 947, 408, 6]
            }, {
                name: 'Year 2008',
                data: [973, 914, 4054, 732, 34]
            }]

i need to get the data to display in a while loop in PHP. I have tried this:

<?php
                $sql="SELECT *, COUNT(category) AS my_groupcount from tickets where deleted = '' and DAY(datetime) = '".$day."' and MONTH(datetime) = '".$month."' and YEAR(datetime) = '".$year."' group by category order by datetime ASC ";
                $rs=mysql_query($sql,$conn);
                while($result=mysql_fetch_array($rs))
                {
                    echo "name: 'Cat ".$result["category"]."',";
                    echo "data: [".$result["my_groupcount"]."]";
                    echo "}, {";
                }
                ?>

i need to group the category column in the tickets table and display the graphs for each category but its not working - i think its because in the while loop its ending with }, { but i need it to end with }]

how can i get round this - the amount of category items in the tickets table is changing all the time as users are able to add/delete categories.

Look into PHPs json_encode. You really shouldn't write json by hand – what if the category is called New and/or 'chic' as I had recently… — Rich Bradshaw
– Rich Bradshaw, Commented Sep 24, 2013 at 13:56

score 4 · Accepted Answer · 2013-09-24 14:17:14Z

4

Why not do it like this:

<?php
    $sql = "[.. SQL Statement ..]";
    $rs = mysql_query($sql, $conn);
    $json = array();

    while($result = mysql_fetch_array($rs)) {
        $json[] = array( 
            'name' => 'Cat '. $result['category'],

            // This does assume that my_groupcount is an array with numbers
            // i.e. array(1, 34, 54, 345)
            // If not, you'll have to make it an array by doing:
            // explode(', ', $result['my_groupcount'])
            // This however does assume that the numbers are in 
            // the "12, 23" format
            'data' => $result['my_groupcount'],
        );
    }             

    echo json_encode($json);

edited Sep 24, 2013 at 14:17

answered Sep 24, 2013 at 13:57

user771071

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

1 Comment

user771071 Over a year ago

A quick note I want to add here: The JSON you display in your example is invalid, as it doesn't validate well in a JSON parser. If you want to check the JSON validation yourself, please take a look here: jsonlint.com

Kamil Szot · Accepted Answer · 2013-09-25 06:50:48Z

1

<?php
            $sql="SELECT *, COUNT(category) AS my_groupcount from tickets where deleted = '' and DAY(datetime) = '".$day."' and MONTH(datetime) = '".$month."' and YEAR(datetime) = '".$year."' group by category order by datetime ASC ";
            $rs=mysql_query($sql,$conn);
            $first = true;
            echo 'series: [{';
            while($result=mysql_fetch_array($rs))
            {
                if(!$first) {
                    echo "}, {";
                } else {
                    $first = false;
                }
                echo "name: 'Cat ".$result["category"]."',";
                echo "data: [".$result["my_groupcount"]."]";
            }
            echo '}]';
?>

edited Sep 25, 2013 at 6:50

answered Sep 24, 2013 at 14:12

Kamil Szot

17.9k8 gold badges65 silver badges67 bronze badges

Comments

aynber · Accepted Answer · 2013-09-24 13:57:30Z

0

Bookend the brackets instead, though it's better to build it then echo it so you can get rid of the last comma.

$string = '';
while($result=mysql_fetch_array($rs))
{
    string.= "{";
    string.= "name: 'Cat ".$result["category"]."',";
    string.= "data: [".$result["my_groupcount"]."]";
    string.= "},";
}
$string = trim($string,','); // gets rid of that last comma

echo "[$string]";

answered Sep 24, 2013 at 13:57

aynber

23.2k9 gold badges57 silver badges69 bronze badges

Comments

mic · Accepted Answer · 2013-09-24 13:59:05Z

0

try

$sql="SELECT *, COUNT(category) AS my_groupcount from tickets where deleted = '' and DAY(datetime) = '".$day."' and MONTH(datetime) = '".$month."' and YEAR(datetime) = '".$year."' group by category order by datetime ASC ";
$rs=mysql_query($sql,$conn);
$output = '[';
while($result=mysql_fetch_array($rs))
{
    $output .= "name: 'Cat ".$result["category"]."',";
    $output .= "data: [".$result["my_groupcount"]."]";
    $output .= "}, {";
}

$output = substr($output, 0, -3) . ']';

but as rich said you really shouldn't be writing JSON by hand.

answered Sep 24, 2013 at 13:59

mic

1,2732 gold badges15 silver badges35 bronze badges

Collectives™ on Stack Overflow

put JS code in a PHP while loop

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related