Pulling a single value from a database

Sometimes you just want to pull a single value from the database without a whole lot of work. Here's how to do it in a single line;

// mysql_result() function is used to pull a single value from the database
$value = mysql_result(mysql_query("SELECT price FROM stuff_we_sell WHERE product = 'widget'"), 0);

Converting IP addresses to sortable form in MySQL & PHP

IP addresses can't readily be sorted numerically because they are strings and the periods in the dotted-quad format common to IP addresses confuses things. For instance, in a sorted list, 143.16.20.11 will sort before 23.45.1.78 even though what you really want is to sort by the first octet (23 & 143), then by the second octed (16 & 45) and so on.

PHP and MySQL provide functions to convert IP addresses to sortable format.

MySQL

Two functions are provided for use in MySQL query. When your IP addresses are stored in a database, you can pull them ready to sort.

INET_ATON() Returns the numeric value of an IP address
INET_NTOA() Returns the IP address converted from a numeric value

Remember that if you need both representations you can pull the value twice in the same query, i.e.

SELECT ip AS ip_dotted_quat,INET_ATON(ip) as ip_sortable FROM mytable

This provides a clean set of data for your PHP or other scripting code to work with.

PHP

You have similar options in PHP.

ip2long("127.0.0.1″) converts the IP to a long integer.

long2ip() converts the long integer back to a dotted-quad IP address.

The online PHP manual suggests using the two in combination to validate an IP as shown here

// make sure IPs are valid. also converts a non-complete IP into
// a proper dotted quad as explained below.
$ip = long2ip(ip2long("127.0.0.1″)); // "127.0.0.1″
$ip = long2ip(ip2long("10.0.0″)); // "10.0.0.0″
$ip = long2ip(ip2long("10.0.256″)); // "10.0.1.0″
?>

Stripping the Query String from a URL in PHP

When you want to work with query strings on a website, you'll sometimes need to strip the query string off the URL. This short code snippet will lead the way.

list($shorturl) = explode('?','http://www.website.com.com?page=7');
echo $shorturl;

Capturing the filename from a path in PHP

When you want to isolate the base filename from a path string, this is the way to do it:

$path = "/home/project/folder/mypage.php";
$file = basename($path);          // $file is "mypage.php"
$file = basename($path, ".php");      // $file is "mypage"

See dirname() and pathinfo() for related information.

Time Stamp differences in MySQL and PHP

MySQL and PHP handle time and data data in different ways, and it's important to be aware of the difference.

Both these environments have a TIMESTAMP construct, but they're not entirely compatible.

While PHP uses a UNIX timestamp format (an integer representing the number of seconds since January 1st, 1970) MySQL's TIMESTAMP data type uses a YYYY-MM-DD HH:MM:SS format.

You can overcome this by using the mysql funtion UNIX_TIMESTAMP() to pull dates in the UNIX format native to PHP.

Making this conversion in your SQL query is generally more efficient and simple than converting in PHP. Remember, you always want to work your data as much as you can in the SQL query and deliver a clean set of data to PHP.

In fact, if you like you can pull the same attribute in different ways in the same query, creating what I'll call pseudo-attributes, as in;

SELECT UNIX_TIMESTAMP(date_attribute) AS unix_date,date_attribute AS nice_date FROM mytable;

This gives you two dates to work with in PHP, represented as unix_date and nice_date for a more human readable format, and balances the processing between your web and database servers.

Setting a time zone offset in PHP

If your server is in a different time zone than your operations or your user-base, you're going to face some confusion when you use time formats to display things like time last updated, or post times.

The way to resolve this is to use the putenv() function.

putenv("TZ=US/Eastern");

To see this in action, run the following bit of code on one of your pages:

echo "Original Time: ". date("h:i:s")."\n";
putenv("TZ=US/Eastern");
echo "Adjusted Time: ". date("h:i:s")."\n";

I found a good listing of the time zones at http://www.theprojects.org/dev/zone.txt.

This is a good thing to put in the include that provides the opening page structure for your site.

Converting row attributes to variables

When you read a row from your dataset and want to work with it, like building table rows, it can be hard to write, debug and maintain your code using the conventional syntax that requires you to move in and out of a quoted string, inserting elements from the row array like this;

while ($row = mysql_fetch_assoc($result)) {
// work with the data from your row
echo "".row['name']."".row['address']."";
}

It's a lot easier to do this if you convert your row elements to simple variables which can be placed directly in the quoted string, like this;

while ($row = mysql_fetch_assoc($result)) {
// create a variable for each attribute
foreach($row as $var => $value){
$$var = $value;
}

// now work with the data from your row
echo "$name$address";
}

Your variables are created with the name of the table attribute, thus $row['address'] becomes $address.

The benefits of this second approach would far outweigh the processing cost of converting the row to simple variables unless you're working with a very high volume site where processor cycles are a significant issue. In that case, this simplified method would serve you well during the development phase, switching to row elements just before final testing.