Calculating the difference between rows in MYSQL using php?

[Mr Chris]

Hello,

 

I have a table with a number of records in it like this example, each with the same order_id

 

 

However this is just an example, there could be three records with the same order_id or there could be 33.

 

What I want to do using php is say if there is more than one record for an order_id work out the difference between the previous and the next row, as indicated on my skilfully drawn diagram between the two columns impressions and clicks :-)

 

However, i'm not at all sure how to go about this as it has to be dynamic and as I say I will never know how many rows are in the database for each order_id?  Can anyone please help or is this not possible?

 

Thank you

 

[Psycho]

You don't state if you are going to be displaying records for multiple orders at one time or only for one order. I will assume multiple.

 

Here is a rough example to show colums for Order ID, impressions, clicks & difference

 

$current_order_id = 0;
while($row=mysql_fetch_assoc($result))
{
    if($current_order_id != $row['order_id'])
    {
        //New order, reset count
        $current_order_id = $row['order_id'];
        $first_clicks = true;
    }
    else
    {
        //Same order as last
        $first_clicks = false;
    }    

    //Calculate difference (if not first)
    $click_difference = ($first_clicks) ? '--' : ($row['clicks']-$last_clicks);
    $last_clicks = $row['clicks'];

    echo "<tr>\n";
    echo "<td>{$row['order_id']}</td>\n";
    echo "<td>{$row['impresssions']}</td>\n";
    echo "<td>{$row['clicks']}</td>\n";
    echo "<td>{$click_difference}</td>\n";
    echo "</tr>\n";
}

[Mr Chris]

Thanks