Php script to optimize my databases; what's wrong with it?

PHP
1

this is a script someone had posted up on another forum that I’m trying out, but I cannot get it to work, and I’m not sure why. my PHP skills are limited so that’s probably a reason why I can’t figure it out on my own. the script dies and throws this error: “Unknown Database!” anyhow, here’s the script if anyone would be so kind to tell me what’s going on with why it’s not working.

database info removed for obvious reasons


<?php

if(!$con = mysql_connect('localhost', 'username', 'password'))
{
    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)

?>
2

change

die("Unknown Database!");

into

die("Select DB error " . mysql_error());

to get a more useful error message.

3

Hello, andrewjs18.

Is the database_name value correct in your script?

4

thanks, I switched that up!

wow, I guess I shouldn’t mess with php at almost 3 AM. no, I TOTALLY missed that. I suppose it’s working because it’s not showing any errors on the screen and it took probably 15-20 seconds to “finish” working.

any pointers on how to make it so it doesn’t require a specific database to run? for example, I’d set it so that it runs through all of the databases in mysql as opposed to 1 defined database.

also, I’ve altered the script some to let me know if the script ran successfully. I hope I did it correctly:


<?php

if(!$con = mysql_connect('localhost', 'username', 'password'))
{
    die('Cannot connect!');
}
elseif(!$sel = mysql_select_db("database", $con))
{
    die("Select DB error " . mysql_error());  
}

$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());
}

ECHO "<b>Above tables successfully optimized.</b>";

mysql_close($con)

?>
5

It won’t work on the schema databases such as information_schema. You’ll need to exclude these databases and their tables if you have them as they will stop your script.

6 

<?php
$link = mysql_connect('localhost', 'user_name', 'password');
$db_list = mysql_list_dbs($link);

while ($row = mysql_fetch_object($db_list)) {
    if ( $row->database == 'information_schema' ) {
        continue;
    } 
    if ( $row->database == 'mysql' ) {
        continue;
    }
    echo "<p>Optimizing the {$row->Database} database</p>";
    
    // Database optimization script goes here (using
    // $row->database for the database name
    
    echo "<p>Optimizing of the {$row->Database} database complete!</p>";
}

echo 'All databases have been optimized!';

That will give you each database in turn with the database optimization lines of your script added where the comment is

7

I’m picking up on this thread, having searched for ‘mysql_list_dbs’.

Space Phoenix, I’ve run your code, but $link fails if I use ‘mysql_connect’, although it appears to work if I use ‘mysqli_connect’ (with the extra ‘i’) (that’s to say there’s no error message for that line). I’m not sure why this should happen. I’m using PHP 5.4.4

However then I get this error for the next line:

Fatal error: Call to undefined function mysql_list_dbs() in D:\websites\mullbed2\control\db_backup4.php on line 7

I’ve had exactly the same problem with other variants of this script, always with the ‘mysql_list_dbs’.

I suspect that’s because if I connect using ‘mysqli’, the remaining functions should also be ‘mysqli…’. However, there doesn’t appear to be a function ‘mysqli_list_dbs’ (well, I haven’t found it yet).

It looks as if I’ll have to get ‘mysql_connect’ to work somehow.

My long term aim is to be able to download a remote DB and update the local version from it, automatically. I’m planning to create a PHP script which I can run in a cron job. My local setup is WinXP (soon to be Win7) with Apache Server.

Has anyone any suggestions for achieving this, or any alternative approaches, please ?

8

What is the error you get when you use mysql_connect ?

9

Thanks for your reply.

The error I get is:

Fatal error: Call to undefined function mysql_connect() in D:\websites\mullbed2\control\db_backup2.php on line 27

So I think that means it’s a PHP error, not MySQL. I begin to wonder if PHP 5.4.4 no longer supports ‘mysql’, but although it’s deprecated, I’ve not seen anything to suggest it won’t work any more.

I have other PHP scripts which access the same database (using ‘mysqli’) without any problem, so there’s not anything major adrift.

Here’s a snippet from my script. It works as expected if used with ‘mysqli’


// mysql connection
	$host = "localhost";
	$user = "root";
	$pass = "password"; // not really, it's something else

	if (!$link = mysql_connect( $host , $user , $pass )) {
		echo "Error: " . mysql_error($link);
	} else {
		echo "DB link created<br />";
	}
10

