PHP - build javascript code using results returned from MySQL

The records below are returned from MySQL table,

store_name            year_month       order_total
HeadOffice            2013-05             359.16
HeadOffice            2013-06             6.00
HeadOffice            2013-07             827.54

Sydney                  2013-06             632.80
Sydney                  2013-07             16.40
Sydney                  2013-12             316.40

Warehouse            2013-06             9.95
Warehouse            2013-07             73.90
Warehouse            2013-10             39.95

is there anyway I can build the following using while or foreach loops?

var store1=[['2013-05', 359.16], [2013-06',  6.00], ['2013-07', 827.54], ['2013-10', 0], ['2013-12', 0]];
var store2=[['2013-05', 0], [2013-06', 632.80], ['2013-07', 16.40], ['2013-10', 0], ['2013-12', 316.40]];
var store3=[['2013-05', 0], [2013-06', 9.95], ['2013-07',73.90], ['2013-10', 39.95], ['2013-12', 0]];

do {
      $year_month = $row_store_sales['$year_month'];
      $store_name = $row_store_sales['store_name'];
      $order_total = $row_store_sales['order_total'];

}while ($row_store_sales = mysql_fetch_assoc($store_sales));

I need to get all the different year_month and check for orderrs from each store?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
That code smell is attention getting...

do {

Open in new window

To me it looks like you're copying code you found on the internet or on DreamWeaver (one of the worst possible ways to learn PHP).  Why not try it from a professional perspective?  Use any of the learning resources shown in this article, especially the Welling/Thompson book.  It will put you light-years ahead of trial and error learning, I promise!
Dave BaldwinFixer of ProblemsCommented:
I'm curious what you are trying to do.  While I have written PHP code that populates javascript variables, it's not the first choice if you just need to display the data on a page.  PHP can do that directly.
sabecsAuthor Commented:
Thanks for your comments, I am trying to get the output from a MySQL table in a format that can be entered into to display charts/graphs on sales ...
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Dave BaldwinFixer of ProblemsCommented:
Ok, that's do-able.  I believe your dates will have to be converted to a simple number that represents a scale on the chart.
Julian HansenCommented:
Something like this might be what you are looking for.

Solution requires you to modify the recordset retrieval code using whatever library you are using.
It also assumes that the results are as depicted i.e. sorted by store name
// Counter for our var names
$i = 1;

// Fetch the first row so we can get the store name
$row = fetch(...);

// Keeps track of the store we are currently working with
// Set it to the first store
$current = $row['store_name'];

// A string to store the store values in
$values = '';

// Don't continue if no rows returned
if (!empty($row)) {

    // Loop through recordset using whatever library you 
    // are using. Modify fetch accordingly
    do {
        // If current store is not the same as the one we 
        // have been processing then output and reset
        if ($row['store_name'] != $current) {
            // Dump the line for the current store
            echo 'var store' . $i . '=[' . $values . '];';
            // Set the current to current store
            $current = $row['store_name'];
            // Increment our variable index
            // Reset the value string
            $values = '';
        // If the value string is not empty apend a comma
        $values .= ($values == '') ? '' : ',';
        // Add the element for this iteration
        $values .= "['" . $row['year_month'] . "'," . $row['order_total'] . "]";
    } while($row = fetch(..));
    // Finally output the last item
    echo 'var store' . $i . '=[' . $values . '];';

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sabecsAuthor Commented:
Thanks Julian, that is great....
Julian HansenCommented:
You are welcome - thanks for the points.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.