SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Apr 2009
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Can't get Sql Update working

    Hi,
    I am a sql newbie.

    I cant get the update function to work.

    The echo function accurately displays the new data, but the table is not updated.

    Any help is greatly appreciated. Here's what I have:



    <?php

    $postciunum=$_POST["ciunum"];
    $postupdate=$_POST["updatedjobinst"];


    $conn=odbc_connect('sswebtest','','');
    if (!$conn)
    {exit("Connection Failed: " . $conn);}
    $sql=" UPDATE tblCustomer
    SET CJobInstructions = '$postupdate'
    WHERE CJobInvoice = '$postciunum'";


    odbc_close($conn);

    echo $postciunum;
    echo $postupdate;

    END;

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    All you did was assign the query text to a string, you wrote no code to execute the query against your database.

    I don't use ODBC, but looking at the manual, you're missing odbc_exec($conn, $sql).

  3. #3
    SitePoint Member
    Join Date
    Apr 2009
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, now i get the following error message:


    Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression "I like cheese'., SQL state 37000 in SQLExecDirect on line 14

    -----------------------Here is updated code ------------------------------

    <?php

    $postciunum=$_POST["ciunum"];
    $postupdate=$_POST["updatedjobinst"];


    $conn=odbc_connect('sswebtest','','');
    if (!$conn)
    {exit("Connection Failed: " . $conn);}
    $sql=" UPDATE tblCustomer
    SET CJobInstructions = '$postupdate'
    WHERE CJobInvoice = '$postciunum'";

    odbc_exec($conn,$sql);
    odbc_close($conn);

    echo $postciunum;
    echo $postupdate;

    END;
    ?>

  4. #4
    SitePoint Member
    Join Date
    Apr 2009
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I found that there was a ' in the inputed text causing the previous error. I added str_replace to eliminate the culprit. Now there is this error:

    Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC Microsoft Access Driver] Record is deleted., SQL state S1109 in SQLExecDirect on line 18

    ************************** Here is current code ****************

    <?php

    $postciunum=$_POST["ciunum"];
    $postupdate=$_POST["updatedjobinst"];

    $replace = array("'");
    $replacewith = array("_");
    $postupdate2 = str_replace($replace, $replacewith, $postupdate);


    $conn=odbc_connect('sswebtest','','');
    if (!$conn)
    {exit("Connection Failed: " . $conn);}
    $sql=" UPDATE tblCustomer
    SET CJobInstructions = '$postupdate2'
    WHERE CJobInvoice = '$postciunum'";

    odbc_exec($conn,$sql);
    odbc_close($conn);


    END;
    ?>

  5. #5
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You should use prepared statements rather than manually replace quotes and other characters that need escaping yourself:

    http://us.php.net/manual/en/function.odbc-prepare.php

    Is there a row in your database matching the WHERE condition of your update?

  6. #6
    SitePoint Member
    Join Date
    Apr 2009
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    Is there a row in your database matching the WHERE condition of your update?

    Yes, and even if I change the $ to a value it still retrieves the same error.

  7. #7
    SitePoint Member
    Join Date
    Apr 2009
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I figured it out!

    First -- Thank you, Dan, for the advise. You were spot on with the addition of the "odbc_exec($conn,$sql);" command.

    Second -- The error I was receiving was because I was using a copy of my database (so I wouldn't screw up everything on my existing database) and I found that the backup database did not have permissions for edit in the ODBC settings. I changed the permission setting and it is now working flawlessly.

    Thank you for your help!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •