optimize mysql through a php script

[andrewjs18]

would anyone be kind enough to put together a script that'll run via a cron job to optimize all of the databases that I currently have set up through my shared host?

I'd appreciate it, as my php skills are fairly nonexistent.

[Gaijin]

I assume you already know how to setup the cron and get it to work, so here would be your script...

PHP Code:

<?php

if(!$con = mysql_connect('locahost', 'user', 'pass'))
{
    die('Cannot connect!');
}

$tables = mysql_query('SHOW TABLES', $con) or die('SHOW TABLES ERROR');
while($table = mysql_fetch_row($tables))
{
    mysql_query("OPTIMIZE TABLE {$table[0]}") or die(mysql_error());
}

mysql_close($con)

?>

You might also want to check out commands such as, clean and repair...

[andrewjs18]

I assume you already know how to setup the cron and get it to work, so here would be your script...

PHP Code:

<?php

if(!$con = mysql_connect('locahost', 'user', 'pass'))
{
    die('Cannot connect!');
}

$tables = mysql_query('SHOW TABLES', $con) or die('SHOW TABLES ERROR');
while($table = mysql_fetch_row($tables))
{
    mysql_query("OPTIMIZE TABLE {$table[0]}") or die(mysql_error());
}

mysql_close($con)

?>

You might also want to check out commands such as, clean and repair...

thanks for the script. note that in case anyone else wants to use this, they'll need to fix "locahost" in your original code to read "localhost".

if the script is showing this, and only this, on the screen (SHOW TABLES ERROR), does that mean it failed or did it run successfully?

[Gaijin]

thanks for the script. note that in case anyone else wants to use this, they'll need to fix "locahost" in your original code to read "localhost".

if the script is showing this, and only this, on the screen (SHOW TABLES ERROR), does that mean it failed or did it run successfully?

Yeah, sorry about the typo my keyboard is finished, it should say localhost.
And I forgot to select the database, here is the full (should) working script

PHP Code:

<?php

if(!$con = mysql_connect('locahost', 'user', 'pass'))
{
    die('Cannot connect!');
}
elseif(!$sel = mysql_select_db("database_name", $con))
{
    die("Unknown Database!");
}

$tables = mysql_query('SHOW TABLES', $con) or die('SHOW TABLES ERROR');
while($table = mysql_fetch_row($tables))
{
    mysql_query("OPTIMIZE TABLE {$table[0]}") or die(mysql_error());
}

mysql_close($con)

?>

PHP Code:

die('SHOW TABLES ERROR') 


Thisis being executed if the SHOW TABLES fails for any reason, probably because of empty database or incorrect config (locahost)...
However, you can also use mysql_list_tables(string $database [, resource $link]) instead of SHOW TABLES.

[andrewjs18]

Yeah, sorry about the typo my keyboard is finished, it should say localhost.
And I forgot to select the database, here is the full (should) working script

PHP Code:

<?php

if(!$con = mysql_connect('locahost', 'user', 'pass'))
{
    die('Cannot connect!');
}
elseif(!$sel = mysql_select_db("database_name", $con))
{
    die("Unknown Database!");
}

$tables = mysql_query('SHOW TABLES', $con) or die('SHOW TABLES ERROR');
while($table = mysql_fetch_row($tables))
{
    mysql_query("OPTIMIZE TABLE {$table[0]}") or die(mysql_error());
}

mysql_close($con)

?>

PHP Code:

die('SHOW TABLES ERROR') 


Thisis being executed if the SHOW TABLES fails for any reason, probably because of empty database or incorrect config (locahost)...
However, you can also use mysql_list_tables(string $database [, resource $link]) instead of SHOW TABLES.

hmn, now the script is dying on the "Unknown Database!" section even when I use live, functioning database information for a forum I admin.

[Gaijin]

Look at this tutorial written by Sly;
http://www.supportforums.net/showthread.php?tid=18695

That is obviously a error on your side, the connecting configuration is false, "database_name" needs t be replaced if you didn't, this is the place where you put the name of the database you use to store data and tables. On most hosts your database name is same as the login name.
Also make changes to the both die function;

PHP Code:

die(mysql_error()); 


If you don't figure it out, the error shown will display yo more information about the roots of the problem, in case you don't understand it post it here so I can take a look at it.

[ChromeWolf]

hmn, now the script is dying on the "Unknown Database!" section even when I use live, functioning database information for a forum I admin.

Try making a configs.php

PHP Code:

<?php
$dhost = "localhost";
$dusername = "root";
$dpassword = ""; //Your Database Password
$ddatabase = ""; //Your Database name.

$con = mysql_connect($dhost, $dusername, $dpassword) or die("Cannot Connect");
mysql_select_db($ddatabase, $con);
?>

I don't know if this is exactly what you're looking for.

[John.]

Thanks for the script GaiJin. Pretty nice .