PHP DELETE immediately after select

Question

I have a PHP server script that SELECTs some data from a MySQL database.

As soon as I have the result from mysql_query and mysql_fetch_assoc stored in my own local variables, I want to delete the row I just selected.

The problem with this approach is that it seems that PHP has done pass-by-reference to my local variables instead of pass-by-value, and my local variables become undefined after the delete command.

Is there anyway to get around this? Here is my code:

    $query="SELECT id, peerID, name FROM names WHERE peer = $userID AND docID = '$docID' AND seqNo = $nid";
    $result = mysql_query($query);

    if (!$result)
        self::logError("FAIL:1 getUsersNamesUpdate() query: ".$query."\n");     

    if (mysql_num_rows($result) == 0)
        return array();

    $row = mysql_fetch_assoc($result);
    $result = array();
    $result["id"] = $row["id"];
    $result["peerID"] = $row["peerID"];
    $result["name"] = $row["name"];

    $query="DELETE FROM names WHERE id = $result[id];";
    $result = mysql_query($query);

    if (!$result)
        self::logError("FAIL:2 getUsersNamesUpdate() query: ".$query."\n");         

    return $result;

Answer 1

You are overwriting your $result variable with your second statement:

$query="DELETE FROM names WHERE id = $result[id];";
$result = mysql_query($query); // result does not contain the array anymore

Change the name to something else. It has nothing to do with call-by-reference or such.

---

Actually, your first assignment of the values is unnecessary as $row is already an array:

$row = mysql_fetch_assoc($result);
$result = array();
$result["id"] = $row["id"];
$result["peerID"] = $row["peerID"];
$result["name"] = $row["name"];

You could just do:

$row = mysql_fetch_assoc($result);
// at the end
return $row;

Then you don't even have to change your variable name for the second statement. But consider to use meaningful variable names.

Answer 2

First of all, why not just use only one query to delete the row that interests you ?

Something like this should do the trick, I suppose :

delete 
from names
where peer = $userID 
  AND docID = '$docID' 
  AND seqNo = $nid

Of course, don't forget to escape/convert the values that should be ;-)

This way, no need for a select query, followed by a delete one.

Second : to make your code more easier to read / understand / maintain, you should probably not re-use the same variable for several different purposes.

Here, your $result variable is used for more than one thing, and it makes things harder to understand :

• resource returned by the first mysql_query
• then, array containing data from the first row
• then, resource returned by the second mysql_query

It's a bit confusing, and will, one day or another, lead to errors...
Actually, it already has ;-) : the third assignment is overriding the data you're getting with the second ones, and boom, you've lost the information that corresponds to the row you've just deleted ;-)