CODEWITHSUNDEEP
phpmysqlienums
H Aßdøµ
H Aßdøµ

Reputation: 3055

Insert in MySQL enum column set the wrong data

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?

Upvotes: 0

Views: 759

Answers (1)

Antonio D&#39;Angelo
Antonio D&#39;Angelo

Reputation: 119

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.

Upvotes: 1

Related Questions