Setting status of other rows after INSERT

Question

Hey, I have a field called STATUS and it is either 1 to show or 0 to hide. My code is below. I am using an edit in place editor with jQuery. Everytime you update it creates a new ROW which I want, but I want only the new one to have STATUS = 1 and the others to 0. Any ideas on how I would do that?

<?php
    include "../../inc/config.inc.php";
    $temp  = explode("_", $_REQUEST['element_id'] );
    $field = $temp[0];
    $id    = $temp[1];
    $textboxval = stripslashes(mysql_real_escape_string(preg_replace('/[\$]/',"",$_REQUEST["update_value"])));      
    $query      = "INSERT INTO notes ($field,status,date,c_id) VALUES ('$textboxval','1',NOW(),'$id')";
    mysql_query($query);
    echo($_REQUEST['update_value']);
?>

Answer 1

I am not sure exactly what you mean - do you want to make all the entries except the new one have status = 0? If so, just issue an update before the insert:

UPDATE notes SET status = 0

However, I should also note that you have a potential SQL injection to worry about. By stripping slashes after applying "mysql real escape string", you are potentially allowing someone to put text in your SQL statement that will execute an arbitrary SQL statement.

Answer 2

Something like this, sorry for the post before, I mis read it the first time then went back:

<?php
    include "../../inc/config.inc.php";
    $temp  = explode("_", $_REQUEST['element_id'] );
    $field = $temp[0];
    $id    = $temp[1];
    $textboxval = mysql_real_escape_stringstripslashes((preg_replace('/[\$]/',"",$_REQUEST["update_value"]))); 
    // set older entries to 0 - to not show but show in history
    $hide_notes      = "UPDATE notes SET status = 0";
    mysql_query($hide_notes);

    // add new entry with status of 1 to show only latest note
    $query      = "INSERT INTO notes ($field,status,date,c_id) VALUES ('$textboxval','1',NOW(),'$id')";
    mysql_query($query);
    echo($_REQUEST['update_value']);
?>

i just ran in to a problem I didn't of the set up of my table doesn't allow me to show more than one client a time and i will be having numerous clients, my bad on planning ha

Answer 3

You really want to get the ID of the newly generated row and then trigger an UPDATE where you all rows where the ID is not the new row, e.g.

UPDATE notes SET status = 0 WHERE id != $newly_generated_id

If the ID column in your table is using AUTO_INCREMENT you can get its ID via "SELECT LAST_INSERT_ID()" and then use the return value in that statement in your UPDATE statement.

Pseudo code:

$insert = mysql_query("INSERT INTO ..."); 
$last_id = mysql_query("SELECT LAST_INSERT_ID()"); 
$update = mysql_quqery("UPDATE notes SET status = 0 WHERE id != $last_id");

The only caveat to this approach is where you might have a brief moment in time where 2 rows have status=1 (the time between your INSERT and the UPDATE). I would wrap all of this in a transaction to make the whole unit more atomic.