Loop through orders database
Moderator: General Moderators
Loop through orders database
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?
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?
Re: Loop through orders database
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;Re: Loop through orders database
Thanks for your reply!
Your code works perfectly, I just wish I bloody understood how! lol
I will have a look over your code this evening.
Thanks again!
Your code works perfectly, I just wish I bloody understood how! lol
Thanks again!
Re: Loop through orders database
I've added in some comments, which will hopefully help explain what's going on.
I still think the whole job could be done in SQL, I just don't know how.
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;Re: Loop through orders database
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!
Thanks for breaking it all down for me!
Re: Loop through orders database
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.
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.
Re: Loop through orders database
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.
Re: Loop through orders database
Fair enough
Thanks again for your help with this, much appreciated!
Re: Loop through orders database
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):
To me is much simple in this way
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 />";
}
} Re: Loop through orders database
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 orderidRe: Loop through orders database
There you go Celauran, now you know how to do it in SQL! and so do I 
Thanks!
Thanks!