Get from MySQL and assign in one go?

[CodilX]

Hi there,

I've wondered for a long time, is there a way to retrieve an array from MySQL and assign in one go?

For example, if I need to retrieve a country name from a database, I would normally do this:

$get_country = mysql_fetch_array(mysql_query('SELECT * FROM countries WHERE id ="45"'));
$country = $get['name'];

I could use $get_country['name'], but is there a way to assign an array item to a variable, but also having the MySQL fetch array in the same line?

So it would look something like this:

$country = mysql_fetch_array(mysql_query('SELECT * FROM countries WHERE id ="45"'))[AND_ASSIGN_$country['name']_TO_$country];

[penders]

You could use the list() [uk3.php.net] construct...

list($country, $field2, $field3) = mysql_fetch_array( 
  mysql_query('SELECT country, field2, field3 FROM countries WHERE id="45"'));

However, I would always select specific fields (not *) if you do this, to prevent your code from breaking if the fields should change. And what if the query should fail?

But why assign it to another variable anyway, why not just keep it in the array?

[httpwebwitch]

penders' solution is good.

you could also write a custom function

function get_value_from_SQL($query, $fieldname){
$result = mysql_query($query);
$row = mysql_fetch_array($result);
return $row[$fieldname];
}

then use it like this:

$value = get_value_from_sql("SELECT * FROM countries WHERE id = 45", 'name');

If it's something you do often with a simple primary key lookup, you could make the function more generic by passing in a $tablename and a $primarykey.

function get_value_from_SQL($tablename, $primarykey, $fieldname){
$query = "SELECT * FROM $tablename WHERE id = $primarykey";
$result = mysql_query($query);
if (!$result){
error_log(mysql_error());
return null;
}
$row = mysql_fetch_array($result);
return $row[$fieldname];
}

the function assumes that you name all your primary keys "id", which though it's good for consistency isn't necessarily the case.

usage:

$value = get_value_from_SQL('countries', 45, 'name');

also it's a subtle point but if your primary key is an integer, you shouldn't put it in quotes. it'll be cast as a string, then recast into an integer before it's used to traverse the table's index.

'SELECT * FROM countries WHERE id = 45'

[rocknbil]

I don't have the code handy, but imagine what these do, and avoiding globals. :-)

function getFieldNames($tablename) {
$fields = array();
//
return $fields;
}

function get_record($table,$field,$target) {
$values = array();
return $values;
}

define('CUSTOMERS','mydb_customer_name');

$headers = getFieldNames(CUSTOMERS);
$customer_data = get_record(CUSTOMERS,'autoinc_id',$cleansed_id);

$flds = count($customer_data-1);

echo '<form>';
for ($i=0;$i<=$flds;$i++) {
echo '<p><label for="' . $headers[$i] .
'">' . $headers[$i] . '<input type="text" name="' .
$headers[$i] . '" id="' . $headers[$i] .
'" value="' . $customer_data[$i] . '"></p>';
}
echo '</form>';

There's a lot more to it, like the auto increment should be id, you need to manage different data types with different fields, you should use filed name aliases and NOT the field names in public forms, but it gets you to thinking . . .