Postgres Update

[smti]

Hi Folks,

 

I am designing a work ticket system for my office. Right now, those who file a work ticket need to know where they are in the work queue. To accomplish this, I have created a field called pqv (Present Queue Value). When the person enters a work ticket, they are assigned their queue value.

 

Now the problem: Once a ticket is closed, the PQVs for all the other open tickets need to be adjusted accordingly (By subtracting 1 from their PQV).  I have written a query to display all open records and their corresponding queue values; however is displayed and updated, not all of them.

 

Here is my code:

 

function adjust_queue(){

include("../../../includes/connection.inc.php");

//Get Queue Values for those still in the database.		
$sql = "SELECT ticketid, pqv FROM tickets where status = 'In Queue'";
$result = pg_query($dbh, $sql);
if (!$result) {
     die("Error in SQL query: " . pg_last_error());
}
  
// iterate over result set
// print each row
while ($row = pg_fetch_array($result)) {
  
  echo "Ticket ids are:" .$row[ticketid];
  echo "<br>";
  echo "Queue Values are:" .$row[pqv];
  echo "<br>";
  

  
//Subtract one from present queue value of each student in queue.
$present_queue_value=$row['pqv'];
$new_queue_value = $present_queue_value-1;

//Excute update query with new queue value
$ticketid=$row['ticketid'];
$query = "UPDATE tickets SET pqv='$new_queue_value' where ticketid='$ticketid'";
}  
  
}
//Send Confirmations to student
  send_closing_confirmation();
}

 

 

When the TicketIDs and PQVs are echo'd only one record is displayed. It seems that if I remove the update query and only display open records all records in the database are show properly.

 

 

Any help would be greatly appreciated!

 

 

Thank You,

 

smti

[btherl]

Where is the code to execute the update query with pg_query()?  When you execute it, do you use a different variable name instead of $result?

[smti]

Thanks.... I got it working.