Can insert "option type" in ENUM in php?

[sigmahokies]

Hi everyone,

 

I hope you have a great thanksgiving weekend.

 

It is kind of hard for me to explain about ENUM in MySQL, Please forgive me for not good at grammar at English. Please let me know if you don't understand my request. I'm ASL user, English is my second language.

 

I am trying to create the type of list in ENUM in website, I set up the MySQL (phpmyadmin) ENUM with three options, But i'm not sure how to do insert the option in MySQL, for example, IN PHP - $sql = "INSERT INTO Table_name (ID, FirstName, LastName, Type) VALUES (NULL, '$firstname','$lastname','option');. I know NULL is not require, but it is host-sharing is requiring it.

 

However, That "option" is type in ENUM that I set, but in website, what is right script to insert the option in ENUM in table? I set ENUM by 'option1','option2','option3', how can i insert into table in that has option from website?

 

Please forgive me if I don't make a clear to explain, I will do my best in next post in this thread.

[benanamen]

Dont use ENUM. Others replying will tell you why not. In the mean time you can research on your own as to why not.

Edited November 30, 2015 by benanamen

[gizmola]

Yeah, I dislike the enum type myself.  First off, it's not standard SQL, so it's only supported in a handful of DB's.  I think you should have a really good reason to use an rdbms specific implementation.

 

Secondly and much more importantly, you are mixing DDL/schema definition with "data".  The decode values for the internal enum representation can just as easily be done as a separate table with a foreign key relationship, and you at that point don't need to allow updating the actual table definition in order to add or delete from the enum list.

 

I find that people are of one of 2 minds:

 

1.  I want everything relational, and configurable through the db.

 

If that's the case then use a separate "lookup" table and relate it using the foreign key.  You can minimize the storage cost in mysql by going as granular as a char(1) or a tinyint.  You also have the benefit of using foreign key constraints for data integrity, while still allowing an admin system to add new values.  

 

2. I want my code to drive this, so that I can reduce load on the db. 

 

Often people will have a list of constants, perhaps in an array:

$shirtsize = array('s' => 'small',
                            'm' => 'medium'
                            'l' => 'large'
                             'x' => 'extra large'
);

This is all an enum is doing for you, and if your argument is that you don't want to pay the price of having an actual legitimate relation by foreign key, then this is often better represented in the server side code, where you can then do things like string localization, and where the enum doesn't benefit you.

 

It seems like your question is in regards to how you update the list of allowable values for the enum, and the answer is -- you have to alter the table.  As I said originally, you don't want to do that, because you really don't want your application to even have the rights to do DDL calls that can add tables, drop tables or alter their structure.  A glitch or an exploit and your whole database can easily be zapped by an attacker.  

 

Also altering a table requires it to be locked which limits concurrency and uptime for your app.

 

If I'm not understanding your question correctly please elaborate, but my advise would be to avoid using enums, since you have several simple and standard options that allow you to do what you need to without them.

[sigmahokies]

Thank you for advise, I am going to remove ENUM from phpmyadmin, trying to set up the one columns will split into three columns.

 

Thank you.

Edited November 30, 2015 by sigmahokies

[gizmola]

I'm not sure why you would use 3 columns.

 

If you want to explain what you are trying to do, I can help with the proper database design.

[sigmahokies]

All right, gizmola

 

I am trying to set up the three class, ASL1, ASL2, ASL3 in a one column that will split into three column on display to allow instructor see type of class.. I need to have three type ASL on one column, Like list of local and nonlocal in membership. I could put this tag -

<td><input type="hidden" value="ASL1">ASL 1</td>.  

 

Seem PHP didn't take the post from value in html that will send value into table column. I don't want to have three tables, I prefer to have one table.

 

For example, following register:

Table:

 

ID (AC) | FirstName | LastName | Email                       | Type

               Gary            Taylor         Gary@gmail.com      ASL1

               Wendy         Taylor         Wendy@gmail.com  ASL2

[\php]

 

So, In other display, then i can select column name from table name that where equal ASL 1 or ASL 2 or ASL 3. Instructor can tell which students are registered in those three classes. Get it? Please advise me if you don't understand what i am saying...

 

Thanks in advance time

 

Gary

Edited December 1, 2015 by sigmahokies

[Strider64]

First of all I 99 percent of the time just use regular varchar, but I fool around with enum every once in a while and came up with this method of getting the categories or what have you from an enum type:

    /* Grab the categories and put it into a category array */
    public function getCategories() {
        /* Set up the query to fetch only the category column which is an enumerated type */
        $this->sql = "SHOW COLUMNS FROM pages LIKE 'category' ";
        $this->category = $this->pdo->query($this->sql);
        /* set it up as an object */
        $this->category->setFetchMode(\PDO::FETCH_OBJ);
        /* Fetch all the rows in that particular column as objects */
        $this->enum = $this->category->fetchAll();
        
        $this->type = $this->enum[0]->Type; // Grab only the Type column:
        
        preg_match('/enum\((.*)\)$/', $this->type, $this->matches); // Strip enum() away from the string:
        $this->vals = explode(',', $this->matches[1]); // Convert it to an array:
        
        /* Trim the ' away from the individual values and put it in categories array */
        foreach ( $this->vals as  $value) {
            $this->categories[] = trim($value, "'");            
        }
        return $this->categories; // Return the array with the proper categories:
    }

but like already stated it is easier to use a different type and not all databases support enum. I also think it's best to have your logic (can't find a the right word) in PHP than MySQL.   Sorry for going kind of going off topic....returning to regular broadcasting.....Though I think you could modify you table to have three columns to achieve what you want (I think)

Edited December 1, 2015 by Strider64

[gizmola]

So if I understand you, you have a "classType" list?

 

 

classType

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

1. ASL1

2. ASL2

3. ASL3

etc.

 

But you also describe this as being the "class" and not a type at all.

 

What it sounds like is that this is a classic students/classes/grades system?

 

The tables you want in that case are typically something like this:

+--------------------+
|    Student         |
+--------------------+
|    student_id (pk) +------+
|                    |      |      +-------------------------+
+--------------------+      |      |   ClassStudent          |
                            |      +-------------------------+
                            |      |   classStudent_id (pk)  |
                            |                                |
+--------------------+      +----> +   student_id (fk)       |
|  Class             |             |                         |
+--------------------+             |                         |
|  class_id (pk)     +-----------> |   class_id (fk)         |
+--------------------+             |                         |
                                   |   year                  |
                                   |   semester              |
                                   |   grade                 |
                                   +-------------------------+

[sigmahokies]

Hi gizmola,

 

I'm sorry for waste your time but explain about split, I found why it didn't work why PHP didn't take value from html's value...I forget to add the vertify the connect to MySQL.

 

Like always start with connect to MySQL like this:

 

<?php
 
include('connection.php')
 
?>

 

I did write this, but I forget to verify script connect INSIDE connection.php...that is why insert is not work. 

 

Now, it is working, even type of class are working by insert, so i can split this column into three columns to view which people are register for class.

 

Thank you for your time...