PHP/MySQL hostserver crashing/freezing

Question

I am not sure if I have totally missed something here so I am asking, to hopefully become a better person. So I already now, ask for forgiveness for my stupidity, if any.

I have a client that is hosted by company that now is blaming the website I've build for the client, for "crashing" (or at least making it run very slow) the server over and over again. And no this is not a huge website with any complex script. It is a blog, with comment functions.

They tell me this is the problem, because from logs there seems to be only one problem:

www.xxxxxx.se xxx.xxx.xx.xxx [14/Mar/2011:05:08:02 +0100] fcgi_php_error:PHP Warning: mysql_query() [function.mysql-query]: Unable to save result set in /home/t/xxxxx/www/include/php/newsfeed_full.php on line 66, PHP Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/t/xxxxx/www/include/php/newsfeed_full.php on line 68

Line 66:

$sql = "INSERT INTO `newsfeed_comments` (post_id, reply_id, date, name, text) VALUES ('".mysql_real_escape_string($post_id)."', '".mysql_real_escape_string($_POST['reply_id'])."', '".date('YmdHis')."', '".mysql_real_escape_string($_POST['name'])."', '".mysql_real_escape_string($_POST['text'])."')";
mysql_query($sql) or die(mysql_error());

$sql output:

INSERT INTO `newsfeed_comments` (post_id, reply_id, date, name, text) VALUES ('168', '111194', '20110322145339', 'Test 2', 'Test text 2')

Line 68:

$sql = "SELECT * FROM `newsfeed_comments` WHERE `post_id` = '".$post_id."' && `reply_id` IS NULL ORDER BY `date` DESC";
$result = mysql_query($sql);
$i = 0;
while ($row = mysql_fetch_assoc($result)) {
//and so on...

$sql output:

SELECT * FROM `newsfeed_comments` WHERE `post_id` = '168' && `reply_id` IS NULL ORDER BY `date` DESC

Problem is that, I have never seen these error myself from testing. I have never been able to replicate the errors, in any way. So it is obviously working.

My question is, what could be wrong? The hosting company just tells me that I need to make sure my script can handle all the possible errors their server might give out.

Same script runs flawlessly on 10-15 other hosting services without any problems and I have never had any problems with them.

Am I missing anything crucial? Do I have a large knowledge gap when it comes to PHP?

Answer 1

You don't have any error handling at all in this, and that's a problem. If your call to mysql_query() doesn't work for some reason, it will return FALSE. That is then handed to mysql_fetch_assoc() and that will fail too.

You really should be doing some basic error handling, something along the lines of this:

$sql = "SELECT * FROM `newsfeed_comments` WHERE `post_id` = '".$post_id."' && `reply_id` IS NULL ORDER BY `date` DESC";
$result = mysql_query($sql);
if (!$result) {
     //do error handling and fail
}
$i = 0;
while ($row = mysql_fetch_assoc($result)) {
...

This page has some good examples of how to do this with mysql_fetch_assoc().

Program defensively, check for error conditions on anything that can possibly fail, and produce informative error messages about what happened, and you'll have much better results.