Loop through orders database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
djlex1928
Forum Commoner
Posts: 31
Joined: Sun Sep 19, 2010 3:23 pm

Loop through orders database

Post by djlex1928 »

Ok, I have been playing around with this for a little while now and it's really bugging me.

I have a database similar to below (i've just shown a few columns for your convenience, just so you can understand what i'm trying to do)
id / orderid / cost / status
1 / 59268 / 58.36 / STOCK
2 / 59268 / 24.30 / ONORDER
3 / 56234 / 97.26 / STOCK
4 / 56234 / 43.22 / STOCK

Basically in the above example I have 2 customers that have ordered 2 parts each. In the first order (59268) the first item is kept in stock but the second had to be ordered in.

It's the second order I'm struggling with (56234)

Heres a written piece of what I want to do

SELECT * FROM orders (the db above)
Loop through all rows
If all rows with the same orderid have a status "STOCK" do something like $i++ (i can work with that)

So the result on this code should be $i = 1 (one order complete) and if all rows in the db status='STOCK' then $i = 2.


I've dabbled with a few different techniques to accomplish this but I'm pretty sure every way I tried was far from the right way.

Can anyone show me the best way to do this?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Loop through orders database

Post by Celauran »

Not particularly elegant, but should hopefully get you started:

Code: Select all

$sql = "SELECT *
        FROM orders
        ORDER BY orderid, status";
$res = mysql_query($sql);

$orders = array();
while ($row = mysql_fetch_assoc($res))
{
    if (!isset($orders[$row['orderid']]))
    {
        $orders[$row['orderid']][] = $row['orderid'];
    }
    $orders[$row['orderid']][] = $row['status'];
}

$completed = 0;
foreach ($orders as $order)
{
    $filled = 1;
    for ($i = 1; $i < count($order); $i++)
    {
        if ($order[$i] == "ONORDER")
        {
            $filled = 0;
        }
    }
    if ($filled == 1)
    {
        $completed++;
    }
}

echo $completed;
djlex1928
Forum Commoner
Posts: 31
Joined: Sun Sep 19, 2010 3:23 pm

Re: Loop through orders database

Post by djlex1928 »

Thanks for your reply!

Your code works perfectly, I just wish I bloody understood how! lol :D I will have a look over your code this evening.

Thanks again!
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Loop through orders database

Post by Celauran »

I've added in some comments, which will hopefully help explain what's going on.

Code: Select all

$sql = "SELECT *
        FROM orders
        ORDER BY orderid, status";
$res = mysql_query($sql);

// We're going to create a multi-dimensional array to store the results in
// Ultimately, we want:
// $orders[59268][59268]
//        [59268][STOCK]
//        [59268][ONORDER]
//        [56234][56234]
//        [56234][STOCK]
//        [56234][STOCK]
$orders = array();
while ($row = mysql_fetch_assoc($res))
{
    // If the current orderid isn't in the array, add it to the array
    if (!isset($orders[$row['orderid']]))
    {
        $orders[$row['orderid']][] = $row['orderid'];
    }
    // Now that we know the orderid is in the array, we'll add its status
    $orders[$row['orderid']][] = $row['status'];
}

$completed = 0;
// Since we're using orderid as our first index, we can loop through the array
// by orderid
foreach ($orders as $order)
{
    // Since any one part on order means the order isn't ready, we'll set ready
    // to be the default state. 1 = ready, 0 = not ready.
    $filled = 1;
    // Now we go through each item in the order
    for ($i = 1; $i < count($order); $i++)
    {
        // As soon as we find one item on order, the whole order is not ready
        if ($order[$i] == "ONORDER")
        {
            $filled = 0;
        }
    }
    // If we've made it this far and the order is still marked as ready, then
    // we know every part was in stock and the whole order is ready, so we 
    // increase the 'ready order' counter
    if ($filled == 1)
    {
        $completed++;
    }
}

echo $completed;
I still think the whole job could be done in SQL, I just don't know how.
djlex1928
Forum Commoner
Posts: 31
Joined: Sun Sep 19, 2010 3:23 pm

Re: Loop through orders database

Post by djlex1928 »

Thanks a million bud! It was the array I was struggling to understand, I've never dealt with creating multidimensional arrays on the fly.

Thanks for breaking it all down for me! :D
djlex1928
Forum Commoner
Posts: 31
Joined: Sun Sep 19, 2010 3:23 pm

Re: Loop through orders database

Post by djlex1928 »

Just a quick question that I'm not quite sure on.

I've always used mysql_fetch_array and to be honest I've never tried using anything else. I did try your code with mysql_fetch_array and it returned the same results, just wondering why, in this instance, you have used mysql_fetch_assoc.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Loop through orders database

Post by Celauran »

Personal preference, I guess. I very seldom use the numerical indices when dealing with SQL results, so I generally use mysql_fetch_assoc over mysql_fetch_array.
djlex1928
Forum Commoner
Posts: 31
Joined: Sun Sep 19, 2010 3:23 pm

Re: Loop through orders database

Post by djlex1928 »

Fair enough :D Thanks again for your help with this, much appreciated!
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Loop through orders database

Post by mikosiko »

please allow me to suggest a different alternative:

If the only thing that you want looping your records is detect if there are incomplete orders, why you instead of all that code (which is a good Celauran's suggestion) you don't do something like this (partial code):

Code: Select all

$query = "SELECT orderid, SUM(IF(status='ONORDER',1,0) AS needtoorder
             FROM orders
             GROUP BY orderid";

$res = mysql_query($sql) or die("Query Error : " . mysql_error());
while ($row = mysql_fetch_assoc($res))
{
    if (!$row['needtoorder']) 
   {
      echo "Order Id : " . $row['orderid'] . " has : " . $row['needtoorder'] . " Items pending to be ordered<br />";
   } else {
      echo "Order Id : " . $row['orderid'] . " has everything in stock <br />";
   }
} 
To me is much simple in this way
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Loop through orders database

Post by Celauran »

I knew it could be done in SQL, I just wasn't sure how. That's a much better way of doing it. Note, however, the missing parenthesis in the query string.

Code: Select all

SELECT orderid, SUM(IF(status='ONORDER',1,0)) AS needtoorder
FROM orders
GROUP BY orderid
djlex1928
Forum Commoner
Posts: 31
Joined: Sun Sep 19, 2010 3:23 pm

Re: Loop through orders database

Post by djlex1928 »

There you go Celauran, now you know how to do it in SQL! and so do I :)

Thanks!
Post Reply