How to insert multiple values but separated into a single MariaDb record?

Question

How to insert multiple values but separated into a single MariaDb record?

Navigation

#1 by (0 votes)

0

Hi, I want to insert multiple products, units, quantities, related dates for the same purchase order and then show them within the same row in a table, but I can not find how to do it. Basically it is a system to capture orders. To be more clear that if they had the same purchase order they will be put together in the same row of the table, and the products, units, quantities, etc, will be shown together with the same purchase order and the others separated but within the same order. table. Over there I read that it is bad to use arrays inside the database since the same database is like an array. I leave my tables:

 CREATE TABLE capturar_pedido (
        cliente varchar(255) NOT NULL,
        orden_de_compra varchar(255),
        producto varchar(255) NOT NULL,
        unidad varchar(255) NOT NULL,
        cantidad varchar(255) NOT NULL,
        fecha_de_embarque Date NOT NULL,
        notas varchar(255),
        etiquetado char(2) NOT NULL,

        id_pedido BIGINT(11) NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (id_pedido)
        )


        CREATE TABLE salida_materiales (


        folio varchar(255) NOT NULL,

        fecha Date NOT NULL,
        numero_remision varchar(255),
        numero_factura varchar(255),
        otro varchar(255),
        referencia_transporte varchar(255),
        placa_tractor varchar(255),
        placa_caja varchar(255),
        comentarios varchar(255),
        retorno char(2),
        fecha_posible Date NOT NULL,
        orden_de_compra varchar(255) not null UNIQUE,
        id_materiales BIGINT(11) NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (id_materiales),
        FOREIGN KEY(orden_de_compra) references capturar_pedido(orden_de_compra))


    CREATE TABLE coa (

    fecha Date NOT NULL,
    presentacion varchar(255) NOT NULL,
    cantidad varchar(255) NOT NULL,
    oc varchar(255),

    lote varchar(255) NOT NULL,
    produccion varchar(255) NOT NULL,
    caducidad varchar(50) NOT NULL,
    sello varchar(255),
    apariencia varchar(255),
    resultado_apariencia varchar(255),
    sabor varchar(255),
    resultado_sabor varchar(255),
    olor varchar(255),
    resultado_olor varchar(255),
    color varchar(255),
    resultado_color varchar(255),
    pureza varchar(255),
    resultado_pureza varchar(255),
    humedad varchar(255),
    resultado_humedad varchar(255),
    id_pedido Bigint(10) not null UNIQUE,

    id_coa BIGINT(11) NOT NULL AUTO_INCREMENT,

    PRIMARY KEY (id_coa),

    FOREIGN KEY(id_pedido) references capturar_pedido(id_pedido)


    )


    CREATE TABLE granulometria (

    parametro varchar(255),
    unidad varchar(255),
    especificacion varchar(255),
    resultado varchar(255),
    id_pedido int(10) not null,//hacerla foreign key pero no unica
    id_granulometria BIGINT(11) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id_granulometria)
    )

php mariadb

asked by Daniel Treviño 25.09.2017 в 20:21

source

1 answer

Connect to Windows server and list the files in a folder. (D: /) using PHP 2 models in a view razor asp.net mvc

score 0 · Answer 1

Your bd should follow a scheme similar to this one:

Both the "clients" and the "products" can exist without having to have orders, therefore they are tables without foreign keys.

An "order" requires the existence of a "client", it has a key that is not related to the ID of the "clients" table. In addition, an order can be composed of 1 or multiple products, hence the need for a table that stores the purchase details for each product, such as the quantity purchased, we have the table "linea_pedido". This table contains two foreign keys, one to know what order each line belongs to and another to know the "product" purchased.

SQL create the tables in the image:

CREATE TABLE productos (
    id          INT(12) UNSIGNED NOT NULL AUTO_INCREMENT,
    nombre      VARCHAR(255)    NOT NULL,
    descripcion VARCHAR(255)    DEFAULT NULL,
    unidades    ENUM( 'ltrs', 'kgs' ) NOT NULL DEFAULT 'kgs',
    cantidad_env FLOAT(6.3)     NOT NULL DEFAULT 1,
    precio_ud   DECIMAL(9.2)    NOT NULL,
    stock       INT(12)         NOT NULL DEFAULT 1,

    created_at  TIMESTAMP   DEFAULT CURRENT_TIMESTAMP,
    deleted_at  TIMESTAMP   DEFAULT 0,
    modified_at TIMESTAMP   DEFAULT 0,

    UNIQUE( nombre, cantidad_env ),
    PRIMARY KEY ( id )

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

CREATE TABLE clientes (
    id          INT(12) UNSIGNED NOT NULL AUTO_INCREMENT,
    nombre      VARCHAR(255)    NOT NULL,
    email       VARCHAR(255)    DEFAULT NULL,
    telefono    VARCHAR(100)    DEFAULT NULL,
    direccion   VARCHAR(255)    NOT NULL,
    password    VARCHAR(255)    NOT NULL,

    created_at  TIMESTAMP   DEFAULT CURRENT_TIMESTAMP,
    deleted_at  TIMESTAMP   DEFAULT 0,
    modified_at TIMESTAMP   DEFAULT 0,

    UNIQUE( nombre, email ),
    PRIMARY KEY ( id )

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

CREATE TABLE pedidos (
    id          INT(12) UNSIGNED NOT NULL AUTO_INCREMENT,
    referencia  VARCHAR(100)    DEFAULT NULL,
    cliente_id  INT(12) UNSIGNED NOT NULL,
    notas       VARCHAR(255)    DEFAULT NULL,

    created_at  TIMESTAMP   DEFAULT CURRENT_TIMESTAMP,
    deleted_at  TIMESTAMP   DEFAULT 0,
    modified_at TIMESTAMP   DEFAULT 0,

    FOREIGN KEY ( cliente_id ) REFERENCES clientes( id )
        ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY ( id )

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

CREATE TABLE linea_pedido (
    id          INT(12) UNSIGNED NOT NULL AUTO_INCREMENT,
    pedido_id   INT(12) UNSIGNED NOT NULL,
    producto_id INT(12) UNSIGNED NOT NULL,
    cantidad    INT(12)         NOT NULL,

    created_at  TIMESTAMP   DEFAULT CURRENT_TIMESTAMP,
    deleted_at  TIMESTAMP   DEFAULT 0,
    modified_at TIMESTAMP   DEFAULT 0,

    FOREIGN KEY ( pedido_id ) REFERENCES pedidos( id )
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY ( producto_id ) REFERENCES productos( id )
        ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY ( id )

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

SQL Code: link