I do not take the char type in a table

Question

I do not take the char type in a table

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I have a PHP form, I am telling you to create the following table:

$sql=" CREATE  TABLE auxtmp ( 
          n_linea     serial,
          c_concepto_mov char(2) not null,
          c_almacen_origen smallint default 0 not null,
          c_almacen_destino smallint default 0 not null,
          f_movimiento date not null,
          ca_movimiento decimal(19,3) not null,
          c_barra char(18) not null, 
          pr_compra decimal(10,2) default 0 not null, 
          pr_venta decimal(10,2) default 0 not null,
          nit_prov decimal(16,0) default 0 not null , 
          cs_factura char(20) not null ,
          foreign key (c_concepto_mov) references traslados(cod_trasl)
                      constraint noexiste_concepto$auxtmp,
          foreign key (c_almacen_origen) references m_almacenes(c_almacen)
                      constraint noexiste_almacen_origen$auxtmp,
          foreign key (c_almacen_destino) references m_almacenes(c_almacen)
                      constraint noexiste_almacen_destino$auxtmp,
          foreign key (c_barra) references read_plu(cod_barra)
                      constraint noexiste_Producto");

The cs_factura field is type char

This form is to load a plan of an inventory, which has the following structure:

Concept | Alm_Origen | Alm_Destino | Fecha_Mov | Quantity | Cod_Barra | Price_Shopping Price_Sale | Nit_Provider | Num_Factura

When I try to load a plan and the invoice number contains letters, I do not upload the information.

This is one of the INSERT that is made in the code:

$sql="INSERT INTO $auxtmp $campos)VALUES($mireg) ";

Printing the SQL, this is the information I get:

INSERT INTO auxtmp (c_concepto_mov, c_almacen_origen, c_almacen_destino,
                    f_movimiento, ca_movimiento, c_barra ,pr_compra,
                    pr_venta, nit_prov, cs_factura )
       VALUES('EC','3' ,'3' ,'18/05/2015' ,'104' ,'7703596011872',
              '24403' ,'24403' ,'165' ,'86ED3' )

mysql php

asked by Norbey Martinez 19.05.2016 в 22:12

source

2 answers

AccesRandomFile shortcut files Filter language from an XML file in PHP

score 0 · Answer 1

I executed your direct code in mySql without the constraints and it works well There may be a problem in the foreign keys.

Once you run the insert it shows the mySQL response so you know what's wrong

echo mysql_error();

score 0 · Answer 2

The code has several errors:

In the string $sql , I do not see the meaning of using $auxtmp at the end of each constraint

The construction of the string ends badly , so: ...constraint noexiste_Producto"); closes the quote before parentheses.

The definition of CREATE TABLE is not correct for MySQL. Let's put the CONSTRAINT at the beginning and we'll see what happens ... :) and we also define that we want our database in MyIsam ... if it's InnoDB what the keys will vary slightly.

Let's take the opportunity to organize our CREATE TABLE a bit ... an organized code is nice.

The INSERT is also not very happy, data of the date type should be entered respecting the standard format that is: yyyy-mm-dd .

Let's see if it works

Code: `Ver DEMO (rextester)`

CREATE  TABLE auxtmp ( 
    n_linea           SERIAL,
    c_concepto_mov    char(2) not null,
    c_almacen_origen  smallint default 0 not null,
    c_almacen_destino smallint default 0 not null,
    f_movimiento      date not null,
    ca_movimiento     decimal(19,3) not null,
    c_barra           char(18) not null, 
    pr_compra         decimal(10,2) default 0 not null, 
    pr_venta          decimal(10,2) default 0 not null,
    nit_prov          decimal(16,0) default 0 not null , 
    cs_factura        char(20) not null ,


    CONSTRAINT   noexiste_concepto_auxtmp 
    FOREIGN KEY  (c_concepto_mov) 
    REFERENCES   traslados(cod_trasl),

    CONSTRAINT   noexiste_almacen_origen_auxtmp 
    FOREIGN KEY  (c_almacen_origen) 
    REFERENCES   m_almacenes(c_almacen),

    CONSTRAINT   noexiste_almacen_destino_auxtmp 
    FOREIGN KEY  (c_almacen_destino) 
    REFERENCES   m_almacenes(c_almacen),

    CONSTRAINT   noexiste_producto 
    FOREIGN KEY  (c_barra) 
    REFERENCES   read_plu(cod_barra)

                        ) ENGINE=MyIsam;


     INSERT INTO auxtmp 
        (c_concepto_mov, c_almacen_origen, c_almacen_destino,
         f_movimiento, ca_movimiento, c_barra ,pr_compra,
         pr_venta, nit_prov, cs_factura )

         VALUES('EC','3' ,'3' ,
                '2015-05-18' ,'104' ,'7703596011872', '24403' ,
                '24403' ,'165' ,'86ED3' ) ;


    SELECT * FROM auxtmp;

Result

n_linea c_concepto_mov  c_almacen_origen    c_almacen_destino   f_movimiento    ca_movimiento   c_barra         pr_compra   pr_venta    nit_prov    cs_factura
1       EC              3                   3                   18.05.2015      104,000         7703596011872   24403,00    24403,00    165         86ED3

I do not take the char type in a table

2 answers

Code: Ver DEMO (rextester)

Result

Code: `Ver DEMO (rextester)`