SQL coding examples

This SQL and PHP will count how many rows contain unique elements in your database.   For example, you may have a database of user entries and want to know how many entries each user has.   In this example, you want to know how many rows contain each unique element "name":

$query = mysql_query("SELECT name, COUNT(*) AS number FROM $table GROUP BY name");
if ( mysql_num_rows($query) == 0 ) echo "No rows in the database";
while ($row = mysql_fetch_array($query)) {
  $name = $row['name'] ;
  $count = $row['number'] ;
  echo "$name has $count rows in the database<br>";
}

Using PHP/MySQL scripts at your host and at home

If you have PHP/MySQL scripts that you want to use both at your host and on your home machine, you can add a few script lines that will allow you to do that without requiring any further modifications when you transfer your scripts back and forth.  In your MySQL configuration statements include a simple "if test" to see if you are on the remote server or on your home machine and set the parameters accordingly.

- - Start Script Here - -
<?phpif($_SERVER['HTTP_HOST'] == "localhost") {
  $host="localhost";
} else {
  $host="your-host-mysql-server.com";
}?>
- - End Script Here - -

The script example above tests for the domain name to see if it is executing on your home machine (localhost in this example), then sets the configuration accordingly.

MySQL Error Notification

Would you like to be notified when your site has a MySQL error?  This PHP script will trap MySQL errors, send the user a custom error page, and email you with the details of the error.  You can customize this example to launch other pages based on the type of error, or to have the error routine do what ever you want.

A typical MySQL statement should look like this:

- - Start Script Here - -
<?php
mysql_connect("$host", "$dbuser", "$dbpswd") or sql_error("connect",mysql_error());?>
- - End Script Here - -

When there is an error, this will call the function sql_error, sending a marker (in this case "connect") and the actual MySQL error.

At the top of your script (before any MySQL statements) put the function sql_error:

- - Start Script Here - -
<?phpfunction sql_error($function,$mysql_err) {
  echo "<center><br><hr>
  Oops . . . looks like our database is having technical problems.
  <br><br>
  Please come back later . . . hopefully we can get things up and running again soon.
  <hr>";
  $error_time = (date(" F d h:ia"));
  $message .= "There has been a MySQL error.\n\n";
  $message .= "Error: $function - $mysql_err\n\n";
  $message .= "Server time of the error: $error_time\n\n";
  mail("youremail@domain.com", "MySQL error" , $message, "From: Website <>");
  die();
}?>
- - End Script Here - -

Automated MySQL Backups

Here is a php script that will backup your MySQL database, creating a different file each day for a month (then start over) so you have 30 days of backups.  It will email you with a backup confirmation.  Run it every night using cron to kick it off.  Replace the X's with your information.  In this example the backups will be placed in your FTP Root directory.

- - Start Script Here - -
<?php
$emailaddress = "XXXXXX@yourdomain.com";$host="XXXXXX"; // database host$dbuser="XXXXXX"; // database user name$dbpswd="XXXXXX"; // database password$mysqldb="XXXXXX"; // name of database$day = (date("d"));$filename = "/full_path_to_file_goes_here/backup" . $day . ".sql";
if ( file_exists($filename) ) unlink($filename);system( "mysqldump --user=$dbuser --password=$dbpswd --host=$host $mysqldb > $filename",$result);$size = filesize($filename);$runtime = (date(" F d h:ia"));$message .= "The backup has been run.\n\n";$message .= "The return code was: $result\n\n";$message .= "Size of the backup: $size bytes\n\n";$message .= "Server time of the backup: $runtime\n\n";mail($emailaddress, "Backup Message" , $message, "From: Website <>"); ?> - - End Script Here - -

You can restore the database using the following:

system( "mysql --user=$dbuser --password=$dbpswd --host=$host $mysqldb < $filename",$result);

Note that hosts may have different paths for the mysql commands.  For example /usr/local/bin/mysqldump could be required.