Code Tips, Snippets, Resources, Tutorials And Help
As part of debugging a bit of code I needed to know the longest possible field lengths that a record contains. You might need to know this if you are performing a database migration. The following query returns a field, along with the length of the string, and orders the results by the number of characters in that string.
SELECT field, CHARACTER_LENGTH(field) as fieldCharacterCount FROM table ORDER BY fieldCharacterCount DESCWritten by Philip Norton.
There are quite a few scripts available on the Internet that allow you to dump data from a database into a format that can be used to replicate that database structure elsewhere. The following function is my take on this commonly occurring script.
<?php
function datadump($table, $drop = true, $stripapos = true)
{
$result = "# Dump of $table \n";
$result .= "# Dump DATE : " . date("d-M-Y") ."\n\n";
if ( $drop ) {
if ( $stripapos ) {
$result .= "DROP TABLE IF EXISTS $table;\n";
// dump create table
$createTableQuery = mysql_query("SHOW CREATE TABLE ".$table.";");
$createTable = mysql_fetch_row($createTableQuery);
$result .= str_replace('`', '', $createTable[1]).";\n\n\n\n";
} else {
$result .= "DROP TABLE IF EXISTS `$table`;\n";
// dump create table
$createTableQuery = mysql_query("SHOW CREATE TABLE ".$table.";");
$createTable = mysql_fetch_row($createTableQuery);
$result .= $createTable[1].";\n\n\n\n";
}
} else {
$result .= "TRUNCATE TABLE $table;\n";
}
$query = mysql_query("SELECT * FROM $table");
$num_fields = @mysql_num_fields($query);
$numrow = mysql_num_rows($query);
$columnsRes = mysql_query("SHOW COLUMNS FROM $table;");
$columns = array();
while ( $row = mysql_fetch_assoc($columnsRes) ) {
$columns[$row['Field']] = $row;
}
while ( $row = mysql_fetch_assoc($query) ) {
$result .= "INSERT INTO ".$table." VALUES(";
$fields = array();
foreach ( $row as $field => $data ) {
if ( strpos(strtolower($columns[$field]['Type']), 'int') !== false
|| strpos(strtolower($columns[$field]['Type']), 'float') !== false
|| strpos(strtolower($columns[$field]['Type']), 'tinyint') !== false ) {
if ( strlen($data) > 0 ) {
$fields[] = $data;
} else {
if ( strtolower($columns[$field]['Null']) == 'no' ) {
$fields[] = 0;
} else {
$fields[] = "NULL";
}
}
} elseif ( strpos(strtolower($columns[$field]['Type']), 'datetime') !== false ) {
if ( strlen($data) > 0 ) {
$fields[] = "\"".$data."\"" ;
} else {
if ( strtolower($columns[$field]['Null']) == 'no' ) {
$fields[] = '""';
} else {
$fields[] = "NULL";
}
}
} elseif ( strpos(strtolower($columns[$field]['Type']), 'time') !== false ) {
if ( strlen($data) > 0 ) {
$fields[] = "\"".$data."\"" ;
} else {
if ( strtolower($columns[$field]['Null']) == 'no' ) {
$fields[] = '""';
} else {
$fields[] = "NULL";
}
}
} elseif ( strpos(strtolower($columns[$field]['Type']), 'varchar') !== false
|| strpos(strtolower($columns[$field]['Type']), 'text') !== false
|| strpos(strtolower($columns[$field]['Type']), 'longtext') !== false
|| strpos(strtolower($columns[$field]['Type']), 'mediumtext') !== false ) {
$data = addslashes($data);
$data = trim(ereg_replace("\n", "\\n", $data));
if ( strlen($data) > 0 ) {
$fields[] = "\"".$data."\"" ;
} else {
if ( strtolower($columns[$field]['Null']) == 'no' ) {
$fields[] = '""';
} else {
$fields[] = "NULL";
}
}
} else {
// $columns[$field]['Type'] will contain the datatype
if ( strlen($data) > 0 ) {
$fields[] = "\"".$data."\"" ;
} else {
if ( strtolower($columns[$field]['Null']) == 'no' ) {
$fields[] = '""';
} else {
$fields[] = "NULL";
}
}
}
}
$result .= implode(',', $fields);
$result .= ");\n";
}
return $result . "\n\n\n";
}
You can call the function in the following way. Because this function looks at one table at a time it is necessary to run a SHOW TABLES query in order to get a list of the tables in the database. This is then used to construct the SQL needed to create the tables and insert the data.
$con = mysql_connect('localhost','username','password');
mysql_select_db('thedatabase');
$result = mysql_query("SHOW TABLES;");
while ( $row = mysql_fetch_row($result) ) {
$table = $row[0];
// dump data
echo datadump($table, true, true);
}
The datadump() function takes three parameters. The first is simply a string containing the name of the table. The second is a boolean that causes the outputted SQL to drop and recreate the tables if set to true, otherwise a truncate is used to clear the tables. The third value is a boolean that causes the function to remove any ` in the SQL, which can cause issues sometimes. Only the first parameter is mandatory, the second two default to true, meaning that the tables are dropped and recreated.
The function works by taking the table name and then using this to fetch the syntax needed to recreate the table (if this option is set) before fetching all of the data from the table. It then finds out the structure of the table and uses this to output an insert statement that escapes the data correctly, even if the value is null.
The data_dump() function returns a string so it could potentially write the contents to a file rather than just output them. Printing everything out works if your dataset is quite small. This script does tend to fall over if there is a lot to do so it might be worth writing to a file in that instance as well as increasing the amount of time a script can run for.
Note that you shouldn't rely on the data created by this script. If you are going to use it then I suggest you do a dry run to make sure that everything inserts as expected. This script has undergone several iterations and simple testing on different databases but it is not fully tested.
Written by Philip Norton.
I use my PC for a lot of different things, and I don't necessarily need to have MySQL server running all the time, especially when I want to play a game. So I wondered if there was a simple way in which I could start and stop the server using a simple .bat file.
I had a look at the MySQL website and found a page that details how to start MySQL from the command line. This page suggested that I use the following command.
"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld"
Use the following command to stop the server. In this case the username is root and the password is wibble.
"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqladmin" -u root -pwibble shutdown
However, this just didn't work when I tried to use it in a .bat file. Realising that MySQL installs a service, I wondered if I could use the net command in order to start and stop the server. It turns out that the command is quite easy to use, so to turn the MySQL server on use the following command.
net start mysql
To stop the server use the following command.
net stop mysql
Add these commands to separate files, perhaps called stopmysql.bat and startmysql.bat, and you will have a neat way of turning on and off your MySQL server at the touch of a button.
If you are using Vista you can still do the same thing but you will need to right click and select "Run as administrator" or the script won't have enough privileges to alter the service.
Written by Philip Norton.
There are numerous ways to print out dates and times, and many hours of programming are spent in converting dates from one format to another.
To do this in MySQL you use the STR_TO_DATE() function, which has two parameters. The first parameter is the time to be parsed and the second is the format of that time. Here is a simple example that converts one date format to a MySQL formatted date string.
SELECT STR_TO_DATE('[21/Apr/2009:07:14:50 +0100]', '[%d/%b/%Y:%H:%i:%S +0100]');
This outputs 2009-04-21 07:14:50.
Using this function is quite intuitive and means that you can convert between time formats very easily. Here are a few more examples of this function.
SELECT STR_TO_DATE('21/04/2009', '%d/%m/%Y'); // 2009-04-21
SELECT STR_TO_DATE('04/21/2009', '%m/%d/%Y'); // 2009-04-21
SELECT STR_TO_DATE('2009-04-21 14', '%Y-%m-%d %H'); // 2009-04-21 14:00:00
If you enter a date that can't be translated then you will get an error, take the following code that tries to convert a minute value of 87.
SELECT STR_TO_DATE('2009-04-21 14:87', '%Y-%m-%d %H:%i');
The following error message is returned.
Incorrect datetime value: '2009-04-21 14:87' for function str_to_date
This error can be quite easy to reproduce. For example, if you wanted to parse a time that was in 24 format, you would use the %H for hours. Using the %h value for hours will produce exactly this error.
The following table lists all of the different parameters that are involved in the date formatting features within MySQL. Use this to parse your own dates.
| Specifier | Description |
|---|---|
| %a | Abbreviated weekday name (Sun..Sat) |
| %b | Abbreviated month name (Jan..Dec) |
| %c | Month, numeric (0..12) |
| %D | Day of the month with English suffix (0th, 1st, 2nd, 3rd, ...) |
| %d | Day of the month, numeric (00..31) |
| %e | Day of the month, numeric (0..31) |
| %f | Microseconds (000000..999999) |
| %H | Hour (00..23) |
| %h | Hour (01..12) |
| %I | Hour (01..12) |
| %i | Minutes, numeric (00..59) |
| %j | Day of year (001..366) |
| %k | Hour (0..23) |
| %l | Hour (1..12) |
| %M | Month name (January..December) |
| %m | Month, numeric (00..12) |
| %p | AM or PM |
| %r | Time, 12-hour (hh:mm:ss followed by AM or PM) |
| %S | Seconds (00..59) |
| %s | Seconds (00..59) |
| %T | Time, 24-hour (hh:mm:ss) |
| %U | Week (00..53), where Sunday is the first day of the week |
| %u | Week (00..53), where Monday is the first day of the week |
| %V | Week (01..53), where Sunday is the first day of the week; used with %X |
| %v | Week (01..53), where Monday is the first day of the week; used with %x |
| %W | Weekday name (Sunday..Saturday) |
| %w | Day of the week (0=Sunday..6=Saturday) |
| %X | Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V |
| %x | Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v |
| %Y | Year, numeric, four digits |
| %y | Year, numeric (two digits) |
| %% | A literal % character |
| %x | x, for any x not listed above |
Written by Philip Norton.
Apache can be set up to log all sorts of information. As of Apache 2.2 the basic log file format that a fresh install of Apache will produce will have the following format:
%h %l %u %t "%r" %>s %b
Which doesn't mean a lot to the uninitiated, so here is a short explanation of each.
This would produce the following sort out output.
127.0.0.1 - - [17/Apr/2009:14:12:20 +0100] "GET / HTTP/1.1" 200 515
This information can be converted into a database format by using the LOAD DATA command. First, lets create the table we need to store this log format.
DROP TABLE IF EXISTS `test`.`apachelog`;
CREATE TABLE `test`.`apachelog` (
`remote_host` varchar(17) DEFAULT NULL,
`remote_logname` varchar(45) DEFAULT NULL,
`remote_user` varchar(45) DEFAULT NULL,
`time1` varchar(22) DEFAULT NULL,
`time2` varchar(7) DEFAULT NULL,
`first_line_of_request` text,
`last_request_status` varchar(4) DEFAULT NULL,
`bytes_sent` varchar(10) DEFAULT NULL
)
Here is the command that is used to convert the log file into that table.
LOAD DATA INFILE 'C:/Program Files/Apache Software Foundation/Apache2.2/logs/access.log' INTO TABLE apachelog
FIELDS TERMINATED BY ' ' OPTIONALLY ENCLOSED BY '"' ESCAPED BY ''
LINES TERMINATED BY 'rn';
Note that our table contains two fields for the time. This is because of two factors. The first is that each field is defined by a space, and because the time value contains a space it is split into two fields. The second is that although we say OPTIONALLY ENCLOSED BY '"' to stop the %r output being split apart, we can't give the LOAD DATA command more than one of these fields. As a result the time is split into the two fields, so we just create a table with more than one field for time to accommodate this.
To improve the table we can use a different output format. Take the following slight alteration to our log file format.
LogFormat "%h,%l,%u,%t,"%r",%>s,%b"
This line can be found in your http.conf file or your httpd-vhosts.conf if you have set up virtual hosts.
This will cause our output to look like the following:
127.0.0.1,-,-,[20/Apr/2009:14:42:01 +0100],"GET / HTTP/1.1",200,515
We can now change out two time columns to a single one, setting the datatype to VARCHAR(30) and using the following LOAD DATA syntax to load our data.
LOAD DATA INFILE 'C:/Program Files/Apache Software Foundation/Apache2.2/logs/access.log' INTO TABLE apachelog
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY ''
LINES TERMINATED BY 'rn';
This gives us a much better set of data in our table.
You can also use the following three MySQL commands to convert from the old table format to the new one.
ALTER TABLE apachelog CHANGE time1 time VARCHAR(40);
UPDATE apachelog SET time = CONCAT(time,' ',time2);
ALTER TABLE apachelog DROP COLUMN time2;
The time2 column will now no longer exist.
Written by Philip Norton.
Follow #! code