UPDATE Query Producing Error

[TWTCommish]

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:

  $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!

[Kevin Yank]

You've got a spurious comma in your SQL 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]

[aspen]

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

[aspen]

damn we posted at the same time. ha.

[Kevin Yank]

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

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:

UPDATE user SET commentcount=commentcount + 1
WHERE userid='$userid'

Let me reiterate, however, that just having this column is bad database design.

[TWTCommish]

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]

[Kevin Yank]

Be sure to read the additional comments I just posted above.

[TWTCommish]

Just read it all...trying it now - but how do I print the count total after the query?

[Kevin Yank]

The query I gave you returns a one-row, one-column result set. You can get the value with mysql_result():

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

[TWTCommish]

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:

  $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?

[Kevin Yank]

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.

  $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];

[TWTCommish]

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.

[Kevin Yank]

SitePoint doesn't pay me for my participation in the forums. Glad I could help, though!