Incremental Update of Records

[karenn1]

Hey everyone,

 

I'm not sure if this is in the right forum but I need urgently help.

 

I have a PHP / PostgreSQL database recording the shipping of parcels, ie. as a user I can send a parcel with a certain width, height, length and weight, filling in all those details on a page. When sending a parcel, you get a unique number to use as a reference. In this case I used a random number between 0 - 1000 and the date. So each unique number looks similar to this: 9519-20080702.

 

The whole system is working fine but my client now wants extra functionality to send more than one parcel at a time. The idea is to have multiple entries for one person but the reference number must be indicative of the batch, ie. 9519-20080702-1, 9519-20080702-2, 9519-20080702-3, etc. depending on how many parcels have been added. I've been trying a while loop to do this to each record but it keeps on coming out 9519-20080702-0 for each one. Here's my code:

 

$i = 0;
	while($i <= 5) {

		$multiple_id = $id.'-'.$i;


		$sql_newid = "UPDATE public.waybill SET add_session_id = '".$multiple_id."' WHERE add_session_id = '".$id."'";
		$result_newid = pg_query($sql_newid);
		$rs_newid = pg_fetch_array($result_newid);

		$i++;		
	}

 

$id is my session id that I used to store the reference number. I think I know why the loop isn't working though. It's updating all the records with that reference number because I'm using a WHERE clause to fetch it. So next time the loop runs, it doesn't find the original number to update the next record. This has me stumped.

 

Can someone please help me?

 

 

 

 

Thanks!

Karen

[karenn1]

Can anybody please help?????

 

 

 

Thanks!

Karen

[br0ken]

$i should increment to but all five times you update the same record as it appears that $id never changes?! But still, rather than have five records with '-0' on the end that would tell me you should have one record with '-5' on the end! Try this and see what happens:

 

<?php

$i = 0;
while($i <= 5)
{

  $multiple_id = $id.'-'.$i;
  echo $multiple_id."<br />";

//  $sql_newid = "UPDATE public.waybill SET add_session_id = '".$multiple_id."' WHERE add_session_id = '".$id."'";
//  $result_newid = pg_query($sql_newid);
//  $rs_newid = pg_fetch_array($result_newid);

  $i++;		
}

?>

 

This should go through the loop and print out each $multiple_id so you can see if it's incrementing correctly.

[karenn1]

I know the increment works fine. I tested that using an echo but in each case when the query runs, it adds the last number in the range to my reference number. So, the echo looks like this:

 

1161-20080703-1

1161-20080703-2

1161-20080703-3

1161-20080703-4

1161-20080703-5

 

But what gets written into the database is this:

 

1161-20080703-5

1161-20080703-5

1161-20080703-5

1161-20080703-5

1161-20080703-5

 

In other words, the query I'm running is overwriting each one.

 

Can anybody shed some light on this?

 

 

[DarkWater]

Edit: Ignore that.  I misread what you were doing.

 

Does every entry in the "batch" have the same $id?

[karenn1]

Hi DarkWater,

 

Yes, each batch has the same $id. That's the reference number for each parcel. In this case, I just need "-1", "-2", etc after the id so that the administrator can see this is a batch. So $id becomes the same prefix in each case.

 

Does that make sense? I've tested again now and I'm sure my query is rubbish.

[DarkWater]

Alright.  The problem is that you're like, overwriting the add_session_id in the query while using it in the where clause...The logic makes no sense. =X

[karenn1]

I realise that my WHERE clause is overwriting it. But how do I fix it? How can I incrementally update my records?

 

Can you help me?

[DarkWater]

Maybe you should make another column called batch_number and have the incrementation in there?  And if there's only one parcel in a batch, it's simple 1, otherwise, it's 1, then 2, then 3, then 4, etc.