CODEWITHSUNDEEP
mysql
Nick James
Nick James

Reputation: 163

update enum value from 1 to 0 mysql?

i'm trying to get this script to update my enum column 'read_message' in my 'ptb_messages' table but it just doesn't do anything. the rest of the script works fine but it's just ignoring the request to update 'read_message from 1 to 0.

can someone please show me where im going wrong? thanks

<?php 

        session_start();

        include 'includes/_config/connection.php'; 

        $subject = $_POST['subject'];

        $message_id=$_GET['to'];

        $textarea = $_POST['textarea'];

        $query = mysql_query("SELECT content FROM ptb_messages WHERE id='".$message_id."'");

     $results=mysql_fetch_array($query);

     $result=$results['0'];

        if($result && $textarea) {

            $sql = mysql_query("UPDATE ptb_messages SET content ='".addslashes($textarea)."' WHERE id='".$message_id."'"); 

              $sql = mysql_query("UPDATE ptb_messages SET date_sent = LOCALTIME WHERE id='".$message_id."'");


            $query = mysql_query("SELECT suibject FROM ptb_messages WHERE id='".$message_id."'");

            $sql = mysql_query("UPDATE ptb_messages SET subject = IF(subject LIKE '%:reply', subject, CONCAT(subject, ':reply'))  WHERE id='".$message_id."'"); 

            $sql = mysql_query("UPDATE ptb_messages SET read_message = '0' WHERE id=".$message_id.""); 



      $_SESSION['message_sent']="<div class=\"message_sent\"></div>"; 
    header("Location: {$_SERVER['HTTP_REFERER']}#confirm");
        }

        ?>

Upvotes: 0

Views: 466

Answers (1)

Marc B
Marc B

Reputation: 360662

You're hitting an edge case in MySQL. enum fields can have their values to referred to by the actual value, or their INDEX in the list of allowable values. You're trying to use 0, which MySQL is interpreting as index 0 of the list, which internally is the empty string.

e.g.

 myfield ENUM('one', 'two', 'three')


 myfield = 'two' => 'two'
 myfield = 1 => 'one'
 myfield = 0 => '', not in list, ignore...

If you just need a 0/1 value for a field, why not use an actual BIT field, which is already 0/1/null-only? Using enums for purely numeric values just runs into this value-v.s.-index problem.

Upvotes: 1

Related Questions