Appending suffix to duplicate rows

[karenn1]

Hey everyone,

 

First off, sorry if this is maybe in the wrong forum. I wasn't sure where to post it.

 

I have a system where users can send a parcel through adding all their info to several pages. This gets recorded in rows into my database with a unique ID number and billing ID for each entry.

 

I'm now adding functionality where a user can send multiple parcels in one go. The data gets saved again with a unique ID but this time all the parcels the user wants to send gets the same billing ID. This now becomes a batch of parcels hence why the billing ID is the same.

 

For the administrator to handle these parcels (and to know that it's a batch), I have a grid already set up displaying the ID, dimensions, billing ID, etc. for all the parcels recorded. What I basically want to do now, is display the billing ID but I want to add "-1", "-2", "-3", etc. depending how many parcels there are in that batch. I want to write this into the database so that each unique ID gets the right billing ID with the new suffix attached. So this is probably a combination of PHP and PostgreSQL.

 

I tried using this query in a while loop:

 

SELECT add_session_id, COUNT(add_session_id)
from waybill
GROUP BY add_session_id
HAVING COUNT(add_session_id) > 1
ORDER BY add_session_id

 

but this returns only one entry (with the billing ID, and how many records it counted). So this doesn't work for me.

 

Can anybody please help me to implement this?

 

 

 

Thanks!

Karen

[Wolphie]

From what I can understand, I would suggest something like the following..

 

<?php
$query = "SELECT COUNT(*) as Num FROM `table` WHERE `billing_id` = '$billing_id'";
$result = mysql_query($query) or trigger_error(mysql_error());

for($a = 0; $a < $result['Num']; $a++)
  print $billing_id . '-' . $a;

?>

 

 

[karenn1]

Hi Wolphie,

 

Thanks for the reply. I tried the code below but nothing gets printing. When I tested the query, Num comes out as 0.

 

 

 

Any ideas?

 

 

[Wolphie]

Did you change the necessary values? Such as table name, column name, etc..?

Please post the relevant code you're using.

[karenn1]

Yes, I did change the relevant values. Here's my code:

 

$query = "SELECT COUNT(*) as Num FROM public.waybill WHERE add_session_id = '$add_session_id'";
$result = pg_query($query);

for($a = 0; $a < $result['Num']; $a++) {
  		print $add_session_id . '-' . $a;
}

[Wolphie]

Add some error handling, it may indicate whats wrong.

 

$result = pg_query($query) or trigger_error(pg_error()); // I don't know what the PostgreSQL error function is, I don't use it sorry.