[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

UPDATE instead of INSERT for MySQL table

I currently have code that inserts a new record into my database. I need to modify it to update a record where the id matches. I need help with the proper syntax.

I currently have:

INSERT INTO order_ord (id_ord, amount_ord, taxes_ord, shipping_ord, currency_ord, date_ord, status_ord, ordernumber_ord, firstname_ord, lastname_ord, email_ord, streetaddress_ord, city_ord, state_ord, country_ord, zip_ord, details_ord)
          VALUES ('$ordernumber', '$ordertotal', '$taxtotal', '$shipping', 'USD', '$orderdate', 'Confirmed', '$ordernumber', '$firstname', '$lastname', '$email', '$street1', '$city', '$state', '$country', '$zip', '$orderdetails')

I need to rewrite it to UPDATE order_ord WHERE id_ord == SESSION_ID

Can anybody help?

thanks.
0
befidled
Asked:
befidled
1 Solution
 
RoonaanCommented:
UPDATE order_ord SET amount_ord='$ordertotal', .., .. WHERE id_ord = $ordernumber

-r-
0
 
tolgaongCommented:
if your id_ord unique you can use this command it insert if it doesnt exists it adds or updated automatically...
replace order_ord set amount_ord='$ordertotal', id_ord=$ordernumber
0
 
ryanmauldinCommented:
UPDATE order_ord  SET amount_ord=$ordertotal, taxes_ord=$taxtotal, shipping_ord=$shipping, currency_ord='USD', date_ord='$orderdate', status_ord='Confirmed', ordernumber_ord=$ordernumber, firstname_ord='$firstname', lastname_ord='$lastname', email_ord='$email', streetaddress_ord='$street1', city_ord='$city', state_ord='$state', country_ord='$country', zip_ord='$zip', details_ord='$orderdetails' WHERE id_ord='$SESSION_ID'

Here i left off ' ' around the variable if it was to be treated as a number instead of a string.  If your SESSON_ID (which i treated as a variable $SESSION_ID) is not a string take off the ' ' around it.  It will prob end up looking like this in your code.

$result = $conn->query("UPDATE order_ord  SET amount_ord=$ordertotal, taxes_ord=$taxtotal, shipping_ord=$shipping, currency_ord='USD',"
                    ." date_ord='$orderdate', status_ord='Confirmed', ordernumber_ord=$ordernumber, firstname_ord='$firstname',"
                    ." lastname_ord='$lastname', email_ord='$email', streetaddress_ord='$street1', city_ord='$city', state_ord='$state',"
                    ." country_ord='$country', zip_ord='$zip', details_ord='$orderdetails' WHERE id_ord='$SESSION_ID'");
if (DB::isError($result))
{
  echo "Query failed.";
  exit();
}
else
{
  // whatever else you have in mind
}
0
 
dktuCommented:
$aryValues = array('id' => $id_ord,
                             'amount_ord' =>$amount_ord,
                             ......
                            );

$sql = "update order_ord set "
foreach($aryValues as $key=>$val) {
   if ($key == 'id') continue;
   $sql .= $key . "='" . convertType($val)  . "',";
}
$sql = substr($sql, 0, strlen($sql)-1);
$sql .= " where id_ord='" .$aryValues['id'] . "'";
// execute your query

function convertType($x) {
// format your data type
// Your code
//....
}

Is that right?

Hopes this help

0
 
befidledAuthor Commented:
I'm still in the process of evaluating the solution that will work best. Please don't abandon.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now