Prevent data duplication in mysql

1

How can I avoid duplicating records in the database? What I try to do is first a registry query. Next I show the code.              

/**
 * Representa el data de los gastos
 * almacenados en la base de datos
 */
require 'DatabaseConnection.php';

class Notas
{


// Nombre de la tabla asociada a esta clase
const TABLE_NAME = "remisiones";

const RUTA = "ruta";

const VENDEDOR = "vendedor";

const FECHAOPERACION = "fechaOperacion";

const FECHAREGISTRO = "fechaRegistro";

const CODIGOCLT = "codigoClt";

const CODIGOPRD = "codigoPrd";

const PRECIO = "precio";

const IVA = "IVA";

const CANTIDAD = "cantidad";

const TOTALNOTA = "totalNota";

const DETALLE = "detalle";

function __construct()
{
}

/**
 * Obtiene todos los gastos de la base de datos
 * @return array|bool Arreglo con todos los gastos o false en caso de error
 */
public static function getAll()
{
    $consulta = "SELECT * FROM " . self::TABLE_NAME;
    try {
        // Preparar sentencia
        $comando = DatabaseConnection::getInstance()->getDb()->prepare($consulta);
        // Ejecutar sentencia preparada
        $comando->execute();

        return $comando->fetchAll(PDO::FETCH_ASSOC);

    } catch (PDOException $e) {
        return false;
    }
}

public static function insertRow($object)
{
    try {

        $pdo = DatabaseConnection::getInstance()->getDb();

       $bsuqueda = "SELECT count (*) as fechaRegistro FROM " . self::TABLE_NAME . " WHERE " . self::FECHAREGISTRO . "=?" ;
       $query = $pdo ->prepare($bsuqueda);
       $query ->bindParam(1, $fechaRegistro);
       $query -> execute();
       echo "num_rows = ".$query->fetchColumn."\n";

        if ($query->fetchColumn() == 0)
       {

         // Sentencia INSERT
        $comando = "INSERT INTO " . self::TABLE_NAME . " ( " .
            self::RUTA . "," .
            self::VENDEDOR . "," .
            self::FECHAOPERACION . "," .
            self::FECHAREGISTRO . "," .
    self::CODIGOCLT . "," .
    self::CODIGOPRD . "," .
    self::PRECIO . "," .
    self::IVA . "," .
    self::CANTIDAD . "," .
    self::TOTALNOTA . "," .
            self::DETALLE . ")".

            " VALUES(?,?,?,?,?,?,?,?,?,?,?)";

        // Preparar la sentencia
        $sentencia = $pdo->prepare($comando);

        $sentencia->bindParam(1, $ruta);
        $sentencia->bindParam(2, $vendedor);
        $sentencia->bindParam(3, $fechaOperacion);
        $sentencia->bindParam(4, $fechaRegistro);
    $sentencia->bindParam(5, $codigoClt);
    $sentencia->bindParam(6, $codigoPrd);
    $sentencia->bindParam(7, $precio);
    $sentencia->bindParam(8, $IVA);
    $sentencia->bindParam(9, $cantidad);
    $sentencia->bindParam(10,$totalNota);
        $sentencia->bindParam(11,$detalle);

       /*$ruta = $object[self::RUTA];
        $vendedor = $object[self::VENDEDOR];
        $fechaOperacion = $object[self::FECHAOPERACION];
        $fechaRegistro = $object[self::FECHAREGISTRO];
    $codigoClt = $object[self::CODIGOCLT];
    $codigoPrd = $object[self::CODIGOPRD];
    $precio = $object[self::PRECIO];            
    $IVA = $object[self::IVA];
    $cantidad = $object[self::CANTIDAD];
    $totalNota = $object[self::TOTALNOTA];
        $detalle = $object[self::DETALLE];*/

      $ruta = "1";
        $vendedor = "Prueba";
        $fechaOperacion = "2016-03-08";
        $fechaRegistro = "2035-12-10 13:46:05";
    $codigoClt = "1";
    $codigoPrd = "1";
    $precio = "9.41";            
    $IVA = "16";
    $cantidad = "35";
    $totalNota = "329.35";
        $detalle ="n";


        $sentencia->execute();

        // Retornar en el ultimo id insertado
        return $pdo->lastInsertId();
      }else
        {

          echo ("No se puede crear un registro ya exitente");

         }

    } catch (PDOException $e) {
        return false;
    }

}

At the moment of executing, the field is still inserted even though it already exists.

    
asked by Ismael Rodriguez 16.05.2016 в 20:16
source

2 answers

2

The best thing is that the column or combination that can not be repeated can be defined in a single index

create table ........

unique ( columna )
    
answered by 21.05.2016 в 12:48
1

There is a very simple way to achieve that.

You have to declare certain restrictions in your database in the fields that you require.

In your case it would be enough to define the field "UNIQUE".

When wanting to insert a dao, it will throw an exception, you just have to control it.

    
answered by 12.08.2016 в 19:13