I just upgraded to PHP 5.3 on my Windows development box, and ran into an issue making any database connection with the MySQL and MySQLi drivers. Pages not making any database connections worked fine, but any page with a database connection would sit and hang for about a minute, and then throw an error.
PHP Warning: mysqli::mysqli(): [2002] A connection attempt failed because the connected party did not (trying to connect via tcp://localhost:3306) in xxx.php on line 2
PHP Warning: mysqli::mysqli(): (HY000/2002): A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.
in xxx.php on line 2
PHP bug #45150 suggests that this is due to issues resolving localhost on Windows. This is partially correct, more specifically it's an issue with localhost resolving to ::1 when IPv6 is enabled.
While it feels like Windows has shoehorned in IPv6 support, the blame seems to lie with MySQL, which won't support IPv6 until version 6.0.
I'm not sure why the timeout isn't instant, the port is closed, so it could be partially a problem with PHP or Windows.
> nmap -sT -p 80,3306,3389 -6 ::1
Starting Nmap 5.00 ( http://nmap.org ) at 2009-11-20 15:48 Mountain Standard Time
Interesting ports on cfg64 (::1):
PORT STATE SERVICE
80/tcp closed http
3306/tcp closed mysql
3389/tcp open ms-term-serv
Nmap done: 1 IP address (1 host up) scanned in 3.14 seconds |
The Fix
Open up %windir%\system32\drivers\etc\hosts with a text editor and comment out the line that looks like:
Prefix it with a #, like so:
Save it and your PHP/MySQL connections will immediately begin working. You could also use 127.0.0.1 in your connection string instead of localhost, but I didn't want to change code in innumerable files.
I wanted to cleanly display tabular data from a PHP command line script, and I like how MySQL formats its output. This is quick, somewhat inefficient, hacky code, but it got the job done. You'll find nothing to be proud of in this code; I don't want to spend more time writing this post about it than I did writing it :)
/**
* Out a MySQL-style table of data
*
* @param array $data Associative array of data to output.
* @param array $header_keys Optional; Assoc array of display names to use for headers. Keys must match those of $data. Defaults to keys of $data.
* @param string $glue String to join lines with, defaults to newline.
* @return string
*
*/
function build_table($data, $header_keys=array(), $glue="\n") {
$table = '';
$data_fmt = array();
$divider_row = array();
$header_fmt = array();
$keys = array_keys($data[0]);
$col_lengths = array_flip($keys); // used to determine the max column width
if( empty($header_keys) ) {
$header_keys = array_combine($keys, $keys);
}
// set the base max length to the length of our header keys
foreach( $keys as $key ) {
$col_lengths[$key] = strlen($header_keys[$key]);
}
foreach( $data as $row ) {
foreach( $keys as $key ) {
$col_lengths[$key] = max($col_lengths[$key], strlen($row[$key]));
}
}
foreach( $keys as $key ) {
$data_fmt[] = '%-' . $col_lengths[$key] . 's';
$header_fmt[] = '%-' . $col_lengths[$key] . 's';
$divider_row[] = str_pad('', $col_lengths[$key]+2, '-'); // fill the spacing
}
$data_fmt = '| ' . implode(' | ', $data_fmt) . ' |';
$divider_row = '+' . implode('+', $divider_row) . '+';
$header_fmt = '| ' . implode(' | ', $header_fmt) . ' |';
// assemble the table
$table .= $divider_row . $glue;
$table .= vsprintf($header_fmt . $glue, $header_keys);
$table .= $divider_row . $glue;
foreach( $data as $row ) {
$table .= vsprintf($data_fmt . $glue, $row);
}
$table .= $divider_row . $glue;
return $table;
} |
The output will be similar to:
+--------------------+--------------------+---------------+
| First | Second col | Random Column |
+--------------------+--------------------+---------------+
| 60386980488376 | 597655305189574649 | 51 |
| 959432 | 459191 | 396802 |
| 73874213 | 570702 | 771 |
| 144579584678722975 | 892300317939345 | 59180 |
| 293172 | 314 | 127725766727 |
+--------------------+--------------------+---------------+ |
It only works with associative arrays, but that's easy enough to fix. You can proudly test it out using this glorious snippet:
$data = array();
function get_rand() {
$max=rand(1,6);
$str='';
for( $i = 0; $i < $max; $i++ ) {
$str .= rand(10, 1000);
}
return $str;
}
for( $i = 0; $i < 5; $i++ ) {
$row = array(
'one' => get_rand(),
'two' => get_rand(),
'three' => get_rand(),
);
$data[] = $row;
}
$disp = array(
'one' => 'First',
'two' => 'Second col',
'three' => 'Random Column',
);
echo build_table($data, $disp); |
Another helper function for ColdFusion, this one to replicate PHP's array_merge which allows you to merge multiple arrays. In this case I'm only concerned with associative arrays, which ColdFusion calls structs.
CFMX provides StructAppend which will merge two (and only two) structures, optionally overwriting keys. I always want to overwrite keys, and I want to merge unlimited structures.
<cfscript>
function struct_merge() {
var base = {};
var i = 1;
for( i = 1; i LTE ArrayLen(arguments); i=i+1 ) {
if( IsStruct(arguments[i]) ) {
StructAppend(base, arguments[i], true);
}
}
return base;
}
</cfscript> |
Usage
<cfscript>
one = {
a = "a",
b = "b",
c = "c",
d = "d",
e = "e"
};
two = {
1 = "one",
2 = "two",
3 = "three",
4 = "four",
a = "one_SetByTwo",
c = "three_SetByTwo"
};
three = {
a = "one_SetByThree",
2 = "b_SetByThree"
};
new = struct_merge(one, two, three);
</cfscript>
<cfdump var=#new#> |
Result:
Almost anyone who has ever heard me talk about about ColdFusion has undoubtedly heard me say it sucks. It's slowly improving (CFMX 8 finally supports JavaScript-style arithmetic operators), but it has a ways to go. For instance it doesn't support ternary operators.
Normal if-else assignment loop
if( $today_is_saturday ) {
$weekend = true;
} else {
$weekend = false;
} |
Assignment using a ternary operator
$weekend = $today_is_saturday ? true : false; |
But I digress. There is also no easy way to tell if a variable is empty, unlike PHP's empty() construct which will return true for any of the following:
- "" (an empty string)
- 0 (0 as an integer)
- "0" (0 as a string)
- NULL
- FALSE
- array() (an empty array)
- var $var; (a variable declared, but without a value in a class)
In ColdFusion you need a User Defined Function (UDF) to accomplish this, and I've written one that mimics this as closely as possible. CF doesn't have a null data type, and if it receives one it'll convert it to an empty string.
The Code
Copy and paste this somewhere inside your code and call it using empty(varName). The varName variable must be defined.
<cfscript>
function empty(val) {
/**
* Return TRUE if one of the following conditions
* for a defined variable is met:
* - A *trimmed* string is empty
* - An array or struct is empty
* - A query has a recordcount of 0
* - A bool is false
* - A number is 0
*
* For all other values including IsDate(testVar) it returns false.
*
* Similar to the php empty() function, www.php.net/empty
*
* @param val Variable to test. (Required)
* @return Returns a boolean.
* @author Corey Gilmore (http://coreygilmore.com/)
*
*/
if( IsSimpleValue(val) ) {
if( IsDate(val) ) {
return false; // no validation here
} else if( IsNumeric(val) ) {
return YesNoFormat(val EQ 0);
} else if( IsBoolean(val) ) {
return NOT YesNoFormat(val);
} else {
// assume string
return NOT YesNoFormat( Len(Trim(val)) );
}
} else {
if( IsArray(val) ) {
return NOT YesNoFormat( ArrayLen(val) );
} else if( IsStruct(val) ) {
return StructIsEmpty(val);
} else if( IsQuery(val) ) {
return NOT val.recordcount;
}
}
return false;
}
</cfscript> |
