Insert in MySQL enum column set the wrong data

Question

This is my first time using MySQL enum data type, so I really don't know how to insert this type of data correctly. I have a table contains a columns has enum type(status column), when I insert a row in this table, the status column will contain a value not that I insert, example:

  define('INVALID_EMAIL_ADDRESS', 0);
  define('EMAIL_EXIST',           1);
  define('NO_MX_RECORDS',         2);
  define('EMAIL_NOT_EXIST',       3);
  define('EMAIL_CHECK_FAILS',     4);

$query_emails = mysqli_query($db_link, "SELECT email_id, email_address FROM emails");
  while ($email_row = mysqli_fetch_array($query_emails)) {
      $check_email = does_email_exist($email_row[1]);
      echo "status: " . $check_email . " " . $email_row[1] . "<br>";
      switch($check_email) {
        case INVALID_EMAIL_ADDRESS;
          //echo $check_email . " " . $email_row[1] . "<br>";
          $query = mysqli_query($db_link, "UPDATE mail_list SET status = " . INVALID_EMAIL_ADDRESS . " WHERE email_id = " . $email_row[0]);
          ++$num_invalid_emails;
        break;
        case EMAIL_EXIST;
          echo $check_email . " -- " . $email_row[1] . "<br>"; // this line get echoed
          $query = mysqli_query($db_link, "UPDATE mail_list SET status = " . EMAIL_EXIST . " WHERE email_id = " . $email_row[0]);
          ++$num_email_exist;
        break;
        case NO_MX_RECORDS;
          echo $check_email . " -- " . $email_row[1] . "<br>"; 
          $query = mysqli_query($db_link, "UPDATE mail_list SET status = " . NO_MX_RECORDS . " WHERE email_id = " . $email_row[0]);
          ++$num_no_mx_records;
        break;
        case EMAIL_NOT_EXIST;
          echo $check_email . " -- " . $email_row[1] . "<br>"; // this line get echoed too, but the other don't.
          $query = mysqli_query($db_link, "UPDATE mail_list SET status = " . EMAIL_NOT_EXIST . " WHERE email_id = " . $email_row[0]);
          ++$num_emails_not_exist;
        break;
        case EMAIL_CHECK_FAILS;
        default;
          $query = mysqli_query($db_link, "UPDATE mail_list SET status = " . EMAIL_CHECK_FAILS . " WHERE email_id = " . $email_row[0]);
          ++$num_email_check_fails;
        break;  
      }  
  }

In this table, some of the email exist, and the other doesn't exist, what I should see in this table row: EMAIL_NOT_EXIST or EMAIL_EXIST, but I saw all records has NO_MX_RECORDS.

What am I doing wrong here?

Dump every query using `echo` and run it manually in MySQL. Check if the same happens. If so, You must check Your enum in MySQL. — Michal Przybylowicz, May 05 '15 at 10:33

score 1 · Accepted Answer · edited May 23 '17 at 10:28

check your database and how is your enum field.

Assuming you are using value from 0 to 4 i think is useless to use enum. use tinyint instead.

Enum became usefull if you set your field status in db like

'NOT VALID MAIL', 'MAIL NOT EXIST' // and so on...

so...options are this:

1) Alter table - in mysql change field type

ALTER TABLE `myTable` MODIFY COLUMNS `status` tinyint(1);

and if you run your actual code will work.

2) Change your code

in the define you use

define('INVALID_EMAIL_ADDRESS', 0);

that is (for me) a perfect solution....anyway...if you have field ENUM in status it should be like in the example of before

'NOT VALID MAIL', 'MAIL NOT EXIST' // and so on...

so your define will be

define('INVALID_EMAIL_ADDRESS', 'HOW I AM IN THE ENUM');

3) – Michał Przybyłowicz have right. to have better help and info you should dump us the enum field, HERE is how.

Insert in MySQL enum column set the wrong data

1 Answers1