UPDATE instead of INSERT for MySQL table

Posted on 2006-05-21
Last Modified: 2012-05-05
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?

Question by:befidled
    LVL 49

    Accepted Solution

    UPDATE order_ord SET amount_ord='$ordertotal', .., .. WHERE id_ord = $ordernumber

    LVL 9

    Expert Comment

    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
    LVL 1

    Expert Comment

    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.";
      // whatever else you have in mind
    LVL 1

    Expert Comment

    $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

    LVL 1

    Author Comment

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

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    The Client Need Led Us to RSS I recently had an investment company ask me how they might notify their constituents about their newsworthy publications.  Probably you would think "Facebook" or "Twitter" but this is an interesting client.  Their cons…
    I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
    Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
    The viewer will learn how to count occurrences of each item in an array.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now