SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    The Netherlands
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Updating awkwardly

    i execute an SQL statement:
    Code:
    UPDATE online 
    SET 
        loc=2,
        dos=1085514101,
        ip='127.0.0.1' 
    WHERE 
       id = 1
    after the execution the row looks like
    Code:
    +----+-----+-----+------------+-------------+
    | id | loc | rid | dos        | ip          |
    +----+-----+-----+------------+-------------+
    |  1 |   0 |   0 | 1085514101 | 192.168.1.1 |
    +----+-----+-----+------------+-------------+
    any ideas?

    also, it sometimes does what it's supposed to do, but most of the time not. The SQL query is in both cases exactly the same.
    Last edited by Sheqel; May 25, 2004 at 13:18.

  2. #2
    SitePoint Addict Viral's Avatar
    Join Date
    Nov 2001
    Location
    Washington DC
    Posts
    294
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What did it look like before? Can you show us the table structure (field types)? It could be that you are trying to update fields with information outside of the boundaries of the field type (i.e. setting loc to 129 when it is defined as a tinyint).

    --Viral
    A computer without Windows is like a chocolate cake without mustard.

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    The Netherlands
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ehm, not sure how it is going to help , but here's the structure:
    Code:
    +-------+-----------------+------+-----+---------+-------+
    | Field | Type            | Null | Key | Default | Extra |
    +-------+-----------------+------+-----+---------+-------+
    | id    | int(5) unsigned |      | MUL | 0       |       |
    | loc   | tinyint(4)      |      |     | 0       |       |
    | rid   | int(6) unsigned |      | MUL | 0       |       |
    | dos   | int(10)         |      |     | 0       |       |
    | ip    | char(15)        |      | MUL |         |       |
    +-------+-----------------+------+-----+---------+-------+
    and it is this very query that fails

  4. #4
    SitePoint Addict Viral's Avatar
    Join Date
    Nov 2001
    Location
    Washington DC
    Posts
    294
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, yup, that query shouldn't fail based on that structure. So in this example, the only thing that worked successfully was the 'dos' field? It looksl ike it updated with your info but the 'loc' and 'ip' fields did not.

    Do you get any errors? what happens if you try to update just a single field? like

    update online set loc=2 where id=1

    would that work? try a different update syntax (like the VALUES version and see if that makes a difference. I can't really think of what would cause this outside of permissions. You could be using an account which only allows updates of some fields. That's the only thing I can think of...?
    A computer without Windows is like a chocolate cake without mustard.

  5. #5
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    well I've just made a simple table and done the update and there were no problems.
    except I allowed nulls and had no keys = MUL (whatever that means)

    Have you a foreign key constraint in place that will not let you change something if to do so would break relational integrity? Cos then it might simply ignore such changes, leaving only those that have no impact in place, although I'd have thought the whole query would be rejected in that case.


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
  •