SQL Injection Safe Queries Redux
if(is_numeric($value)) return $value;
elseif(empty($value)) return 'NULL';
elseif(is_string($value)) return '\''.mysql_real_escape_string($value).'\'';
elseif(is_array($value)) return implode(',',array_map('mysql_safe_string',$value));
}
function mysql_safe_query($format) {
$args = array_slice(func_get_args(),1);
$args = array_map('mysql_safe_string',$args);
$query = vsprintf($format,$args);
$result = mysql_query($query);
if($result === false) echo '<div class="mysql-error"><strong>Error: </strong>',mysql_error(),'<br/><strong>Query: </strong>',$query,'</div>';
return $result;
}
// example
$result = mysql_safe_query('SELECT * FROM users WHERE username=%s', $username);
Just use mysql_safe_query in place of mysql_query and you should be safe from SQL injection attacks. Use %s in place of any variables, and append them as arguments. Don't quote your strings, it'll be done for you automatically. Arrays will be flattened for you automatically and concatenated with commas. You can delete the error-echoing line if you want, but I find it useful for development.
Facebook PHP API: Get the names of all your friends
If you didn't already know, Facebook has an API that exposes quite a darn bit information. You can easily query this data using their API, but each request takes a fair bit of time. Typically, to get the names of all your friends, first you have to grab a list of the user ids of all your friends, and then query each and every single one to get their names. For me, that's about 300 cross-server requests, which will almost certainly cause my server to time out, and probably force Facebook to reject me. Fortunately, Facebook has also created their own MySQL-like language, FQL (I pronounce it feequel), which lets you do some of these "complicated" queries in a single call. Here's a simple example I wrote:
require_once 'facebook-platform/php/facebook.php';
$appapikey = 'yourapikeyhere';
$appsecret = 'yoursecretkey';
$facebook = new Facebook($appapikey, $appsecret);
$user_id = $facebook->require_login();
$result = fql_query('SELECT name FROM user WHERE uid IN (SELECT uid2 FROM friend WHERE uid1=%s)', $user_id);
pr($result);
function fql_query($query) {
global $facebook;
$args = array_slice(func_get_args(), 1);
return $facebook->api_client->fql_query(vsprintf($query, $args));
}
function pr($arr) {
echo '<pre>';
print_r($arr);
echo '</pre>';
}
You'll notice I've included two of my favorite wrapper functions. You can unroll them if you want. Anyway, just thought I'd share 
I prefer writing FQL than trying to remember all their API calls anyway.
Oh... and just FYI, this prints out something like this:
Array
(
[0] => Array
(
[name] => Mark Zuckerberg
)
[1] => Array
(
[name] => Tom Riddle
)
I hate it when people leave it a mystery what exactly their example is doing!
Get Domain & Subdomain from URL
define('PROTOCOL', strtolower(substr($_SERVER['SERVER_PROTOCOL'],0,strpos($_SERVER['SERVER_PROTOCOL'],'/'))).'://');
define('SUBDOMAIN', $matches[1]);
define('DOMAIN', $matches[2]);
define('HERE', $_SERVER['REQUEST_URI']);
If you're at http://www.sub.domain.com/page, then:
PROTOCOL = http://
SUBDOMAIN = sub
DOMAIN = domain.com
HERE = /page
Insert into MySQL datetime column from PHP
You can use this simple function to convert a unix timestamp (like the one obtained from time()) to MySQL datetime format:
if(!isset($timestamp)) $timestamp = time();
return date('Y-m-d H:i:s', $timestamp);
}
I'm using this instead of MySQL's NOW() function because my MySQL server isn't localized to my timezone, but my PHP is by usage of date_default_timezone_set.
Edit: And you can use this function to convert back the other way (you can do this directly in the SQL too, but just in case)
list($date, $time) = explode(' ', $datetime);
list($year, $month, $day) = explode('-', $date);
list($hour, $minute, $second) = explode(':', $time);
return mktime($hour, $minute, $second, $month, $day, $year);
}
Human-readable file size in JavaScript
"size" is in bytes, the rest you should be able to figure out.
var units = ['B', 'KB', 'MB', 'GB', 'TB', 'PB', 'EB', 'ZB', 'YB'];
var i = 0;
while(size >= 1024) {
size /= 1024;
++i;
}
return size.toFixed(1) + ' ' + units[i];
}
PHP code for handling recursive categories/hierarchical data
I'm just going to paste the code I wrote here... you can read some other tutorial to understand what it's doing, but for some reason they have really incomplete examples, so here's the whole shabang. It will even keep your nodes in alphabetical order. It has a function for displaying a drop down list too; wrap it in <select> tags.
class Category {
function getParent($id) {
return mysql_safe_query('SELECT parent.* FROM categories AS child, categories AS parent WHERE parent.id = child.parent_id AND child.id = %s LIMIT 1', $id);
}
function getChildren($id, $direct = false) {
if($direct) {
return mysql_safe_query('SELECT * FROM categories WHERE parent_id = %s ORDER BY left_val ASC', $id);
} else {
return mysql_safe_query('SELECT child.* FROM categories AS child, categories AS parent WHERE child.left_val >= parent.left_val AND child.right_val <= parent.right_val AND parent.id = %s ORDER BY child.left_val ASC', $id);
}
}
function insert($name, $parent_id) {
$result = mysql_safe_query('SELECT * FROM categories WHERE parent_id <=> %s AND name < %s ORDER BY name DESC LIMIT 1', $parent_id, $name);
if(mysql_num_rows($result) > 0) {
// insert between children
$row = mysql_fetch_assoc($result);
mysql_safe_query('UPDATE categories SET left_val = left_val + 2 WHERE left_val > %s', $row['right_val']);
mysql_safe_query('UPDATE categories SET right_val = right_val + 2 WHERE right_val > %s', $row['right_val']);
mysql_safe_query('INSERT INTO categories (parent_id, left_val, right_val, name) VALUES (%s, %s, %s, %s)',
$parent_id, $row['right_val'] + 1, $row['right_val'] + 2, $name);
} else {
$result = mysql_safe_query('SELECT * FROM categories WHERE id <=> %s LIMIT 1', $parent_id);
if(mysql_num_rows($result) > 0) {
// insert first child
$row = mysql_fetch_assoc($result);
mysql_safe_query('UPDATE categories SET left_val = left_val + 2 WHERE left_val > %s', $row['left_val']);
mysql_safe_query('UPDATE categories SET right_val = right_val + 2 WHERE right_val > %s', $row['left_val']);
mysql_safe_query('INSERT INTO categories (parent_id, left_val, right_val, name) VALUES (%s, %s, %s, %s)',
$parent_id, $row['left_val'] + 1, $row['left_val'] + 2, $name);
} else {
// insert at beginning of tree
mysql_safe_query('UPDATE categories SET left_val = left_val + 2, right_val = right_val + 2');
mysql_safe_query('INSERT INTO categories (parent_id, left_val, right_val, name) VALUES (%s, %s, %s, %s)',
null, 1, 2, $name);
}
}
}
function delete($id) {
$result = mysql_safe_query('SELECT * FROM categories WHERE id = %s LIMIT 1', $id);
$row = mysql_fetch_assoc($result);
mysql_safe_query('DELETE FROM categories WHERE id = %s LIMIT 1', $id);
mysql_safe_query('UPDATE categories SET parent_id = %s WHERE categories.parent_id = %s', $row['parent_id'], $id); // relink parent
mysql_safe_query('UPDATE categories SET left_val = left_val - 1, right_val = right_val - 1 WHERE left_val > %s AND right_val < %s', $row['left_val'], $row['right_val']); // update children
mysql_safe_query('UPDATE categories SET left_val = left_val - 2 WHERE left_val > %s', $row['right_val']); // update left values of nodes to right
mysql_safe_query('UPDATE categories SET right_val = right_val - 2 WHERE right_val > %s', $row['right_val']); // update right values of encompassing nodes and nodes to right
}
function incPostCount($id, $amount=1) {
$result = mysql_safe_query('SELECT * FROM categories WHERE id = %s', $id);
$row = mysql_fetch_assoc($result);
return mysql_safe_query('UPDATE categories SET num_posts = num_posts + %s WHERE left_val <= %s AND right_val >= %s',
$amount, $row['left_val'], $row['right_val']);
}
function decPostCount($id, $amount=1) {
return self::incPostCount($id, -$amount);
}
function getPath($id) {
$result = mysql_safe_query('SELECT * FROM categories WHERE id = %s', $id);
$row = mysql_fetch_assoc($result);
return mysql_safe_query('SELECT * FROM categories WHERE left_val <= %s AND right_val >= %s ORDER BY left_val ASC',
$row['left_val'], $row['right_val']);
}
function printOptions($selected=null, $format=null, $mult=null, $add=null) {
if(!isset($format)) $format = '<option value="%s" style="padding-left:%spx"%s>%s</option>';
if(!isset($mult)) $mult = 15;
if(!isset($add)) $add = 3;
$depth = 0;
$result = mysql_safe_query('SELECT * FROM categories ORDER BY categories.left_val ASC');
while($row = mysql_fetch_assoc($result)) {
if(isset($last)) {
if($row['left_val'] == $last['left_val'] + 1) {
++$depth;
$next_right = $last_right + 1;
} elseif($row['left_val'] > $last['right_val'] + 1) {
$levels = $row['left_val'] - $last['right_val'] - 1;
$depth -= $levels;
}
}
$last = $row;
if($row['id'] == $selected) $selected_str = ' selected="selected"';
else $selected_str = '';
echo sprintf($format, $row['id'], $depth*$mult+$add, $selected_str, $row['name'], $row['num_posts'], $depth);
}
}
function printTree($format=null) {
if(!isset($format)) $format = '<li><a href="index.php?cat=%s">%s</a> (%s)';
$depth = 0;
$result = mysql_safe_query('SELECT * FROM categories ORDER BY categories.left_val ASC');
echo '<ul>';
while($row = mysql_fetch_assoc($result)) {
if(isset($last)) {
if($row['left_val'] == $last['left_val'] + 1) {
echo '<ul>';
++$depth;
$next_right = $last_right + 1;
} elseif($row['left_val'] > $last['right_val'] + 1) {
$levels = $row['left_val'] - $last['right_val'] - 1;
echo str_repeat('</li></ul></li>', $levels);
$depth -= $levels;
} else {
echo '</li>';
}
}
$last = $row;
echo sprintf($format, $row['id'], $row['name'], $row['num_posts'], $depth);
}
echo str_repeat('</li></ul>', $depth+1);
}
function printAdminTree() {
self::printTree('<li><a href="index.php?cat=%s">%s</a> (<a href="category_delete.php?id=%1$s">Delete</a>)');
}
}
Please leave a comment if you found this useful!
Flash Player 10 on Ubuntu 64-bit
There's a wad of tutorials out there on how to to install this plugin, but I've found they either don't work, or over complicate things.
If you haven't already found it, download the Flash Player 10 64-bit plugin for linux. Then,
sudo cp libflashplayer.so /usr/lib/firefox-3.0.10/plugins
You might be running a different version of Firefox by the time you read this tutorial, so bust a "locate firefox | grep plugins" to find the appropriate folder.