Database Question !! please Help!

Discussion in 'OT Technology' started by Spirito, May 21, 2005.

  1. Spirito

    Spirito New Member

    Joined:
    Mar 16, 2005
    Messages:
    87
    Likes Received:
    0
    Location:
    England
    Hi guys , i made an order SQL in my forum database


    --------------------------------------------------------
    UPDATE`post` SET `username` = 'hhh',
    `userid` = 'X';
    UPDATE `thread` SET `postusername` = 'hhh',
    `postuserid` = 'X';

    ------------------------------------------------------

    and i want to cancel this order , how can i ? , please help . all the nicknames

    changed to ''HHH'' .. :noes:

    thanks !
     
  2. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    you need to use the "WHERE field=value" clause otherwise you'll have bad results.
     
  3. Spirito

    Spirito New Member

    Joined:
    Mar 16, 2005
    Messages:
    87
    Likes Received:
    0
    Location:
    England
    Hi jollyogre , thanks for reply. can you explain more please :noes:
     
  4. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    THINK about it.

    you want to

    UPDATE (table) `post`
    (and) SET (field) `username` = [to] 'hhh'
    (and) (field) `userid` = [to] 'X'

    But under those conditions EVERY record gets set to the above.... You need to tell it WHERE to update the record.

    For example

    UPDATE (table) `post`
    (and) SET (field) `username` = [to] 'hhh'
    (and) (field) `userid` = [to] 'X'
    WHERE `uid` = [is] 13

    Or, more typically:
    UPDATE (table) `post`
    (and) SET (field) `username` = [to] 'hhh'
    WHERE `userid` = [is] 'X'

    which translates to:
    UPDATE `post` SET `username` = 'hhh' WHERE `userid` = 'X';

    A computer can't read your mind.... You have to tell it WHERE to update the query.
     
  5. Spirito

    Spirito New Member

    Joined:
    Mar 16, 2005
    Messages:
    87
    Likes Received:
    0
    Location:
    England
    thanks a lot for your help , actually i'm very far from programming and these stuff .. i'll try to work it out , i thought that i may need further SQL order to cancel it ​
     
  6. Spirito

    Spirito New Member

    Joined:
    Mar 16, 2005
    Messages:
    87
    Likes Received:
    0
    Location:
    England
    ANY help guys ? :wtc:
     
  7. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    i just fucking gave you the answer :rolleyes:
     
  8. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,918
    Likes Received:
    10
    Location:
    Los Angeles
    He told you the answer, unless you didn't describe your full question in detail. You want to cancel an order by changing the username to hhh? Mr. Ogre gave you the exact syntax to use. If you want to cancel, as in delete the record, than you need to use the delete syntax. I like to keep existing records, so I would either dump the data into a history table, or add an extra field "status", where 0 = active, and 1 = cancelled, then I would just update the status field to 1. However, you would have to change your select queries to SELECT * FROM blah WHERE status = 0.

    But with the question you asked, Ogre gave you the exact answer
    PHP:
    UPDATE `postSET `username` = 'hhh' WHERE `userid` = 'X';
     
  9. Spirito

    Spirito New Member

    Joined:
    Mar 16, 2005
    Messages:
    87
    Likes Received:
    0
    Location:
    England
    :hsugh: , i guess you misunderstood me :hsd: , or perhaps i didnt clear my question .

    i ''DID'' this SQL order in my forum database and it changed all my members

    nicknames and posts counts and this is the problem. :noes:

    now i want to make the things back as it was before this order :x:
     
    Last edited: May 22, 2005
  10. Spirito

    Spirito New Member

    Joined:
    Mar 16, 2005
    Messages:
    87
    Likes Received:
    0
    Location:
    England
    thanks very much kingtoad . i meant to cancel, as in delete the record
     
  11. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,918
    Likes Received:
    10
    Location:
    Los Angeles
    Then you would do something similair to
    PHP:
    DELETE `usernameFROM `tablenameWHERE `userid` = 'X';
    In english, this would delete all records in the table where the userid in ALL records are 'X'
     
  12. Spirito

    Spirito New Member

    Joined:
    Mar 16, 2005
    Messages:
    87
    Likes Received:
    0
    Location:
    England
    thanks for reply , is this will make me lose any data?

    i don't want to lose the data, i want to keep it as it was before i did the order

    thanks ,,
     
  13. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,918
    Likes Received:
    10
    Location:
    Los Angeles
    Then create an additional field, "status", give it an integer type, and update the type. It's the most safest way to do it, other than actually editing a userid or username, which isn't exactly the best method to do things, since I'm assuming userid is the primary key in the table, it probably shares relationships with other tables... and username, that's a given.
     
  14. Spirito

    Spirito New Member

    Joined:
    Mar 16, 2005
    Messages:
    87
    Likes Received:
    0
    Location:
    England
    thanks a lot ! but i dont have this good #understanding of these stuff#

    now i created the ''status'' field , and gave it an integer type and updated it .
    what next ? :ugh:


    cheers , :x:
     
    Last edited: May 22, 2005
  15. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,918
    Likes Received:
    10
    Location:
    Los Angeles
    Then you would change your select statements to select WHERE status = 0. Then when you update it, as in "cancel" the order, update the status to 1.

    You may want to check this out
    http://www.w3schools.com/sql/default.asp
     
  16. Spirito

    Spirito New Member

    Joined:
    Mar 16, 2005
    Messages:
    87
    Likes Received:
    0
    Location:
    England
    that's great ! , thanks for your kind help :hs:
     
  17. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    if you want to "undo" what you did, you're shit out of luck... the query you executed was permanent, and your data is gone. Hope you had a backup.
     
  18. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Agreed - but wouldn't transactional records be able to fix this or not?
     
  19. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,918
    Likes Received:
    10
    Location:
    Los Angeles
    Yes, it will. But I am pretty sure a project like he is doing is probably a school project. I'm assuming that he's probably in an intro class because he's still new to SQL, so, going off about data replication is probably a bit out of his league for the moment.
     
  20. Spirito

    Spirito New Member

    Joined:
    Mar 16, 2005
    Messages:
    87
    Likes Received:
    0
    Location:
    England
    no i'm actually far from programming and these stuff , studying BMS.

    anyway thanks a lot guys , i solved it , and i ran a backup .

    cheers :hs:
     

Share This Page