/    Sign up×
Community /Pin to ProfileBookmark

MySQL updates field on INSERT but not on UPDATE

Copy linkTweet thisAlerts:
Mar 03.2009

My database has a number of fields, among them Name and Nused, both of which are VARCHAR(30) latin1_swedish_ci not_null fields. The two following SQL commands are typical of what is causing my headache, as the “Oth” text is copied correctly into the database on the INSERT command, but after the UPDATE command, while the Name fields and Get fields are changed appropriately, the Nuser field is now blank. Whatever was in it before is blanked out.

[CODE]
INSERT INTO Precord (id,iref,lref,Get,Name,Nused,Date) VALUES (0,32,197,1,’William’,’Oth’,’2009-03-03 00:00:00′)

UPDATE Precord SET Get=-1,Name=’William’,Nused=’Oth’ WHERE id=20968
[/CODE]

to post a comment
PHP

9 Comments(s) ↴

Copy linkTweet thisAlerts:
@chazzyMar 03.2009 — is it "blank" or is it "null" ?
Copy linkTweet thisAlerts:
@JayhawkauthorMar 03.2009 — Well, it doesn't show as "null" in phpmyadmin, although I'm not altogether sure how phpmyadmin distinguishes between the two on display, but it is defined as a "not_null" field.
Copy linkTweet thisAlerts:
@chazzyMar 04.2009 — just for arguments sake, what happens when...

  • - You just update the single field IE UPDATE Precord SET Nused='Jimmy' WHERE id=20968

  • - You run the same original update, but use a different value IE UPDATE Precord SET Get=-1,Name='William',Nused='Jimmy' WHERE id=20968


    • Either way it seems like a very odd issue. What version of MySQL are you using?
    Copy linkTweet thisAlerts:
    @JayhawkauthorMar 04.2009 — Good suggestions. Even when updated with no other fields in SQL statement the issue remains, and value is inconsequent.

    Version is 4.1.22standard.

    The SQL statements in the orig post, when executed in phpmyadmin, do execute properly, and the "Nused" field does get updated with the expected data. It is only when executed by php that the failure occurs.
    [CODE]
     if ($_POST['id'] > 0)
    $Query = "UPDATE Precord SET Get=$Typ,Name='$Name',Nused='$Posn' WHERE id=" . $_POST['id'];
    else $Query = "INSERT INTO Precord (id,iref,lref,Get,Name,Nused,Date) VALUES (0,$Inum,$Lnum,$Typ,'$Name','$Posn','$D')";
    mysql_query($Query, $Link);
    echo $Query;
    [/CODE]
    I have tried error trapping the mysql_query() call; it throws no error.
    Copy linkTweet thisAlerts:
    @skywalker2208Mar 04.2009 — Have you tried printing out the queries to make sure all the variables are getting populated?
    [code=php]
    echo "UPDATE Precord SET Get=$Typ,Name='$Name',Nused='$Posn' WHERE id=" . $_POST['id'];

    [/code]
    Copy linkTweet thisAlerts:
    @JayhawkauthorMar 04.2009 — [CODE]echo $Query;[/CODE]
    Copy linkTweet thisAlerts:
    @skywalker2208Mar 04.2009 — does printing out the query return the expected results?
    Copy linkTweet thisAlerts:
    @JayhawkauthorMar 04.2009 — The results of the echo $Query; are in my original post.
    Copy linkTweet thisAlerts:
    @chazzyMar 04.2009 — if it works in PHP My Admin it sounds like it's more of a PHP issue so off it goes. the only other thing i can think of is to try putting single quotes (') around the value of the Get column, even though they shouldn't be required.
    ×

    Success!

    Help @Jayhawk spread the word by sharing this article on Twitter...

    Tweet This
    Sign in
    Forgot password?
    Sign in with TwitchSign in with GithubCreate Account
    about: ({
    version: 0.1.9 — BETA 5.19,
    whats_new: community page,
    up_next: more Davinci•003 tasks,
    coming_soon: events calendar,
    social: @webDeveloperHQ
    });

    legal: ({
    terms: of use,
    privacy: policy
    });
    changelog: (
    version: 0.1.9,
    notes: added community page

    version: 0.1.8,
    notes: added Davinci•003

    version: 0.1.7,
    notes: upvote answers to bounties

    version: 0.1.6,
    notes: article editor refresh
    )...
    recent_tips: (
    tipper: @AriseFacilitySolutions09,
    tipped: article
    amount: 1000 SATS,

    tipper: @Yussuf4331,
    tipped: article
    amount: 1000 SATS,

    tipper: @darkwebsites540,
    tipped: article
    amount: 10 SATS,
    )...