SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    $postcount++; koomann's Avatar
    Join Date
    Feb 2003
    Location
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Angry MySQL syntax error...Just can't get it.

    PHP Code:
    $query "INSERT INTO users (num_blocked, users_blocked) VALUES ('$number_block', '$_POST[ignored_user]') WHERE username='$user'";
            
    $result mysql_query($query); 
    I get the following error:

    You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE username='Eros'' at line 1
    What am I doing wrong? I just can't see it.

  2. #2
    Sell crazy someplace else markl999's Avatar
    Join Date
    Aug 2003
    Location
    Manchester, UK
    Posts
    4,007
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $query "INSERT INTO users (num_blocked, users_blocked) VALUES ('$number_block', '{$_POST['ignored_user']}') WHERE username='$user'"
    Edit:

    Oops, what Gaheris said, no WHERE with an INSERT, my bad

  3. #3
    PHP manual bot bronze trophy Gaheris's Avatar
    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,195
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Drop the WHERE part. If you want to update the data (not inserting new one like you're doing it now) use UPDATE (look it up).

  4. #4
    $postcount++; koomann's Avatar
    Join Date
    Feb 2003
    Location
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I figured that part out. I've been sitting at the computer too long... O_o

    But now I have a new problem. Say I have a list of names. John is already on the list, but I also want Susy on the list. How would I be able to add Susy without overwriting John?

  5. #5
    PHP manual bot bronze trophy Gaheris's Avatar
    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,195
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is it a flat file or a real relational database?

  6. #6
    $postcount++; koomann's Avatar
    Join Date
    Feb 2003
    Location
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm using MySQL.

  7. #7
    PHP manual bot bronze trophy Gaheris's Avatar
    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,195
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  8. #8
    $postcount++; koomann's Avatar
    Join Date
    Feb 2003
    Location
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I can't use an insert. If I were to, it's create a completely new row because of the auto_increment value of the ID row. I tried using UPDATE, but it doesn't allow me to do what I need to do. This is add more data to the field, other than completely re-writing it.

    Example:
    There's a list of names in the row:
    -Billy
    -Johnny
    -Andy

    Now, I want to put more names in row, without overwriting the original names.

  9. #9
    PHP manual bot bronze trophy Gaheris's Avatar
    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,195
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Get the names from the row, append the new name(s), update the row.

  10. #10
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah, so you are storing lists in one column of each row? If so, you just made your relational database cry.

    Alright, nothing that bad, but definitely not the way that relational database is designed to handle that sort of data.

    This is something I dislike about relational databases in general, there is usually no array feature that can be used in a given column so you can't just add data onto a given column of a given row. You pretty much have to select, get the data, modify the data, and update the row using the new data (which includes the old data).

    There's a better way to accomplish this (a many-to-many way) but I'm not even sure if I have understood the problem so I won't go into it. Yeah, to that point, does the above answer the original question?
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  11. #11
    $postcount++; koomann's Avatar
    Join Date
    Feb 2003
    Location
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Gaheris
    Get the names from the row, append the new name(s), update the row.
    That might help, if I knew what append means

    Samsm: I think I know what you mean. I need to select the names, add the changes, then update the the information in the database. Doesn't sound too hard

    The reason why I need a list of updateable names where the data isn't lost each update is because it's for an Ignore list in a small private messaging system I'm making.

    Thanks to all =]

  12. #12
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by koomann
    That might help, if I knew what append means
    Gaheris said the same thing I did, we just used different words. And besides, this is the information age! Anyone with an Internet connection has little reason not to know anything. :-)

    So you aren't interested in getting that data normalized? Your current method of storage suffers from a modify/delete anomaly (modify or delete a username and their name will be incorrect on ignore lists). Also, you are forcing your to do some work that could be done by the database.

    Basically you could get rid of the above problems by creating another table called ignoreList or something and have it set up something like this:

    create table ignoreList (
    userID INT PRIMARY KEY,
    ignoredUserID INT
    );

    See how that might work? Or does that look like too much trouble? :-)
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  13. #13
    $postcount++; koomann's Avatar
    Join Date
    Feb 2003
    Location
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Aye, the door of enlightenment opens again. This would be MUCH easier, and no, I don't think it would be too much trouble

  14. #14
    PHP manual bot bronze trophy Gaheris's Avatar
    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,195
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Uh, sorry, for using the word append. I thought that if a german kid knows it then someone from the US will probably too. Anyway, samsm gave you a nice link which should explain it.
    It looked like you messed up your database design a bit you looks but now you are sorting it out , good luck with it.

  15. #15
    $postcount++; koomann's Avatar
    Join Date
    Feb 2003
    Location
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Heh, go figure. I'm half German

  16. #16
    PHP manual bot bronze trophy Gaheris's Avatar
    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,195
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh, okay.


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
  •