如果行不存在则插入

Question

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 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?

Answer 1

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); 
          }
        }