Script crashes mid way...

nivek9991

Hello All,
I seem to be getting some weird errors with a little script I wrote.
I run this script, and every single time, after a random number of seconds, the page refreshes with "Cannot find server" (no specific error).
I go and check the table where I was inserting, and every single time, there is a different number of entries it added.
The code is below, but I don't think there's anything wrong with the code:

 $if_empty_query = "SELECT count FROM song_count LIMIT 0,1";
	$if_empty_res = mysql_query($if_empty_query);
	if ($if_empty_res) {
		$empty_table = "TRUNCATE TABLE song_count";
		$result = mysql_query($empty_table,$db);
	}

$letter = "a";
for ($k=0; $k<26; $k++) {
	$sql = "SELECT DISTINCT artist FROM lyrics WHERE artist_letter=\"".$letter."\" ORDER BY artist ASC";
	$artist_list = mysql_query($sql,$db);


	for ($i=0; $artist_list_row = mysql_fetch_row($artist_list) ; $i++) {
		$sql2 = "SELECT DISTINCT song FROM lyrics WHERE artist = \"".$artist_list_row[0]."\"";
		$song_count_res = mysql_query($sql2,$db);
		$song_count = mysql_num_rows($song_count_res);

		$artist_list_row[0] = addslashes($artist_list_row[0]);
		$insert_query = "INSERT DELAYED INTO song_count (artist,count) VALUES (\"".$artist_list_row[0]."\",\"".$song_count."\")";
		$insert_res = mysql_query($insert_query,$db);
	}
	$letter++;
}

Has anybody run into a problem like this, or have any idea what might be wrong?

sarahk

some thoughts

$if_empty_query = "SELECT count FROM song_count LIMIT 0,1";

should be

$if_empty_query = "SELECT count FROM song_count LIMIT 0,1";

because count is also a function name.

how about changing queries 2 and 3 to

$sql = "SELECT artist, song, artist_letter
FROM lyrics
WHERE artist_letter='$letter' ORDER BY artist, song ";

then loop through this. If you have a variable which starts as

$first_letter = "#"

and test $first_letter to match artist_letter and if there is no match then you put in the header stuff for that new letter. This saves you one query.

I haven't used the insert delayed but I'd start by commenting out lots of code and seeing if what is left works and then uncomment some, see if it still works and so on to identify where your loop happens.

I'm also assuming that $letter++ works, haven't tried that.

nivek9991

Some good suggestions, but I have to explain more:

$if_empty_query = "SELECT count FROM song_count LIMIT 0,1";
This query works fine, the problem is in the for loop (I know this because the table indeed does get emptied).

With the dual for loop, here is an explaination:
The first loop grabs distinct artists, so that I have a list of all the artists (in the larger lyrics table, if a particular artist has 20 lyrics, then there are 20 rows, so I grab distinct artists to simply get a list).

I then grab all the songs from that particular artist, which is query 3, and count them.

Then I insert that artist, and the number of songs for that artist.

(And yes, $letter++ works).

It's tough to get any help on this because I get no specific error, it just says page not found.

And I want to emphesize that the last insertion query does indeed work, as after I run the script, I check the table, and some entries have been added, but here's the strange thing:
I can do a reload after every failure, and I get a different number of items that were inserted every single time.

It's not like the script is failing on one particular artist, it simply randomly decides to crap out.

I'm really at a loss as to why the script can't complete, and why it (or the server) seems to have a mind of it's own when it decides it wants to crap out.

sarahk

I'd say that you are getting a time out type of error where the page just can't complete so it's saying it can't do it.

the way you do it with the distinct artist is that you have one query to get the artists and then 1 for every artist which could be 100s. My suggestion has you doing a single query - 1 - much faster. With intelligent looping you can still ensure you have your headers in place. Maybe more logic in php but much more efficient.

Now, lets say you follow my advice and you still get the error, the next step would be to put a limit on the query of say 100, then 500, then 1000 and see if it's a size problem.

Yes it craps out in different places but then the page will take a different amount of time to process depending on a number of variables and will therefore make different amounts of progress.

try it - see how it goes.

====================

as for the following:

if ($artist_list_row[0] != $curArtist && $curArtist != "#")
{
  $artist_list_row[0] = addslashes($artist_list_row[0]);
  $insert_query = "INSERT DELAYED INTO song_count (artist,count) VALUES (\"$curArtist\",'$song_count')";
  $insert_res = mysql_query($insert_query,$db);
  $curArtist = $artist_list_row[0];
  $song_count = 1;
}
else
{ 
  $song_count++;
}

this allows the insert for the last artist only after we've flipped over to a new one. I'd actually put it into a function so we can call the logic easily because we need it after we exit the loop for the final insert.