RE: [PHP] Data integrity checking w/ PHP & MySQL?

Beware. It can spiral out of control very quickly. I'm pushing 2 gigs of traffic a day, and that's not counting the images which are pulled off another site. I still haven't hooked up with any ad networks yet either. I did what Peter recommended, incrementing the VOTE # and RATING in the mysql_query itself.. still flying along, no slow_queries or anything.. and actually I took off skip locking and we're still flying along ok.. I was concerned about locking the tables as well but it didn't appear to hurt anything at the moment (139k of queries since the change, 0 slow). The code I have now for that portion is: (check to see if voted on in the last hour by this IP) if ($row = mysql_fetch_array($votecheck)) { $SCORE = ($RAWTOTAL / $VOTES); $SCORE = ($SCORE * 100); $SCORE = round($SCORE); $SCORE = ($SCORE / 100); } else { $insertvote = mysql_query("INSERT into log set IP = '$USERIP', PID = '$PID', DATETIME = '$DATETIME'"); if (($RATING > '-2') && ($RATING < '11')) { $SHOWRAWTOTAL = ($RAWTOTAL + $RATING); $SHOWVOTES = $VOTES + 1; $SCORE = ($SHOWRAWTOTAL / $SHOWVOTES); $SCORE = ($SCORE * 100); $SCORE = round($SCORE); $SCORE = ($SCORE / 100); $updatepre = "UPDATE pictures SET RAWTOTAL = $RAWTOTAL+$RATING, VOTES = $VOTES+1, SCORE = '$SCORE' where PID = '$PID '"; $updatescore = mysql_query($updatepre); } seems to be working ok. Eventually I'm open-sourcing this, I just do not have time to document everything I would like and bugs and exploits are turning up at an uncomfortable rate right now so I'd rather try to debug as best I can first (yes I kno w many eyes etc.), I just don't want to expose myself to some huge vulnerability without having the chance to catch it first. Thanks everyone, I appreciate it.. humming along now, and hopefully that will take care of the integrity problem. Chad Day bangable.com -----Original Message----- From: Kyle Jerviss [mailto:kjj@jerviss.org] Sent: Thursday, December 07, 2000 12:22 PM To: Chad Day Cc: 'php-general@lists.php.net'; 'php-db@lists.php.net' Subject: Re: [PHP] Data integrity checking w/ PHP & MySQL? Ok, that's it! I'm going to write a traffic-whore site like this, too. :) You could SELECT their data, update it like you do now, then reSELECT it and compare it to their old score for a quick sanity check. Also, locking the tables might help, but would probably kill your performance. Post (or send me) your update code and I'll take a look. Chad Day wrote: > > I've run into some problems on my high traffic site, bangable.com, with what > appears to be data integrity in MySQL, and was wondering if there are any > checks I can do in PHP to validate that the right information was updated. > > I have a ton of inserts/selects going on.. in little over a day the number > of queries has reached 1 million. This is an amihotornot type site, what > happens is the user votes on a picture, that rating/# of votes is updated in > the DB, the persons IP thrown into a database, then moves onto the next > picture.. I've gotten a few reports of people who have had like 600 votes > then all of a sudden they drop to like 27 (or some other random #) or their > score changes, or some combination of the 2. I've only had about half a > dozen reported cases of this, and half a dozen in several million queries is > pretty good, but it reflects badly on the integrity of the system and I need > to correct that. > > Is there any way I can code in a check in PHP to see if it got updated > correctly without inducing too much overhead? While searching around the > net this morning for data integrity in MySQL, what I basically saw was the > check needs to be coded in the programming language. If anyone has > recommendations on how to do this, I would appreciate it. I don't want to > switch over to another DB unless there's no solution, as I didn't use a db > wrapper and changing all those database calls would really suck. > > Thanks, > Chad Day > > When I speak german... I think german in my head... but like...Do skript > kiddies see a w40l3 8uncha 1's and 0's and 3's and 4's and 7's in > their > h34d'5 w43n t43y R +a1k1n6 ? -- SirStanley > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, e-mail: php-general-unsubscribe@lists.php.net > For additional commands, e-mail: php-general-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net