Getting column names from a mysql query

[htiek]

Hello

This is what I need to do

Loop over a query row and assign the name of column to the value of the column.

For example say the database had the following field names:
memID, firstName, lastName

And the values:
45, ‘Joe’, ‘Smith’

I can figure out how to loop over the row and output the values but I need to assign these values to their corresponding field name.

I’m not at home now so I haven’t got the exact code but its something like this (from memory).

for($i= 0; $i < count($row);$i++){
eval(“s_”.”COLUMNNAME”) = stripslashes(“row[$i]”)
}

The bit I’m not sure about is the

eval(“s_”.”COLUMNNAME”)

part where COLUMNNAME should be the name of the column name, how do I get this. What it should do is assign the given value of $row[$i] to the column name (and prefix the column name with s_)

I’ve looked around but can’t find the answer.

I think with SQL server you can do something link “select top 1 * from tableName” to get column names. But not sure about MySql.

Thanks for any help

[Moonglobe]

if you use mysql_fetch_assoc() you get the column names as the keys.

[mrhappiness]

http://www.php.net/mysql-field-name might be of help

but why don't you know the columns' names?
you don't use SELECT * do you?

[vincery]

Hi,

Are you just trying to determine the column or field names? Or, trying to change or set those column or field names into their actual values?

Anyway, take a look about the following mysql functions if they can help you:

---

mysql_field_name

(PHP 3, PHP 4 )

mysql_field_name-- Get the name of the specified field in a result

Description

string mysql_field_name ( resource result, int field_index)

mysql_field_name() returns the name of the specified field index. result must be a valid result identifier and field_index is the numerical offset of the field.

Note: field_index starts at 0.

e.g. The index of the third field would actually be 2, the index of the fourth field would be 3 and so on.

Note: Field names returned by this function are case-sensitive.

Example 1. mysql_field_name() example

<?php
/ The users table consists of three fields:
user_id
username
password.
/
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$db_selected) {
die('Could not set $dbname: ' . mysql_error());
}
$dbname = 'mydb';
$db_selected = mysql_select_db($dbname, $link);
if (!$db_selected) {
___die('Could not set $dbname: ' . mysql_error());
}
$res = mysql_query('select from users', $link);

echo mysql_field_name($res, 0) . "\n";
echo mysql_field_name($res, 2);
?>

The above example would produce the following output:

user_id password

For downwards compatibility mysql_fieldname() can also be used. This is deprecated, however.

mysql_fetch_field

(PHP 3, PHP 4 )

mysql_fetch_field-- Get column information from a result and return as an object

Description

object mysql_fetch_field ( resource result [, int field_offset])

Returns an object containing field information.

mysql_fetch_field() can be used in order to obtain information about fields in a certain query result. If the field offset isn't specified, the next field that wasn't yet retrieved by mysql_fetch_field() is retrieved.

The properties of the object are:

name - column name

table - name of the table the column belongs to

max_length - maximum length of the column

not_null - 1 if the column cannot be NULL

primary_key - 1 if the column is a primary key

unique_key - 1 if the column is a unique key

multiple_key - 1 if the column is a non-unique key

numeric - 1 if the column is numeric

blob - 1 if the column is a BLOB

type - the type of the column

unsigned - 1 if the column is unsigned

zerofill - 1 if the column is zero-filled

Note: Field names returned by this function are case-sensitive.

Example 1. mysql_fetch_field() example

<?php
$conn = mysql_connect('localhost:3306', 'user', 'password');
if (!$conn) {
_die('Could not connect: ' . mysql_error());
}
mysql_select_db('database');
$result = mysql_query('select from table');
if (!$result) {
die('Query failed: ' . mysql_error());
}
/ get column metadata */
$i = 0;
while ($i < mysql_num_fields($result)) {
echo "Information for column $i:<br />\n";
$meta = mysql_fetch_field($result, $i);
if (!$meta) {
__echo "No information available<br />\n";
}
echo "<pre>
blob:___$meta->blob
max_length:$meta->max_length
multiple_key: $meta->multiple_key
name:______$meta->name
not_null:$meta->not_null
numeric:_$meta->numeric
primary_key:$meta->primary_key
table:____$meta->table
type:___$meta->type
unique_key:$meta->unique_key
unsigned:__$meta->unsigned
zerofill:$meta->zerofill
</pre>";
__$i++;
}
mysql_free_result($result);
?>

[htiek]

Thanks for all your replies, to answer a question above I am using “select * from tableName” and I do know the name of the table, however this bit of code will come in very handy down the line.

By fixing what I had before with your advice this is what I got so far

[code=php]
	$query = "select * from members";
	$results = mysql_query($query);
	$num_results = mysql_num_rows($results);
	for ($i=0; $i < $num_results; $i++){
		$row = mysql_fetch_row($results);
		for ($i=0; $i < count($row); $i++){
			echo mysql_field_name($results, $i).": ".stripslashes($row[$i])."<br />";
		}
	}
[/code]

This is the output

memID: 1
memFName: Joe
memLName: Smith
memEMail: [e-mail]
memCategory: members category name
memActive: 1

What I want at the end of this is a set of variables with corresponding values

s_memID = 1
s_memFName = “Joe”
s_memLName = “Smith”
s_memEMail = “[e-mail]”
s_memCategory = “members category name”
s_memActive = 1

I would like to set these inside the loop in the code above where at the moment it says

echo mysql_field_name($results, $i).": ".stripslashes($row[$i])."<br />"; 

I tried

eval("\$s_".mysql_field_name($results, $i)) = stripslashes($row[$i]); 

but it’s not doing what I want it to.

basically I need to prefix the column names with s_ and get PHP to make a variable out of it with the colum value stripslashes($row[$i]); assigned to the particular variable.

Any further suggestions? 😕 😕

[Moonglobe]

you need to put more into a string for the eval:

eval('$s_'.mysql_field_name($rs, $i).' = "'.stripslashes($row[$i]).'";');