Insert if row doesn't exist

OK, firstly i know i havent checked user inputs and the code is a bit of a mess but its in testing at the moment, so its not a live system.

I have a table on one page, i'm using jQuery to go through each row and send the values as an array to a php page which goes through each row and updates the relevant row in the mysql db. Thtas great as long as you only 开发者_JAVA百科want to edit what you've got. If the user adds another row to the table, it will still send all rows but i need the php to insert if it doesnt find the relevant row to update, that make sense?

This is my code for the php update as it is:

if (isset($_POST['data']) && is_array($_POST['data'])) {
              foreach ($_POST['data'] as $row => $data) {
                $sql = "UPDATE `orders` SET supp_short_code='".$data['supp_short_code']."', project_ref='".$data['project_ref']."', om_part_no='".$data['om_part_no']."', description='".$data['description']."', quantity=".$data['quantity_input'].", cost_of_items='".$data['cost_of_items']."', cost_total='".$data['cost_total_td']."' where order_id = ".$data['order_id']." and row_id = ".$data['index']."";
                $result = mysql_query($sql); 
              }
            }

The "index" is the row index from the table on the first page if that helps?

First, that query is incredibly insecure. You should be using mysql_real_escape_string.

Second, you could test for whether the index property exists, but that puts you at risk for bad user data (always assume that user data can be messed up). If rowid and orderid form a unique key on the table, then what you're probably better off doing is using ON DUPLICATE KEY UPDATE:

$sql = 'INSERT INTO `orders` VALUES (' . mysql_real_escape_string( $data['supp_short_code'] ) /* ... */ ' ON DUPLICATE KEY UPDATE ' // your original sql

Just create an IF statement to check whether $data['index'] exists. If not then create an insert statement rather than an update.

if (isset($_POST['data']) && is_array($_POST['data'])) {
          foreach ($_POST['data'] as $row => $data) {
            if($data['index']){
              $sql = "UPDATE `orders` SET supp_short_code='".$data['supp_short_code']."', project_ref='".$data['project_ref']."', om_part_no='".$data['om_part_no']."', description='".$data['description']."', quantity=".$data['quantity_input'].", cost_of_items='".$data['cost_of_items']."', cost_total='".$data['cost_total_td']."' where order_id = ".$data['order_id']." and row_id = ".$data['index']."";
            } else {
              $sql = /*Create INSERT statement*/;
            }
              $result = mysql_query($sql); 
          }
        }

Since we are dealing with mysql, REPLACE might be of use here. http://dev.mysql.com/doc/refman/5.0/en/replace.html