note 112413 added to ref.pgsql
| From: | AnthonyChadszinow | Date: | Fri, 14 Jun 2013 06:19:58 +0000 |
| Subject: | note 112413 added to ref.pgsql | ||
| Groups: | php.notes | ||
| Request: | Send a blank email to php-notes+get-195205@lists.php.net to get a copy of this message | ||
SQL to PHP array conversion function which works for single & multi-dimension postgres arrays
and all types of escape/control character combinations. All others tested here have failed in edge
cases (multi dims, control characters, escaped control chars) and also don't set the right type
in your PHP array. If you have PostgreSQL 9.2 you can do the json encode in query then decode in
PHP trick but using this function means you don't have to change your query.
Some test cases (as Postgres returns them):
PG TEXT array = {}
PG BOOLEAN array = {}
PG INTEGER array = {}
PG TEXT array = {{{"",blar},{"\",
",NULL}},{{"{","}"},{"\\","\""}},{{"NULL",TRUE},{"\"",FALSE}}}
PG BOOLEAN array = {t,f,NULL}
PG INTEGER array = {-1,0,1,NULL}
function convertSQLArrayToArray($SQLArrayString, $PHPType='string')
{
$SQLArrayString = substr($SQLArrayString, 1, -1);
$values = array();
if ($SQLArrayString === '') {
return $values;
}
$valueBuffer = '';
$valueIsSubArray = FALSE;
$withinDoubleQuotes = FALSE;
$escapeInForce = FALSE;
$SQLArrayLength = strlen($SQLArrayString);
for ($i = 0; $i < $SQLArrayLength; $i++) {
$char = $SQLArrayString[$i];
if ($char === '{') {
if ($withinDoubleQuotes === FALSE) {
if ($valueIsSubArray === FALSE) {
$valueIsSubArray = TRUE;
$openCurlyBraceCounter = 1;
} else {
$openCurlyBraceCounter++;
}
}
$valueBuffer .= $char;
} else if ($char === ',') {
if ($valueIsSubArray === TRUE
|| $withinDoubleQuotes === TRUE
) {
$valueBuffer .= $char;
} else if ($withinDoubleQuotes === FALSE) {
// Time to dump the value buffer.
$values[] = $valueBuffer;
$valueBuffer = '';
}
} else if ($char === '"') {
if ($withinDoubleQuotes === FALSE) {
$withinDoubleQuotes = TRUE;
} else if ($escapeInForce === FALSE) {
$withinDoubleQuotes = FALSE;
} else {
$escapeInForce = FALSE;
}
$valueBuffer .= $char;
} else if ($char === '\\') {
if ($escapeInForce === FALSE) {
$escapeInForce = TRUE;
if ($valueIsSubArray === TRUE) {
$valueBuffer .= $char;
}
} else {
$escapeInForce = FALSE;
$valueBuffer .= $char;
}
} else if ($char === '}') {
if ($withinDoubleQuotes === FALSE) {
$valueBuffer .= $char;
$openCurlyBraceCounter--;
if ($openCurlyBraceCounter === 0) {
$valueBuffer = convertSQLArrayToArray(
$valueBuffer,
$PHPType
);
$valueIsSubArray = FALSE;
}
} else {
$valueBuffer .= $char;
}
} else {
$valueBuffer .= $char;
}//end if
}//end for
$values[] = $valueBuffer;
foreach ($values as $key => $value) {
if (is_array($value) === FALSE) {
if ($value === 'NULL') {
$values[$key] = NULL;
} else if ($PHPType === 'boolean') {
if ($value === 't') {
$values[$key] = TRUE;
} else {
$values[$key] = FALSE;
}
} else if ($PHPType === 'string') {
if (substr($value, 0, 1) === '"') {
$values[$key] = substr($value, 1, -1);
}
} else {
$values[$key] = $value;
settype($values[$key], $PHPType);
}//end if
} else {
break;
}//end if
}//end foreach
return $values;
}
----
Server IP: 69.147.83.199
Probable Submitter: 59.167.129.67
----
Manual Page -- http://php.net/manual/en/ref.pgsql.php
Edit -- https://master.php.net/note/edit/112413
Del: integrated -- https://master.php.net/note/delete/112413/integrated
Del: useless -- https://master.php.net/note/delete/112413/useless
Del: bad code -- https://master.php.net/note/delete/112413/bad+code
Del: spam -- https://master.php.net/note/delete/112413/spam
Del: non-english -- https://master.php.net/note/delete/112413/non-english
Del: in docs -- https://master.php.net/note/delete/112413/in+docs
Del: other reasons-- https://master.php.net/note/delete/112413
Reject -- https://master.php.net/note/reject/112413
Search -- https://master.php.net/manage/user-notes.php