Retrieving fields name from mysql

[Duderino82]

I was wondering if there is a way to collect the names of the fields
from a specific table. I think the soluction is to be researched in the
sql code but maybe someone knows of a way to o so directly from php.

Example.
Table: Categories
Field1: type1
Field2: type2
Field3: type3
Field4: type4
Field5: type5
What I want is to be able to have an array (or an object) that cointans
the name of the fields:

array[0]=type1
array[1]=type2
array[2]=type3
array[3]=type4
array[4]=type5

Any suggestions?

[Mike Youell]

As far as I'm aware there is no way to do it accept use the "describe"
function of mysql (or sql or whatever).

You can call this using the php mysql query command. I use mysqli in
PHP5, it becomes even easier then.

I haven't tested this but try the following:

// Initialise the type array to empty.
$TypeArray = array();

// Perform the mysql "describe" of the table Categories.
$QueryResult = $this->m_mysqli->query("describe Categories");

// Error checking for above function here, yada yada yada ;o)

// Convert the first result into an object.
$ResultObject = $QueryResult->fetch_object();

// If there was a result then...
while ($ResultObject !== NULL)
{
// Get the type...
$Type = $ResultObject->Type;

// And push it onto the type array.
array_push($TypeArray, $Type);

// Get the next result.
$ResultObject = $QueryResult->fetch_object();
}

[Ken Robinson]

Mike Youell wrote:

As far as I'm aware there is no way to do it accept use the "describe"
function of mysql (or sql or whatever).

You may want to look at the function mysql_fetch_field(), which
according to the manaual does "Returns an object containing field
information. This function can be used to obtain information about
fields in the provided query result."

Ken

[NC]

Duderino82 wrote:

I was wondering if there is a way to collect the names of the fields
from a specific table. I think the soluction is to be researched in the
sql code but maybe someone knows of a way to o so directly from php.

Example.
Table: Categories
Field1: type1
Field2: type2
Field3: type3
Field4: type4
Field5: type5
What I want is to be able to have an array (or an object) that cointans
the name of the fields:

array[0]=type1
array[1]=type2
array[2]=type3
array[3]=type4
array[4]=type5

Any suggestions?

There are several possible ways of doing it. One is to execute a SHOW
CREATE TABLE query and parse the result. Another is to run a SHOW
FIELDS FROM Categories query:

$fields = array();
$result = mysql_query('SHOW FIELDS FROM Categories');
while ($record = mysql_fetch_array($result, MYSQL_NUM)) {
$fields[] = $record[0];
}

Now the $fields array should contain names of all fields in the
table...

Yet another way is to retrieve a random record and look at its
structure. Something like this:

$fields = array();
$result = mysql_query('SELECT * FROM Categoties LIMIT 1');
$record = mysql_fetch_array($result, MYSQL_ASSOC);
foreach ($record as $field=>$value) {
$fields[] = $field;
}

Now, again, the $fields array should contain names of all fields in the
table...

Cheers,
NC

[Mary Pegg]

Mike Youell wrote:

As far as I'm aware there is no way to do it accept use the "describe"
function of mysql (or sql or whatever).

http://uk2.php.net/function.mysql-list-fields explains all.

You could also, I expect, use "select * from table limit 0,1"
and then array_keys() on the resulting row.

[Peter Fox]

Following on from Mike Youell's message. . .

MySql:-
Look up SHOW COLUMNS for what the database looks like.

If you want to see the field names for a live results set you can use
the flag that sets the associated name in the mysql_[err] - I'm not at
mt PHP system] fetch function and extract the keys from the array result
set.

As far as I'm aware there is no way to do it accept use the "describe"
function of mysql (or sql or whatever).

You can call this using the php mysql query command. I use mysqli in
PHP5, it becomes even easier then.

I haven't tested this but try the following:

// Initialise the type array to empty.
$TypeArray = array();

// Perform the mysql "describe" of the table Categories.
$QueryResult = $this->m_mysqli->query("describe Categories");

// Error checking for above function here, yada yada yada ;o)

// Convert the first result into an object.
$ResultObject = $QueryResult->fetch_object();

// If there was a result then...
while ($ResultObject !== NULL)
{
// Get the type...
$Type = $ResultObject->Type;

// And push it onto the type array.
array_push($TypeArray, $Type);

// Get the next result.
$ResultObject = $QueryResult->fetch_object();
}

--
PETER FOX Not the same since the cardboard box company folded
pe******@eminent.demon.co.uk.not.this.bit.no.html
2 Tees Close, Witham, Essex.
Gravity beer in Essex <http://www.eminent.demon.co.uk>

[Duderino82]

Thankx a lot! You info was very usefull!