php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Request #22783 MySQL_getColumnOptions(tableName, columnName)
Submitted: 2003-03-19 03:54 UTC Modified: 2003-03-19 04:29 UTC
From: yaroukh at email dot cz Assigned:
Status: Not a bug Package: Feature/Change Request
PHP Version: 4.3.0 OS:
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: yaroukh at email dot cz
New email:
PHP Version: OS:

 

 [2003-03-19 03:54 UTC] yaroukh at email dot cz
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.

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2003-03-19 04:29 UTC] georg@php.net
There is no support in the api for.
 
PHP Copyright © 2001-2022 The PHP Group
All rights reserved.
Last updated: Wed Jan 26 07:03:34 2022 UTC