Complex sql statement help

blackwidow

Hi guys,

I have the following sql statement:

$sql = @mysql_query("SELECT * FROM ipn_trans WHERE shipped='' ORDER BY time DESC;");

I have some key fields that are relevant to what I'm trying to do:

shipped - will either be 'yes' or empty
txn_id - will contain a transaction id from paypal
payment_status - will either be 'Pending', 'Failed', or 'Completed'.

I'm trying to list new transactions that havnt been shipped yet, hence the above statement. (I manually update 'shipped' when I've posted the order off)

However, if a transaction is 'Pending', then it changes to 'Completed', a new record is inserted into the database with the same txn_id - so I end up displaying the same transaction twice (once for 'Pending', and once for 'Completed').

I'm in desparate need of an sql statement where I will only display the record in the database that has its status as 'Completed' ONLY if there is more than one record. I still want to show 'Pending' transactions as they come through.

I hope that made some sense!

Kind regards

Mark

Piranha

It seems like you have a design problem with the database. You should not generate one row for pending and another for completed. You should read up on database normalization to understand this, and redesign the database to handle these cases.

blackwidow

Hi Piranha, many thanks for your reply. I have read up on database normalisation, and it seems that to some degree my database is to requirements. I have two tables, one for the customer information (name, address, transaction information etc), and one for the product(s) sold to that customer with a parent id field matching the id field of the transaction table.

Its actually quite handy as it is, because I can view the history of a given transaction - i.e. when it was first started and set as pending, and then when it became completed and I recieved the funds.

However, I'm still stuck on how to solve my original problem. Thats not say that your comments werent gratefully recieved, but there isnt going to be thousands of transactions each month, possibly somewhere between 100-150 a year.

kind regards

mark

NogDog

If I understand the requirement correctly, I believe you could use a sub-query something like this:

SELECT it1.* 
  FROM ipn_trans AS it1 
  WHERE it1.payment_status = 'Completed' 
    AND (SELECT COUNT(*) FROM ipn_trans AS it2 WHERE it1.txn_id = it2.txn_id) > 1

blackwidow

Hi Charles,

Thats ace!! I worked a treat. I'm not going to pretend I fully understand what it all means as I'm no expert on mysql. However, I do get the gist of how the statement works.

I've added a little to it as you can see below (the 'shipped' part). Basically, it now says "if theres more than one record for that transaction id, only show the one that has its status set to 'Completed' - Fabulous........................However, how would I add to that statement to display 'normal' records for Completed transactions (i.e. for those transactions that were processed as Completed first time, and there is only one entry??

heres what I've got so far:

$sql_l = @mysql_query("SELECT * FROM ipn_trans AS it1 WHERE it1.payment_status = 'Completed' AND (SELECT COUNT(*) FROM ipn_trans AS it2 WHERE it1.txn_id = it2.txn_id) > 1 AND it1.shipped = ''");
if (!$sql_l) {
echo "<p>Unable to retrieve new transaction list</p>";}

while ($new_txn_list=mysql_fetch_array($sql_l)) {

$txn = $new_txn_list['txn_id'];
$status = $new_txn_list['payment_status'];

echo $txn . "   ->   " . $status . "  *<br>";

}

NogDog

I think I need some clarification here: do you want two separate queries, one for completed transactions with more than one record, and one for completed transactions with only one, or is all you really need is one query for all completed transactions?

blackwidow

Hi Charles,

Ideally I would like one query for all completed transactions only, those transactions would be the ones with singular, and multiple records (the ones with multiple records only being selected if they have their payment_status set as 'Completed').

This is in an ideal world, so I can log in to the backend of my site and see at first glance those transactions that have just arrived, and those that have cleared from their initial 'Pending' status.

If it cant be done then I might have to use two queries, and have two sections on the main admin page - one for cleared transactions and one for new Completed transactions.

Thanks for your help on this, its much appreciated. I've just been reading the Hard Knocks section on your site, very interesting reading, and certainly some great tips!

cheers

Mark

NogDog

Perhaps we're over-complicating things, and all you need is:

SELECT * FROM ip_trans WHERE shipped = '' AND payment_status = 'Completed'

Or am I still missing a piece of the puzzle?

blackwidow

Technically you are totally right................and I had done that to start off with. But I soon learned that I had no way of knowing at first glance which transactions had been 'Pending' to start off with.

I'm selling re-prints from clients weddings, and as such they take time to prepare and have to be sent off to a lab etc etc, so when I get a transaction thats 'Pending', I can start the prep work (anything from a few hours to a few days), and when that transaction has cleared I can then send the prepped files off to the lab for processing. Hence the need to display cleared transactions(with an asterix), and fresh completed transactions, in one place.

Using a slight variation of your statement, I have managed to display a 'Pending' list in one html table, and then when it has cleared it disappears from that list, and appears in the 'Completed' list using your original statement, but that list only shows the transactions that have cleared, and not new ones aswell.

I'm off to get a few hours kip now. I'll be back first thing though.

Once again, many, many thanks for your help!!

cheers

Mark

Piranha

As you say it is probably not worth the effort to totally normalize the database for that few records. And I don't think it's worth doing a strange looking query in the database either. Simply retreive all the information from the database, and handle the other stuff in PHP.

Just order the result by txn_id and payment_status, then check if it is a new txn_id or the same one.