Maybe the mysql extension hasn’t been installed? Try phpinfo() to check.

11

Thank you. You’re right. It must have been by default (or typing error), as I had not deliberately excluded it (it would not have occurred to me to do so).
I have edited php.ini and restarted Apache. The ‘mysql’ extension now shows up in phpinfo().

Let’s hope my troubles are over (in this context, at least). Thank you for your help.

12

All of this code is horrible.

You shouldn’t be using the mysql_ functions as they are deprecated.

You shouldn’t be echoing html out of php directly like that.

You shouldn’t be mixing your presentation code with your application logic.

Stuff like this is why I’m starting to really hate php.

13

Has nothing to do with PHP.

14

It has to do with the mentality of php developers, which in itself is encouraged by the php language.

I’m so tired of inheriting awful php code that is virtually impossible to maintain.

15

Bit of a sweeping generalisation there bud!

Agreed

Agreed

Also agreed

However, I am damn confident in saying that programmers at the level of @SpacePhoenix; and @guido2004; would not use that style of coding in a project.
For simple scripts like the above snippet - meh?!

16

But they didn’t tell the original poster that these things are bad.

I have inherited so many terrible scripts that started life like this. I just find it very frustrating, and while I know php is not the only language to suffer from these kind of issues, it does seem to be something that permeates the majority of php code I see. I’m not saying my code is absolutely perfect, but at least I think about it and engineer it.

Thing is, we should be criticising code like this when we encounter it, not just telling the OP how to get his awful code to run. It’s not even a criticism of the op as such - it’s more a criticism of the experts in places like this who should be doing more to point out these flaws at an early stage, rather than allowing them to flourish.

17

If you’d read my (July 2012) postings more carefully you’d have spotted that I was trying to use a function called ‘mysql_list_dbs’ (mentioned in the earlier postings), which doesn’t appear to have an equivalent ‘mysqli’ version (please tell me if you know better). Thinking it was probably better (possibly essential) not to mix ‘mysql’ and mysqli’ functions indiscriminately, I was trying to connect to MySQL using ‘mysql_connect’, but it wouldn’t work. There was a good reason for that, which Guido2004 suggested (and he was right).

I’m aware that ‘mysql_’ functions are deprecated. The snippets of PHP code I posted were for development, not production, and I find echoing out the errors to be helpful. If you know of a better and simpler way to do it, it would be useful to say so.

As it stands you’ve hijacked the thread to have a rant, which considerably reduces it’s usefulness to me and others.

I have since achieved my original objective via a different route.

18

The code you posted from July 2012 was still using the mysql_ functions.

It seems to me that there is not a general focus on quality in this forums - at least not in the php areas.

In another post, someone was asking for some database query help and I discovered a vulnerability in his code that meant an attacker could delete the entire database just by passing a string to the get request.

I pointed out the error and told him where to look to fix it.

I was criticised by so-called ‘experts’ in this forum for posting off topic and not directly helping him with this query. I was then literally told to “please stop posting.”.

In this thread I’ve pointed towards some of your messy code and gave you pointers at least in terms of where to go from there. Again, I’m criticised for “hijacking” the thread.

It’s not hijacking if I point out fundamental problems with your code.

If it were the other way round and I was posting for advice and you found some (genuine) problems with my code and then helped me sort them, I’d be thankful for your help.

I’m sorry if you’re just like the average php programmer and don’t care about quality. At least some of us do.

19

I care very much about quality. You do not seem to have taken into account the fact that I felt FORCED to try using ‘mysql_’ functions because there is no ‘mysqli_’ equivalent to ‘mysql_list_dbs’ (or if there is, I’ve failed to find it, and no-one has made any suggestion). Now ‘mysql_list_dbs’ may be the most appallingly awful function ever devised, and that may be why there’s no ‘mysqli_’ equivalent. but no-one’s actually said that.

You’ve been quick to pour scorn on what has been posted, but not a single one of your comments has actually been technically helpful. I don’t intend to continue this unedifying slanging match, so I’m going to unsubscribe from this thread.

20

Bringing this thread back on topic, you can use a SHOW DATABASES query to get a list of the databases. If you’re using that list make sure that you don’t touch either the information_scheme or the mysql databases.

Once you have the list of databases (not including the information_schema and mysql databases) you just use a SHOW TABLES query to get a list of tables for the database and then using a loop for each table in turn do the OPTIMIZE TABLE query for each table.