optimizing tables with php and mysql

eightmin

I have a membership page on my web site. I also have an admimistration panel which allows me to delete members. I want to code my php to automatically optimize the table after a member has been deleted. I used an MySQL query to do this after the actual deletion. The code to optimize table works when I tried it in the phpMyAdmin but when I code it into my script, it does not optimize the table. I don't get any parse errors or anything and the delete works but the table does not get optimized. Here is the code that I am using:

$delete = "DELETE from members where memID = '$q' ";
$result = mysql_query($delete);
$deleted = "mysql_affected_rows($result)";

$opt = "OPTIMIZE TABLE members";
$opt_table = "mysql_query($opt)";

What am I doing wrong or what am I missing?
eightmin

superwormy

Ok so first of all, WHY ARE YOU OPTIMIZING THE TABLES SO MUCH!?!

In reality, you should rarely IF EVER need to use OPTIMIZE TABLE, and you DEFINITALLY SHOULD NOT BE USING IT AFTER EVERY DELETE QUERY!

It locks the tables which will cause problems if you run it too much on large tables, and theres really no need to use it unless you make A LOT of changes to the table ( ie 2,000 delete statements, not 1 )

:-P

BUT... anyway...

You arn't even calling the functions, because for some strange reason you have the function calls surrounded in double quotes, which makes them a string, not a function call.

Use this:


$delete = "DELETE from members where memID = '$q' ";
$result = mysql_query($delete);
$deleted = mysql_affected_rows($result);

$opt = "OPTIMIZE TABLE members";
$opt_table = mysql_query($opt);

Also, start using:

mysql_query ("your query here") or die (mysql_error());

So you can see your errors as well!

eightmin

Well Superwormy, your suggestion worked for the optimize code. I get a big "DUH" on quoting the function call. I went back and checked my code and the only place I put the double quotes on a function call was with the mysql_affected_rows();. Funny thing happened though.

When I removed the double quotes from the optimize table call and affected_rows call, I got a parse error on the mysql_affected_rows(). I went back in to my code, added the quotes back on the mysql_affected_rows() and left them off of the optimize table call and everything worked fine?! Go figure?

Thanks for the help. I am a self taught php-mysql person. I put the optimize table after the deletion because I had remembered reading that optimizing tables helps them run faster. The web site I am working on gets over 3 million hits a year but we do not delete members that often. Guess I am going to have to do some more reading on the mysql.