+- 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')
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)
?>
Thisis being executed if the SHOW TABLES fails for any reason, probably because of empty database or incorrect config (locahost)...PHP Code:die('SHOW TABLES ERROR')
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
.