CODEWITHSUNDEEP
phppostmysqliinsert
jamesp777
jamesp777

Reputation: 51

PHP submit to insert and update data

I need my form to post either INSERT or UPDATE data, depending on whether the column already exists. The INSERT query works fine, but the UPDATE doesn't. I guess this would be because the initial sql query ($query) is failing, more than likely because it is not finding the $Unique_Ref POST data. If this is the case, how do I extract this single value from the $invoice_data array?

This is the form page code:

if (isset($_GET['success']) && empty($_GET['success'])) {echo 'Record saved'; } else {if (empty($_POST) === false && empty($errors) === true) {$invoice_data = array(
    'Unique_Ref'            => mysqli_real_escape_string($conn, $_POST['Unique_Ref']),
    'Supplier_Name'         => mysqli_real_escape_string($conn, $_POST['Supplier_Name']),
    'Supplier_Invoice_Ref'  => mysqli_real_escape_string($conn, $_POST['Supplier_Invoice_Ref']),
    'Office'                => mysqli_real_escape_string($conn, $_POST['Office']),
    'Loss_Surplus_Amount'   => mysqli_real_escape_string($conn, $_POST['Loss_Surplus_Amount']),
    'Loss_Surplus_Currency' => mysqli_real_escape_string($conn, $_POST['Loss_Surplus_Currency']),
    'Outcome'               => mysqli_real_escape_string($conn, $_POST['Outcome']));

    save_invoice($invoice_data);header('Location: invoices.php?success'); exit();
    } else if (empty($errors) === false) {echo output_errors($errors);} ?> *html form....*

This is the save_invoice() function:

function save_invoice($invoice_data) {
global $conn;
array_walk($invoice_data, 'array_sanitize');
$fields = '`' . implode('`, `', array_keys($invoice_data)) . '`';
$data   = '\'' . implode('\', \'', $invoice_data) . '\'';

$query  = mysqli_query($conn, "SELECT * FROM `invoicelog` WHERE `Unique_Ref` = '$Unique_Ref'");
$result = mysqli_num_rows($query);

if($result > 0) {
    mysqli_query($conn, "UPDATE `invoicelog` SET $fields = $data WHERE `Unique_Ref` = '$Unique_Ref'");
} else {
    mysqli_query($conn, "INSERT INTO `invoicelog` ($fields) VALUES ($data)");
}

Any suggestions would be very welcome. As I mentioned, the final INSERT query works fine, it's the UPDATE query which isn't functioning.

Upvotes: 1

Views: 170

Answers (4)

0yeoj
0yeoj

Reputation: 4550

Your have to pair your 

$fields = '`' . implode('`, `', array_keys($invoice_data)) . '`';
$data   = '\'' . implode('\', \'', $invoice_data) . '\'';

Edit

i've edited you code like this:

$fields = array_keys($invoice_data)[$i];
$data = $invoice_data;

to achieve whats below, and it works for me.. hmm..

End Edit

something like :

'fields[0] = $data[0], ...' //and so on, place it in a loop. or whatever you prefer..  :)

because when updating it's (field1 = value1, field2 = value)

Edit2

Actual code of testing: enter image description here

End Edit

maybe something like this will do?

$set_arr = array();
for ($i = 0; $i < count($data); $i++) 
{
    $set_arr[] = "`".array_keys($invoice_data)[$i]/*$fields*/."` = '".$data[$i]."' ";
}

$setString = implode(',', $set_arr);

/*
Result ]> `Unique_Ref` = '1' ,`Supplier_Name` = '2' ,`Supplier_Invoice_Ref` = '3' ,`Office` = '4' ,`Loss_Surplus_Amount` = '5' ,`Loss_Surplus_Currency` = '6' ,`Outcome` = '7' 
       ]> from my test
*/

then your update statement will be something like this:

"UPDATE `invoicelog` SET ($setString) `Unique_Ref` = '$Unique_Ref'

Hope i've helped you.. i'm outta here, Happy Coding Cheers!

Upvotes: 3

Amit M Lad
Amit M Lad

Reputation: 11

//mysqli_query($conn, "UPDATE invoicelog SET $fields = $data WHERE Unique_Ref = '$Unique_Ref'");

update below code with above line.

$str = '';
foreach($invoice_data as $field=>$val ){
    $str = $str.",".$field."=".$val;
}
$str = substr($str,1);
mysqli_query($conn, "UPDATE `invoicelog` SET $str WHERE Unique_Ref = '$Unique_Ref'");

Upvotes: 1

Alghi Fari
Alghi Fari

Reputation: 450

make sure insert quote (') for string value :

mysqli_query($conn, "UPDATE `invoicelog` SET $fields = '$data' WHERE `Unique_Ref` = '$Unique_Ref'");

Upvotes: 0

umka
umka

Reputation: 1655

You can't set multiple values to multiple fields within single expression like SET $fields = $data. You should set each value for each field separately: SET field1='value1', field2='value2', ...

Upvotes: 1

Related Questions