Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

How to get enum values from MySQL column using PHP?

12 views
Skip to first unread message

Don

unread,
Mar 12, 2009, 11:22:41 AM3/12/09
to
I'm trying to populate an HTML <select> statement with the enum values
from a MySQL column using PHP. Can anyone tell me how to access these
enum values using PHP?

Thanks,
Don

Dikkie Dik

unread,
Mar 12, 2009, 11:36:43 AM3/12/09
to
More of a MySQL question, but here you go:

SELECT yourEnumColumn+0 FROM yourTable;

This requests the value as an integer, so it will result one (and the
one you want).

SELECT CONCAT('', yourEnumColumn) FROM yourTable;

Will off course do the opposite, but you wouldn't need it as the string
value is returned by default.

Best regards.

Don

unread,
Mar 12, 2009, 12:03:05 PM3/12/09
to
On Thu, 12 Mar 2009 16:36:43 +0100, Dikkie Dik <dik...@nospam.org>
wrote:

Thanks for the reply. I tried your suggestion as follows:
----------------------

$query = "SELECT type_of_activity+0 FROM hours_spent_record LIMIT 1";
$result = @mysql_query ($query); // Run the query.
print ("type_of_activity type = " . $result . "\n");

----------------------
And, got:

"type_of_activity type = Resource id #5"
----------------------

What I'm looking for is the enum values assigned to that column, which
are:

enum('Red','White','Blue')

I appreciate your help!

Don

Michael Fesser

unread,
Mar 12, 2009, 12:06:50 PM3/12/09
to
.oO(Don)

>I'm trying to populate an HTML <select> statement with the enum values
>from a MySQL column using PHP. Can anyone tell me how to access these
>enum values using PHP?

Fetch the column description from the database:

SELECT COLUMN_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'yourTable'
AND COLUMN_NAME = 'yourColumn';

The result will be a string like

enum('A','B','C','...')

Then use PHP's string functions to get the single values. substr(),
strtr() and explode() might come in handy.

Micha

Don

unread,
Mar 12, 2009, 12:24:54 PM3/12/09
to
On Thu, 12 Mar 2009 17:06:50 +0100, Michael Fesser <net...@gmx.de>
wrote:


Hi Michael,

I tried the following:
-----------------------------
$query = "SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE
TABLE_NAME = 'hours_spent_record' AND COLUMN_NAME =
'type_of_activity'";
$result = @mysql_query ($query);


print ("type_of_activity type = " . $result . "\n");

----------------------------

And, got:

"type_of_activity type = Resource id #5"

---------------------------

What am I doing wrong?

Don

Michael Fesser

unread,
Mar 12, 2009, 12:34:24 PM3/12/09
to
.oO(Don)

>I tried the following:
>-----------------------------
>$query = "SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE
>TABLE_NAME = 'hours_spent_record' AND COLUMN_NAME =
>'type_of_activity'";
>$result = @mysql_query ($query);
>print ("type_of_activity type = " . $result . "\n");
>----------------------------
>
>And, got:
>
>"type_of_activity type = Resource id #5"
>---------------------------
>
>What am I doing wrong?

Ever worked with databases in PHP? A look at the manual might help to
learn how to send a query and how to get the results from it:

http://www.php.net/mysql_query
http://www.php.net/mysql_fetch_row

Also avoid the @ operator to suppress error messages - if something goes
wrong with the query, you'll never know why.

Micha

Johan Holst Nielsen

unread,
Mar 12, 2009, 1:32:56 PM3/12/09
to

try....

$query = "SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE
TABLE_NAME = 'hours_spent_record' AND COLUMN_NAME = 'type_of_activity'";

$result = mysql_result(mysql_query($query),0);
echo "type_of_activity type = ".$result;


A nasty way to get the values out in a array could be

eval("$values = array".substr($result,4).";");
var_dump($values);

--
Johan Holst Nielsen
Freelance PHP Developer - http://phpgeek.dk

Michael Fesser

unread,
Mar 12, 2009, 1:47:46 PM3/12/09
to
.oO(Johan Holst Nielsen)

>try....
>
>$query = "SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE
>TABLE_NAME = 'hours_spent_record' AND COLUMN_NAME = 'type_of_activity'";
>$result = mysql_result(mysql_query($query),0);
>echo "type_of_activity type = ".$result;
>
>
>A nasty way to get the values out in a array could be
>
>eval("$values = array".substr($result,4).";");
>var_dump($values);

eval() is evil. The correct way with string functions is also just a
simple one-liner. You could even let the DB do the main work, so that
you can directly feed the result to explode() to get the array.

Micha

Johan Holst Nielsen

unread,
Mar 12, 2009, 2:09:56 PM3/12/09
to

Agree... thats why I called it nasty ;) At least you can get the values
out then....

But you are right....

$values = explode("','",substr($result,6,-2));

Should do the same - in a much prettier way.

Lars Eighner

unread,
Mar 12, 2009, 2:54:17 PM3/12/09
to
In our last episode, <e1air4t05rinecblc...@4ax.com>, the
lovely and talented Don broadcast on comp.lang.php:

> Thanks,
> Don

This is real clunky. I assume there is something more elegant.
What happens here is you use SHOW COLUMNS to get a description
of the column and then do the messy business of parsing the
description.

First use SHOW COLUMNS to describe the column, in this case 'cats'.
Test for the column not existing or being empty if those are
possibilities.

$result = mysql_query("SHOW COLUMNS FROM $blog_name LIKE 'cats'");
$cats_string = mysql_result($result,0,1);

Then:

$cats = table_decode_cats_type($cats_string);

where:

function table_decode_cats_type(){
$numargs = func_num_args();
if($numargs >= 1){$cats_string = func_get_arg(0);}else{$cats_string = '';}
$cats_array = array();
$cats_string = preg_replace('/^(set\()?\'/','',$cats_string);
$cats_string = preg_replace('/\'(\))?$/','',$cats_string);
$cats_array = explode("','",$cats_string);
for($i=0;$i<count($cats_array);$i++){
$cats_array[$i] = str_replace('&#39;','\'',$cats_array[$i]);
$cats_array[$i] = str_replace('&#44;',',',$cats_array[$i]);
$cats_array[$i] = htmlspecialchars_decode($cats_array[$i]);
}
return $cats_array;

}

This returns a numbered array with the types as values.


--
Lars Eighner <http://larseighner.com/> use...@larseighner.com
51 days since Rick Warren prayed over Bush's third term.
Obama: No hope, no change, more of the same. Yes, he can, but no, he won't.

Don

unread,
Mar 13, 2009, 12:07:27 AM3/13/09
to


Thanks a milliion Johan! It worked perfectly. I'm building a
web-empowered database for the Southwest's largest rose garden. I've
bulit databases before, but this is the first time for a web-empowered
one. I've got most of it working just great, but decided I wanted to
populate the many HTML <select> <option> automatically from the DB.
There certainly is always something new to learn with this stuff.

Thanks again Johan, and to you also Dikkie!

Don

Don

unread,
Mar 13, 2009, 12:37:46 AM3/13/09
to

Oh, and you too, Michael

0 new messages