Skip to content
Dec 11 11

PHP mySQL Database backup

by moflint

There are times when you need to get an SQL dump of a database but you don’t have access to management tools AND system(), exec() and passthru() commands are disabled. This script from David Walsh will do the trick:

<?php
/**
 * Thaks to David Walsh (http://davidwalsh.name/backup-mysql-database-php)
 */

backup_tables('localhost', 'root', '', 'bvcb_forms');

/* backup the db OR just a table */

function backup_tables($host, $user, $pass, $name, $tables = '*') {

    $return = '';

    $link = mysql_connect($host, $user, $pass);
    mysql_select_db($name, $link);

    //get all of the tables
    if ($tables == '*') {
	$tables = array();
	$result = mysql_query('SHOW TABLES');
	while ($row = mysql_fetch_row($result)) {
	    $tables[] = $row[0];
	}
    } else {
	$tables = is_array($tables) ? $tables : explode(',', $tables);
    }

    //cycle through
    foreach ($tables as $table) {
	$result = mysql_query('SELECT * FROM ' . $table);
	$num_fields = mysql_num_fields($result);

	$return.= 'DROP TABLE ' . $table . ';';
	$row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE ' . $table));
	$return.= "\n\n" . $row2[1] . ";\n\n";

	for ($i = 0; $i < $num_fields; $i++) {
	    while ($row = mysql_fetch_row($result)) {
		$return.= 'INSERT INTO ' . $table . ' VALUES(';
		for ($j = 0; $j < $num_fields; $j++) {
		    $row[$j] = addslashes($row[$j]);
		    $row[$j] = preg_replace("/\n/", "/\\n/", $row[$j]);
		    if (isset($row[$j])) {
			$return.= '"' . $row[$j] . '"';
		    } else {
			$return.= '""';
		    }
		    if ($j < ($num_fields - 1)) {
			$return.= ',';
		    }
		}
		$return.= ");\n";
	    }
	}
	$return.="\n\n\n";
    }

    //save file
    $handle = fopen('db-backup-' . time() . '-' . (md5(implode(',', $tables))) . '.sql', 'w+');
    fwrite($handle, $return);
    fclose($handle);
}
?>
Nov 16 11

Timestamp columns… when phpMyAdmin won’t let you change defaults!

by moflint
ALTER TABLE my_table CHANGE created created TIMESTAMP NOT NULL DEFAULT 0, CHANGE modified modified TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL
Oct 24 11

Programatically edit a CSV file

by moflint

Here’s an example of adding a leading zero to columns 2 and 3 of the CSV file:

$input = fopen('numbers.csv','r');
$output = fopen('numbers_touched.csv','w');
while($csv_line = fgetcsv($input,1024)) {
	$line = array();
	foreach ($csv_line as $k => $field){
		if ($k == 2 || $k == 3) {
			$field = '0' . $field;
		}
		$line[] = $field;
	}
	fputcsv($output,$line);
}
fclose($input);
fclose($output);
Oct 20 11

Bash backup script

by moflint

Here’s a script for backing up directories and databases. All files are compresses, and files older than 30 days are removed.

#
# Backup script
#
# Make compressed backups of directories/files, and old backups

# Format: YEAR MONTH DAY - HOUR MINUTE SECOND
DATE=$(date +%Y%m%d-%H%M%S)

# backup directories and files
tar -czf "/backup/target_file-$DATE.tar.gz" /home/username/public_html/home/
tar -czf "/backup/target_file_forwarders-$DATE.tar.gz" /etc/valiases/domain_name.co.uk

# backup databases
# this could be done in one command, but I want to keep separate files
mysqldump database1 | gzip -f > "/backup/databases/database1-$DATE.sql.gz"
mysqldump database2 | gzip -f > "/backup/databases/database2-$DATE.sql.gz"

# delete files older than 30 days recursively on this path
find /backup -type f -mtime +30 -exec rm -f {} \;

Oct 20 11

Find mysql database size from command line

by moflint

SELECT table_schema "database name", sum( data_length + index_length ) / 1024 / 1024 "Database size in MB" FROM information_schema.TABLES GROUP BY table_schema;

Oct 19 11

Make a compressed tarball, and extract the same

by moflint

Create a bzip’ed tarball
tar -cjvf test.tbz home

-c tells tar to create an archive. j tells it to bzip the file. v makes it display the names of of the files as it processes them. z is to compress the file and f tells tar to use the next name in the command (test.tbz in the example). The final argument is the directory to be processed.

Likewise create a gzip’ed tarball
tar -czvf test.tar.gz home

The z flag tells it to use gzip compression.

Extracting is simple too:
tar -xzvf test.tar.gz (gzip) or tar -xjvf test.tbz (bzip)

Oct 19 11

Removing orphaned virtfs mounts

by moflint

Shows who is logged in:
# who

Anybody running a virtfs, or any orphaned virtfs users?
cat /proc/mounts | grep virtfs

Unmount them!
for i in `cat /proc/mounts |grep virtfs |grep user_name_here |awk ‘{print$2}’`; do umount $i; done

Sep 29 11

Timthumb and Ajax

by moflint

Timthumb is a great tool for cropping/resizing images dynamically at the time the page loads. But what if you want to update the src of an image tag with a new image, for example, in an image carousel?

You can use timthumb via an ajax call, but the resulting data is binary so you have to do two things: 1) convert the binary to base64, 2) add a prefix to the src attribut it to tell the browser how to handle it.

So converting the binary data to base64 is done inside the timthumb script. What I did was check for a param in the GET and, if present, I converted the data to base64. In the timthumb serveCacheFile() function I replaced these line:

