PHP mySQL Database backup
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);
}
?>
ALTER TABLE my_table CHANGE created created TIMESTAMP NOT NULL DEFAULT 0, CHANGE modified modified TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL
Programatically edit a CSV file
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);
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)
Removing orphaned virtfs mounts
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
Timthumb and Ajax
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->cachefile) - (strlen($this->filePrependSecurityBlock) + 6); $this->sendImageHeaders($imgType, $imageDataSize); $bytesSent = @fpassthru($fp);
with this:
$imageDataSize = filesize($this->cachefile) - (strlen($this->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->cachefile);
$binary = fread($fp,$fsize);
$bin64 = base64_encode($binary);
fclose($fp);
echo 'data:image/bmp;base64,'.$bin64;
return true;
} else {
$this->sendImageHeaders($imgType, $imageDataSize);
$bytesSent = @fpassthru($fp);
}
Then in my HTML page I have a bit of jQuery as follows:
load_link = encodeURI("&src=" + link + "&h=194&w=263&marko=1");
$.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.
Parsing XML: xpath attributes
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'];
}
XML parsing and Namespaces
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);
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>
Conditionally load jQuery
<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>
Test if variable exists
if(typeof(a) != "undefined"){...}
CSS transparencies
.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;
}
