From: yaroukh at email dot cz
Operating system:
PHP version: 4.3.0
PHP Bug Type: Feature/Change Request
Bug description: MySQL_getColumnOptions(tableName, columnName)
I believe people would appreciate function getColumnOptions(tableName,
columnName) which would return all the enum/set options of the desired
column in an array.
Of course, one can parse the options out of DESC-query, but if you want to
make your code "bulletproof", you have to take care of commas and
apostrophes (which are used in the DESC-result for separating and
enclosing the options); plus when there are some 200 options in the
enum-column (e.g. column `country` in table `users`) it's getting pretty
slow.
I believe there is a way of getting the options out of MySQL somehow
"directly", isn't it? (I am a poor Java !_BEGINNER_!. :o) We have some 3-4
ways of getting data ouf of the query-result, but this is something I
really do miss in PHP's MySQL functions.
The result array should be indexed in dependancy to the column type which
it is describing; because of way MySQL translates the enum- and set-fields
into a numeric representation ...
With an enum-field the numeric representation is equal to an index of the
option it contains, and the first option has index 1 => the array index
should be 1-based.
Example {
mysql> DESC orders paymentType;
+-------------+--------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key |
Default | Extra |
+-------------+--------------------------------------+------+-----+---------+-------+
| paymentType | enum('online','invoice','fax','cod') | YES | |
NULL | |
+-------------+--------------------------------------+------+-----+---------+-------+
mysql> SELECT paymentType, paymentType * 1 `pt` FROM orders WHERE
userID = 11444;
+-------------+------+
| paymentType | pt |
+-------------+------+
| online | 1 |
| cod | 4 |
| fax | 3 |
| invoice | 2 |
+-------------+------+
$paymentTypes = MySQL_getColumnOptions("myDatabase.orders",
"paymentType");
Print_R($paymentTypes);
Array {
[1] => online
[2] => invoice
[3] => fax
[4] => cod
}
}
With a set-enum the numeric representation is obtained by involving 2 by
indexes of the options it sontains, and the first option has index 0 =>
the array index should be 0-based.
Example {
mysql> DESC admins `privileges`;
+------------+----------------------------------------------------------------------------+------+-----+---------+-------+
| Field | Type
| Null | Key | Default | Extra |
+------------+----------------------------------------------------------------------------+------+-----+---------+-------+
| privileges |
set('admins','changeLog','licenses','orders','packages','pricing','users')
| YES | | NULL | |
+------------+----------------------------------------------------------------------------+------+-----+---------+-------+
the MySQL's numeric representation of the set-field containing
privileges licenses,orders,users is 76:
2 ^ 2 // licenses
2 ^ 3 // orders
2 ^ 6 // users
-----
76
$privileges = MySQL_getColumnOptions("myDatabase.admins",
"privileges");
Print_R($privileges);
Array {
[0] => admins
[1] => changeLog
[2] => licenses
[3] => orders
[4] => packages
[5] => pricing
[6] => users
}
}
have a nice day :o)
Jaroslav "Yaroukh" Zaruba
Czech rep.
--
Edit bug report at http://bugs.php.net/?id=22783&edit=1
--
Try a CVS snapshot: http://bugs.php.net/fix.php?id=22783&r=trysnapshot
Fixed in CVS: http://bugs.php.net/fix.php?id=22783&r=fixedcvs
Fixed in release: http://bugs.php.net/fix.php?id=22783&r=alreadyfixed
Need backtrace: http://bugs.php.net/fix.php?id=22783&r=needtrace
Try newer version: http://bugs.php.net/fix.php?id=22783&r=oldversion
Not developer issue: http://bugs.php.net/fix.php?id=22783&r=support
Expected behavior: http://bugs.php.net/fix.php?id=22783&r=notwrong
Not enough info: http://bugs.php.net/fix.php?id=22783&r=notenoughinfo
Submitted twice: http://bugs.php.net/fix.php?id=22783&r=submittedtwice
register_globals: http://bugs.php.net/fix.php?id=22783&r=globals
PHP 3 support discontinued: http://bugs.php.net/fix.php?id=22783&r=php3
Daylight Savings: http://bugs.php.net/fix.php?id=22783&r=dst
IIS Stability: http://bugs.php.net/fix.php?id=22783&r=isapi
Install GNU Sed: http://bugs.php.net/fix.php?id=22783&r=gnused
