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

Reply via email to