CODEWITHSUNDEEP
phpmysql
Naroju
Naroju

Reputation: 2667

MYSQL Duplicate rows

I am trying to insert values into a table in MYSQL, the table has a column which should be unique,so that column always will have different values.

I tried putting UNIQUE for the coloumn but it did not work, Also tried putting that column as PRIMARY KEY and insert IGNORE INTO command it did not work (http://www.tutorialspoint.com/mysql/mysql-handling-duplicates.htm)

My intention is to put phonenumber column unique so every value in this column is different. if the newly inserting value is not unique it should skip wihout giving error.

My code to Create table:

  public function create_member_table($table)
 {
$this->sql ="CREATE TABLE IF NOT EXISTS $table ( id BIGINT NOT NULL AUTO_INCREMENT,
                                    username VARCHAR(50) NOT NULL,
                                    phonenumber VARCHAR(20) NOT NULL,
                                    country VARCHAR(2) NOT NULL,
                                    profession VARCHAR(5000) NOT NULL,
                                    profilepic VARCHAR(5000) NOT NULL,
                                    smskey VARCHAR(100) NOT NULL,
                                    status INT NOT NULL,
                                    reg_date_time DATETIME NOT NULL,
                                    UNIQUE (id,phonenumber))
                                    PARTITION BY HASH(id)
                                    PARTITIONS 1023;";
$this->tb = mysqli_query($this->ret,$this->sql);
if(!$this->tb){
  echo "Table not created<br>";

}
else{
  echo "Table created<br>";

}

Insert table:

  public function table_member_insert($table,$phonenumber="",$username="",$country="",$profession="",$profilepic="0",$smskey="",$status="") {

   $this->sql = "INSERT INTO $table
                  (username,phonenumber,country,profession,profilepic,smskey,status,reg_date_time)
                  VALUES
                  ('$username','$phonenumber','$country','$profession','$profilepic','$smskey','$status',now());";

                  $this->tb = mysqli_query($this->ret,$this->sql);
                  if(!$this->tb){
                    echo "values not inserted<br>";

                  }
                  else{
                    echo "values inserted<br>";
                  } }

Upvotes: 0

Views: 67

Answers (1)

Shadow
Shadow

Reputation: 34232

The problem is that you defined the combination of id and phonenumber fields as unique. Since your id field is defined as auto_increment, it will be unique on its own, therefore any combination with phonenumber field will also be unique.

You need to define the phonenumber field alone as unique. After that you can use insert ignore to insert a new record with an existing phone number without raisin an error. However, pls note that in case of a match, the unique index will prevent the entire record from being inserted.

Upvotes: 3

Related Questions