Need help on counting recordset

[jomama]

I have a client table and a transaction table and the relationship is one client to many transaction. So, for each client, I have a page that views each transaction at a time.

Here's what I want to do:

On the page that views a particular transaction I want to display the following:
"2nd Transaction"

Where the "2nd" is determined by querying the DB for all transactions by client in order by date and somehow programmatically determining whether the transaction is the client's 1st, 2nd or 3rd...etc.

Any clue how to do this??😕

Thanks in advance for your advice

[LordShryku]

Easiest thing to do would be to add another field to the transactions table and increment everytime there's a transaction against the client with something like this

$sql = "SELECT max(trans_number) AS lastTrans
        FROM   transactions
        WHERE client = '".$client."'";
$res = mysql_query($sql);
if(mysql_num_rows($res) > 0) {
   $num = mysql_fetch_array($res);
   $lastTrans = $num['lastTrans'];
}
else {
   $lastTrans = 0;
}

$newTransNumber = ($lastTrans + 1);