$imageDataSize = filesize($this-&gt;cachefile) - (strlen($this-&gt;filePrependSecurityBlock) + 6);
$this-&gt;sendImageHeaders($imgType, $imageDataSize);
$bytesSent = @fpassthru($fp);

with this:

		$imageDataSize = filesize($this-&gt;cachefile) - (strlen($this-&gt;filePrependSecurityBlock) + 6);

		if (isset($_GET['marko'])){
			// Look out for the 'marko' flag. This is serving a base64 encoded string so
			// that an HTML image tag can display the file in it's src attribute. Useful
			// when Ajax is needed to resize files for a carousel.
			$fsize = filesize($this-&gt;cachefile);
	        $binary = fread($fp,$fsize);
	        $bin64 = base64_encode($binary);
			fclose($fp);
			echo 'data:image/bmp;base64,'.$bin64;
			return true;
		} else {
			$this-&gt;sendImageHeaders($imgType, $imageDataSize);
			$bytesSent = @fpassthru($fp);
		}

Then in my HTML page I have a bit of jQuery as follows:

	load_link = encodeURI(&quot;&amp;src=&quot; + link + &quot;&amp;h=194&amp;w=263&amp;marko=1&quot;);
	$.get('/images/timthumb.php', load_link, updateLink);

	function updateLink(data) {
	    $('#flickr-first-photo').attr('src',data);
	}

Where the ‘link’ var is the external file, in my case, a flickr hosted image. You can see I’m passing marko=1 in as a GET param. This is my flag that I pick up in the timthumb.php script. The function updateLink() adds the base64 encoded data to the src of the image with id flickr-first-photo.

Jun 10 11

Parsing XML: xpath attributes

by moflint

Here are two examples of getting attribute values using an xpath query: one using simpleXML and one using DOM document. Both queries are on namespaces within the XML file.

First here’s the DOM Document version:

$dom = new DomDocument();
// it's possible to use the static method ::load instead of intantiating
$dom->load('http://api.flickr.com/services/feeds/groups_pool.gne?id=12345678@N00&format=rss_200');
$xpath = new DOMXPath($dom);
$result = $xpath->query("//media:thumbnail/@url");
foreach ($result as $val) {
    // make an array of thumbnail URLs from a Flickr group
    $thumbsArray[] = $val->nodeValue;
}

… and the SimpleXML version:

$xml = simplexml_load_file('http://api.flickr.com/services/feeds/groups_pool.gne?id=12345678@N00&format=rss_200');
$result = $xml->xpath('//media:thumbnail/@url');
foreach ($result as $val) {
    // make an array of thumbnail URLs from a Flickr group
    $thumbsA[] = (string)$val['url'];
}
May 11 11

.htaccess for canonical issues

by moflint

Options +FollowSymLinks
RewriteEngine On
RewriteCond %{HTTP_HOST} ^markflint\.net$ [NC]
RewriteRule ^(.*)$ http://www.markflint.net/$1 [R=301,L]

Apr 26 11

XML parsing and Namespaces

by moflint

Here’s a simpleXML script I used to parse a feed. There are MP3 links that are under the namespace xmlns:media=”http://search.yahoo.com/mrss/”. The links are in the feed like this:

<media:group>
    <media:content url='linkformp3.mp3' />
</media:group>

To get at the url attribute you first have to access the ‘media’ namespace by using children('media',true). This tells the parser to find children of the namespace prefix ‘media’. Another way to do this is to use the url declared for the namespace, but using children(‘media’,true) seems easier. Here’s the full script:

<?php
$file = 'http://www.ucbmedia.co.uk/feeds/wft/index.php';
$xml = simplexml_load_file($file, 'SimpleXMLElement', LIBXML_NOCDATA);

foreach ($xml->channel->item as $item) {
    echo "<b>".$item->title."</b><br><br>";
    echo $item->description."<br><br>";
    echo $item->pubDate."<br><br>";
    // get children for namespace prefixed 'media'
    $media = $item->children('media',true);
    $mp3 = $media->group->content->attributes();
    echo $mp3['url']."<br><hr><br><br>";
}
?>

Also notice the way of loading a feed and preserving CDATA in simpleXML, simplexml_load_file($file, 'SimpleXMLElement', LIBXML_NOCDATA);

Apr 14 11

Map a local domain using apache httpd.conf and windows hosts file

by moflint

Add a host file definition, eg:

mydomain.dev 127.0.0.1

This tells your PC to send that domain to localhost. Now in apache’s httpd.conf you set up a virtual host to map that domain on localhost to a ‘DocumentRoot’ directory on your machine:

<VirtualHost 127.0.0.1>
ServerName mydomain.dev
DocumentRoot "C:\xampp\htdocs\mydomain"
</VirtualHost>
Apr 9 11

Conditionally load jQuery

by moflint
<script type="text/javascript">
// load jQuery if not already loaded
if (typeof jQuery == 'undefined') {
   var head = document.getElementsByTagName("head")[0];
   script = document.createElement('script');
   script.id = 'jQuery';
   script.type = 'text/javascript';
   script.src = 'pathto/jQuery/jquery-1.5.1.min.js';
   head.appendChild(script);

}
</script>
Apr 3 11

Test if variable exists

by moflint
if(typeof(a) != "undefined"){...}
Mar 26 11

CSS transparencies

by moflint
.transparency_class {
  /* IE 8 */
  -ms-filter: "progid:DXImageTransform.Microsoft.Alpha(Opacity=90)";

  /* IE 5-7 */
  filter: alpha(opacity=90);

  /* Netscape */
  -moz-opacity: 0.9;

  /* Safari 1.x */
  -khtml-opacity: 0.9;

  /* Modern browsers */
  opacity: 0.9;
}