Support Forums
optimize mysql through a php script - Printable Version

+- Support Forums (https://www.supportforums.net)
+-- Forum: Categories (https://www.supportforums.net/forumdisplay.php?fid=87)
+--- Forum: Coding Support Forums (https://www.supportforums.net/forumdisplay.php?fid=18)
+---- Forum: PHP The Hypertext Preprocessor (https://www.supportforums.net/forumdisplay.php?fid=21)
+---- Thread: optimize mysql through a php script (/showthread.php?tid=22345)



optimize mysql through a php script - andrewjs18 - 09-24-2011

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.


RE: optimize mysql through a php script - Gaijin - 09-24-2011

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...


RE: optimize mysql through a php script - andrewjs18 - 09-28-2011

(09-24-2011, 01:28 AM)Gaijin Wrote: 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?



RE: optimize mysql through a php script - Gaijin - 09-28-2011

(09-28-2011, 12:08 AM)andrewjs18 Wrote: 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.



RE: optimize mysql through a php script - andrewjs18 - 09-28-2011

(09-28-2011, 04:39 PM)Gaijin Wrote: 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.


RE: optimize mysql through a php script - Gaijin - 09-30-2011

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.


RE: optimize mysql through a php script - ChromeWolf - 10-05-2011

(09-28-2011, 11:24 PM)andrewjs18 Wrote: 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.


RE: optimize mysql through a php script - John. - 10-05-2011

Thanks for the script GaiJin. Pretty nice Big Grin.