In the statement INSERT INTO
it is mandatory to write all the fields of the table regardless of the fact that all the values (variables) in the field VALUE
do not receive data.
Example
$conexion->query( "INSERT INTO $ejemp (casa, color, estrato, barrio, carro) VALUES ('$casa','$color','$estrato','$calle','$carro')" );
the table has 5 fields, but only the , , ejemp
field receives data from a form . the others don't.casa
color
estrato
The question is because I have a database on a web server and I added 3 new columns which, until I modified it, INSERT INTO
did not insert any data, but I had a table XAMPP
with fields that were not there INSERT
and it did record the data that was sent.
Can someone clarify that point since I was reading but most of the documentation is in English.
---EDIT----
The fields in red (last 3 in the image) are not in the INSERT
but not in the VALUE
, that is, it had 23 items in it INSERT
and 23 Items in the VALUE. my intention was to fill the last 3 fields with another form with a UPDATE
Before those last 3 fields were in the table the data was inserted, after appending those 3 columns no longer. What I did was write the values in it INSERT
and the variables in VALUE
it even though they didn't receive data, and there was INSERT
data sent.
the code was this
$conexion->query( "INSERT INTO $tabla_db1 (submission_id, pedido, fecha, seleccionael6, valorfonendo, lodeseas, seleccionael25, totalart, abonofonendos, abonoaccesorios, envio, valorabono, fechalimite, total, nombre, documento, celular, correoelectronico, direccionde, ciudad, departamento, comentarios, texto_a) VALUES ('$id','$pedido','$fecha','$fonendo','$valor_fdo','$marcar','$articulo','$total_art','$abono_f','$abono_a','$envio','$abono','$limite','$total','$nombre','$doc','$cel','$mail','$dir','$city','$dpto','$info','$texto_a')" );
The MySQL documentation says the following about queries of the type
INSERT INTO ...
:The second option means, in other words, that you can write a statement like this:
As you can see, the list of columns does not appear to the left of
VALUES
... in this case, a value must be provided for each column in the order in which they are defined in the table, passing this value according to the type of the table. column (varchar, int, etc).Although this can be done, the normal thing is to build our
INSERT
as indicated in the 1st option, that is, explicitly indicate to the left ofVALUES
the names of the columns where we want to insert the data and to the right ofVALUES
the values that we want to insert.To answer your question:
Suppose a table like this:
Let us now see several cases.
Case 1: everything normal... as it should be
If you write a query like this to insert data into the table above:
It is mandatory to pass a value for the column
nombre
and another value for the columnapellido
to the query . Here the column is omitteddocumento
in the SQL statement, but the data is inserted, because this column has a default value defined, which it will assume when the record is created.Case 2: Not so normal... but it works
This query would work, because you're entering
nombre
blank data, which is not the same as null.Case 3: Not so normal, but it works
It also works, because in this case the column
apellido
is nullable.Case 4: Omitting the value of a column indicated in the list
If you write a query like this:
You will be entitled to the following error in MySQL:
That is, you told me that I was going to enter data in two columns, but you are giving me data from only one! .
Case 5: Trying to enter a null value in a non-nullable column
Another thing that can happen is that you try to enter a null value
NULL
, in a non-nullable column. For example:Here you will be entitled to the following error:
If you see the one
CREATE TABLE
above you will understand the reason for this system protest :)However, in case 2 there was no error, because a blank value was entered, not null, because
NULL
andis not the same.
Case 6: Ignoring a non-nullable column in the SQL statement that does not have a default value defined at the same time
In the following query:
You will deserve the following error:
Because you omitted from the SQL statement the column
nombre
which is neither nullable nor has a default value. You will see that the same has not happened with the columndocumento
which has been omitted in this and other insert statements. Why does it claim nothing with respect todocumento
and yes with respect tonombre
? Because asdocumento
it has a default value defined, the new rows created assume that value when the column is omitted in any statement.VER DEMO DEL CÓDIGO
Recommendation
The use of prepared queries is strongly recommended in all database handling that includes data from outside, since sending entire queries , that is, in which the entire instruction goes, is an open door to SQL Injection. What prepared queries do is separate the data from the SQL statement itself, making the code more secure and even easier.
See about it:
It could be because when adding the column you allowed null values or if you did not allow them, you put a default value