SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Having a small problem...

    On the submission of a form, I'm doing several things...one is verifying the user's username and password...if they match up, it submits their entry to an SQL database...and then updates data in another title as a result (it's counting up one for each entry...like posting counting)...here's some of the code I have to update the field:

    Code:
      $sql = "UPDATE user SET " .
             "commentcount='$commentcount', " . 
             "WHERE userid='$userid'";
      if (mysql_query($sql)) {
        echo("<P>Your entry has been modified.</P>");
      } else {
        echo("<P>Error adding submitted entry: " .
             mysql_error() . "</P>");
      }
    I get this error:

    Error adding submitted entry: You have an error in your SQL syntax near 'WHERE userid='1'' at line 1

    Just to keep things clear: I've got a little user-comment system setup so that people can use the same username and password they have on my copy of vBulletin to leave comments after news stories...I've altered the "user" table in vBulletin to include an INT NOT NULL fielD: "commentcount"...I'm trying to grab that comment from the database, increment it up one, and then use the above code to update it with the new number...

    I've got it all ready except for this last part.

    Any help is greatly appreciated!

  2. #2
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    You've got a spurious comma in your SQL code:

    Code:
    $sql = "UPDATE user SET " .
      "commentcount='$commentcount', " . // Remove comma!
      "WHERE userid='$userid'";
    if (mysql_query($sql)) {
      echo("<P>Your entry has been modified.</P>");
    } else {
      echo("<P>Error adding submitted entry: " .
           mysql_error() . "</P>");
    }

    [Edited by kyank on 09-17-2000 at 10:28 AM]
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  3. #3
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I dont know if you have it in there for the sake of PHP or what.

    But you don't need that comma after your set info. You only use that comma if you're going to change more than 1 thing.

    Chris

  4. #4
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    damn we posted at the same time. ha.

  5. #5
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    From the sounds of it, there are some problems with your approach, however. First off, you shouldn't have to keep count of the number of comments -- MySQL can do that for you using its COUNT() function! Whenever you need to know how many comments there are in the table from a particular user, just do

    Code:
    SELECT COUNT(*) FROM user, comments WHERE user.userid = comments.userid
    AND user.userid='$userid';
    Storing the count in a column of your user table is just asking for trouble in terms of synchronization and so forth. Furthermore, you are storing redundant data, since the information is already available in the database as shown above.

    If for some reason you feel you need to keep this column, there's another issue to consider. The method you're using to update the number is not 'thread-safe'. Whenever you do a read-modify-update on the number, you're running the risk that another process will be doing the same read-modify-update at the same time. If you get an interleaving pattern like read1-read2-modify1-modify2-update1-update2, you can see that the number will only be incremented by one -- not two! For this reason, you should use a single UPDATE query:

    Code:
    UPDATE user SET commentcount=commentcount + 1
    WHERE userid='$userid'
    Let me reiterate, however, that just having this column is bad database design.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  6. #6
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you so much Kevin! Amazingly quick and solved my problem.

    Good news is I went into vB's code and got the whole thing to work...nice integration going. Thanks so much

    EDIT: Very good point...I'll give it a try.


    [Edited by TWTCommish on 09-17-2000 at 10:33 AM]

  7. #7
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Be sure to read the additional comments I just posted above.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  8. #8
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just read it all...trying it now - but how do I print the count total after the query?

  9. #9
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    The query I gave you returns a one-row, one-column result set. You can get the value with mysql_result():

    Code:
    $count = mysql_result($result,0,0);
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  10. #10
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks again...problem, though:

    Here's what I used:

    $commenttotal = mysql_result($result,0,0);

    Calling upon it doesn't work, and I think it's due to the nature of the code I'm using...it's the code vBulletin uses to get a member's profile:

    Code:
      $userinfo=$DB_site->query_first("SELECT username,joindate,lastpost,usertitle,email,showemail,invisible,homepage,icq,aim,yahoo,biography,posts FROM user WHERE userid=$userid");
      $commentcount=$DB_site->query_first("SELECT COUNT(*) FROM user, comments WHERE user.userid = comments.userid AND user.userid='$userid';");
      $username=htmlspecialchars($userinfo[username]);
      $searchusername=urlencode($userinfo[username]);
      $datejoined=date($dateformat,$userinfo[joindate]+(($timeoffset+$bbtimezoneoffset)*3600));
      $commenttotal = mysql_result($result,0,0);
      $usertitle=$userinfo[usertitle];
    That's the block I've got...As you can see the second query there is yours...Any ideas?

  11. #11
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Try this, then. Notice I've assigned an alias to the count() coulumn. I've also removed the spurious semicolon at the end of the query.

    Code:
      $userinfo=$DB_site->query_first("SELECT username,joindate,lastpost,usertitle,email,showemail,invisible,homepage,icq,aim,yahoo,biography,posts FROM user WHERE userid=$userid");
      $commentcount=$DB_site->query_first("SELECT COUNT(*) AS thecount FROM user, comments WHERE user.userid = comments.userid AND user.userid='$userid'");
      $username=htmlspecialchars($userinfo[username]);
      $searchusername=urlencode($userinfo[username]);
      $datejoined=date($dateformat,$userinfo[joindate]+(($timeoffset+$bbtimezoneoffset)*3600));
      $commenttotal=$commentcount[thecount];
      $usertitle=$userinfo[usertitle];
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  12. #12
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You're sick! Fantastic!

    Dang - how much do you charge per hour? Whatever it is, SitePoint should triple it.

    Thanks a ton man - you've made my litle world complete You'll definetly be given a thank you on the site when this is all done.


  13. #13
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    SitePoint doesn't pay me for my participation in the forums. Glad I could help, though!
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference


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
  •