Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
optimize mysql through a php script
#1
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.
Reply
#2
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...
Reply
#3
(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?
Reply
#4
(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.
Reply
#5
(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.
Reply
#6
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.
Reply
#7
(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.
Reply
#8
Thanks for the script GaiJin. Pretty nice Big Grin.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  PHP Framework List: An Ultimate Guide to 102 PHP Frameworks for Web Developers tk-hassan 0 758 07-27-2020, 11:26 PM
Last Post: tk-hassan
  Req. Upload PHP script. 3 X P L 0 I T 1 1,169 07-14-2012, 05:19 PM
Last Post: 'Snorlax
  PHP Video Tutorials (PHP For Beginners) Eleqtriq 4 3,233 10-10-2011, 01:00 PM
Last Post: Greyersting
  Creating a simple PHP/MySQL login script ndee 16 9,383 05-14-2011, 02:18 AM
Last Post: モrainee
  Simple MySQL Tutorial Sly 4 1,061 05-14-2011, 01:53 AM
Last Post: Sly

Forum Jump:


Users browsing this thread: 1 Guest(s)