Query to get data and update at one time

[showman13]

Good Morning...

I was just wondering if it is possible to do a query based on the status of a record, and if one is found with the status of 'E'arned, then to return the relevant fields from that record while at the same time changing the status to 'P'laced...

I guess I'm asking if you can do a SELECT and a conditional UPDATE based on the results of the SELECT, in a single query..?

Sort of a combination of these two into a single query.

SELECT ent_id, mem_id, from_step, from_pos
FROM step1_reentry
WHERE status='E'
LIMIT 1

UPDATE step1_reentry
SET status='P', last_update='".$created."'
WHERE ent_id = '$entry'         

And then be able to do the processing based on the information retrieved from the query

Hope that makes sense

Any assistance would be greatly appreciated.

Douglas

[phorce]

Hello,

I dunno if this is what you mean, but I was thinking something like:

<?php

    // connection information

    $select_query = "SELECT ent_id, mem_id, from_step, from_pos FROM step1_reentry WHERE status='E'LIMIT 1";
    $select_result = mysql_query($select_query);
    if(mysql_affected_rows() == 1)
    {
        while($row = mysql_fetch_array($select_result))
        {
            // retrun the relivent fields.
            // but do it in variables, so like $name = $row['name'];
        }

        // NOW you can do the UPDATE statement.
    }

?>

I dunno if this is what you mean, or if it will help.. :)

[showman13]

Actually, I know I can do that, and do it all the time...

What I was asking is if it is possible to do both in a single query

Do the select, and if a record is found with the status of E, to change that status to P while sending back the data from the select...

[showman13]

Actually I think it would look something like this

  $sql_r="SELECT ent_id, mem_id
    FROM step1_reentry
    WHERE status='E'
    LIMIT 1
  ";
  $result_r=mysql_query($sql_r);
  if($row_r=mysql_fetch_array($result_r)){
    $member=$row_r[1];
    $sql_ur = "
        UPDATE step1_reentry
        SET status='P', last_update='".$created."'
        WHERE ent_id = '".$row_r[0]."'
    ";
    $result=mysql_query($sql_ur);
  }

Not sure if it should be 'if' or 'while' , or if it really matters in this case...

But am always looking for more efficient ways to accomplish what I need.

Thanks for your response

[diafol]

Trying to force multiple sqls into a single query doesn't always produce a more efficient method. Often the complexity can make it slower.

  $result_r=mysql_query("SELECT ent_id, mem_id
        FROM step1_reentry
        WHERE status='E'
        LIMIT 1");
  if(mysql_num_rows($result_r)){
        $row_r=mysql_fetch_array($result_r)){
        $member=$row_r[1];
        $sql_ur = mysql_query("UPDATE step1_reentry
            SET status='P', last_update='$created'
            WHERE ent_id = {$row_r[0]}");
        }
  }

I think that's it?