Allowed duplicate data MySQL

Question

I'm using a internet application which is named Dolibarr and I would like to modify a MySQL condition in the llx_cotisation table.

This table doesn't allow the same cotisation date per member. For example, I can't have a member which paid 2 different cotisations the same day. Because the field dateadhcan't be the same for a member. If I try to add an other cotisation with the same date to the first one, I get an MySQL error :

Duplicate entry '3-2016-01-01 00:00:00' for key 'uk_cotisation'

This is the table structure :

mysql> describe llx_cotisation ;
+-------------+-----------+------+-----+-------------------+-----------------------------+
| Field       | Type      | Null | Key | Default           | Extra                       |
+-------------+-----------+------+-----+-------------------+-----------------------------+
| rowid       | int(11)   | NO   | PRI | NULL              | auto_increment              |
| tms         | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| datec       | datetime  | YES  |     | NULL              |                             |
| fk_adherent | int(11)   | YES  | MUL | NULL              |                             |
| dateadh     | datetime  | YES  |     | NULL              |                             |
| datef       | date      | YES  |     | NULL              |                             |
| cotisation  | double    | YES  |     | NULL              |                             |
| fk_bank     | int(11)   | YES  |     | NULL              |                             |
| note        | text      | YES  |     | NULL              |                             |
+-------------+-----------+------+-----+-------------------+-----------------------------+
9 rows in set (0.00 sec)

The field is : dateadh

Is it possible to allow duplicating value there ?

The part of script in php looks like :

   // List of subscriptions
    $sql = "
    SELECT d.rowid
         , d.login
         , d.firstname
         , d.lastname
         , d.societe
         , c.rowid as crowid
         , c.cotisation
         , c.dateadh
         , c.datef
         , c.fk_bank as bank
         , c.note
         , b.fk_account
      FROM ".MAIN_DB_PREFIX."adherent as d
         , ".MAIN_DB_PREFIX."cotisation as c
      LEFT 
      JOIN ".MAIN_DB_PREFIX."bank as b 
        ON c.fk_bank=b.rowid
     WHERE d.rowid = c.fk_adherent
     ";
    if (isset($date_select) && $date_select != '')
    {
        $sql.= " AND c.dateadh LIKE '".$date_select."%'";
    }
    if ($search_ref)
    {
            if (is_numeric($search_ref)) $sql.= " AND (c.rowid = ".$db->escape($search_ref).")";
            else $sql.=" AND 1 = 2";    // Always wrong
    }

Thank you so much if you could help me, and I sorry for my very bad English :/

EDIT : this is the result to the command : show create table llx_cotisation

| llx_cotisation | CREATE TABLE `llx_cotisation` (
  `rowid` int(11) NOT NULL AUTO_INCREMENT,
  `tms` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `datec` datetime DEFAULT NULL,
  `fk_adherent` int(11) DEFAULT NULL,
  `dateadh` datetime DEFAULT NULL,
  `datef` date DEFAULT NULL,
  `cotisation` double DEFAULT NULL,
  `fk_bank` int(11) DEFAULT NULL,
  `note` text,
  PRIMARY KEY (`rowid`),
  UNIQUE KEY `uk_cotisation` (`fk_adherent`,`dateadh`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 |

SOLUTION :

I removed the UNIQUE KEY and it seems to work perfectly !

[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Oct 24 '16 at 12:37
`dateadh` does not have a unique condition so it should accept multiple dates. AAMOF only the `rowid` has constraints — Jay Blanchard, Oct 24 '16 at 12:39
Never mix comma-join and explicit join syntax. In fact, don't use comma joins at all. — Strawberry, Oct 24 '16 at 12:40
I understand, I have to modify UNIQUE KEY with just : `fk_adherent` isn't it ? I edited my post — Essex, Oct 24 '16 at 12:42
@Andromedae93 that probably won't work either since if fk_adherent was unique, the combination constraint wouldn't have failed either. — Devon Bessemer, Oct 24 '16 at 12:44
Remove `UNIQUE KEY uk_cotisation` from your table. However, this may have an impact at the application at other places where the code may assume that such values are indeed unique. Do such changes only if you perfectly understand the implications across the entire application. — Shadow, Oct 24 '16 at 12:44
If I remove the UNIQUE KEY uk_cotisation from my table, I don't know if this operation will impact the program. I will try without the line. It's an open-source software, so I didn't develop the php script — Essex, Oct 24 '16 at 12:47
I removed the UNIQUE KEY and it seems to work very well ! Thank you everybody :) — Essex, Oct 24 '16 at 12:55

Allowed duplicate data MySQL

0 Answers0