PHP: mysql_select_array

A function that accepts an array listing columns in a MySQL database table along with a reference and value and extracts

Engineered specifically for use with a user table that will list only those properties of the user that I specify in the passed array.

Passes array by reference (as opposed to value) so that the passed array (providing the category names for the various user properties) can be synchronized with array of values selected from the database.

/* Fx mysql_select_array
author: tatwell AT gmail DOT com
$ARRAY is a list of columns specifying which info to pull from database
Passed by reference so that the headers may be synchronized with the
values pulled form DB
1 result, returns 1 row; More than 1 result, returns an array of rows
(as arrays)
******************************/
function mysql_select_array(&$ARRAY, $ref_column, $value, $table='default',
$db_name='default', $limit_one=TRUE) {

// check database
if ( $db_name == 'default' ) {
if ( defined('DB_NAME') ) {
$db_name = DB_NAME;
} else {
trigger_error("db_name not defined for __FUNCTION__", E_USER_WARNING);
return FALSE;
}
}

// check table
if ( $table == 'default' ) {
if ( defined('TABLE') ) {
$table = TABLE;
} else {
trigger_error("table not defined for __FUNCTION__", E_USER_ERROR);
return FALSE;
}
}


// initialize values for building select phrase from array
$select_phrase = ''; /* initialize string */
$drop_offset = 0; /* index marker for tracking array */

// build select phrase and unset any array values which do not match
up with a TABLE COLUMN
foreach ( $ARRAY as $listed_col ) {
if ( mysql_fetch_row(mysql_query("SHOW COLUMNS FROM `$table`
LIKE '$listed_col'")) ) {
$select_phrase .= "`$listed_col`, "; /* array value match TABLE COLUMN */
} else {
unset($ARRAY[$drop_offset]); /* array value does not match TABLE COLUMN */
}
$drop_offset++;
} /* end column check */

// clean up query phrase
$select_phrase = trim(substr($select_phrase, 0,
strrpos($select_phrase, ',')));

// if no columns matched, warn and return
if ( !strlen($select_phrase) ) {
$report = "0 columns not found in table '$table'";
trigger_error($report, E_USER_WARNING);
return FALSE;
}

// build query
$select_query = "SELECT $select_phrase FROM `$table`
WHERE `$ref_column` = '$value'";

if ($limit_one) {
$select_query .= 'LIMIT 1';
}

// open db
open_db($db_name);

// execute query
$result = mysql_query($select_query)
or mysql_trigger_error($select_query);

// fetch result
if ( mysql_num_rows($result) > 0 && $limit_one ) {
$output = mysql_fetch_row($result);
}
elseif ( mysql_num_rows($result) > 0 ) {
$i = 0; /* array index */
while ($row = mysql_fetch_row($result)) {
$rows[$i] = $row;
$i++;
}
$output = $rows;
} else { /* no results */
$report = "0 rows found in table '$table'";
trigger_error($report, E_USER_NOTICE);
mysql_free_result($result);
return $report;
}

// return results
mysql_free_result($result);
return $output;

} /* end Fx */
/*****************************/