What is wrong with this INSERT Statement

Question

I am trying to use this statement to insert new data into my orders table like so:

$tsql2 = 
"INSERT INTO pedidos (numero_local, emailUser,codigopapel,cantidad, color) 
VALUES ('$local', '$emailOn', '$codigopapel', '$cantidad', '$selectcolor')";

$stmt2 = sqlsrv_query($conn, $tsql2);

I have foreign keys on this table which are:

1. numero_local(location number) which comes from a location table
2. emailUser comes from a user table
3. codigopapel(paper code) which comes from a table called paper

These are my 3 foreign keys and I have tried doing the INSERT as shown above and using:

INSERT INTO pedidos VALUES((SELECT numerolocal FROM localidad WHERE numerolocal = $local))

The above is an example but I have written the SELECTS for all foreign key variables and yet the insert does not work.

Finally I have checked that the data types for all foreign keys and the data being uploaded are consistent across tables and coming from the web page.

In short: What am I doing wrong?

Answer 1

This does not make sense:

INSERT INTO pedidos VALUES((SELECT numerolocal FROM localidad WHERE numerolocal = $local))

You haven't specified a column list, so this should fail with a syntax error (since your table clearly has more than one column).

Formats for INSERT are typically:

INSERT dbo.table(col1, col2) SELECT col1, col2 FROM dbo.othertable;

Or:

INSERT dbo.table(col1, col2) VALUES(const1, @var2);

I don't think I've ever see one construct queries inside VALUES like you have. I suspect you should have something like this:

INSERT INTO dbo.pedidos (numero_local, emailUser,codigopapel,cantidad, color) 
  SELECT numerolocal, emailUser, ...
  FROM dbo.localidad WHERE numerolocal = $local;

Answer 2

As in https://stackoverflow.com/questions/17148144/insert-into-sqlsrv-query-statement-not-working .. you might try die('Errors: ' . print_r(sqlsrv_errors(), TRUE); .. or something like that. (I don't know PHP.) Also, use placeholders (?) for values, then pass your parameters. If you just interpolate strings into your SQL literally, you may be vulnerable to SQL injection attacks, as well as other errors that occur if string variables have quotes within them.

Answer 3

If you have access to the database, Echo (instead of run) your variable with SQL command and then put that command into SQL server and see what error you have there.

$tsql2 = 
"INSERT INTO pedidos (numero_local, emailUser,codigopapel,cantidad, color) 
VALUES ('$local', '$emailOn', '$codigopapel', '$cantidad', '$selectcolor')";

/* $stmt2 = sqlsrv_query($conn, $tsql2); */

echo $tsql2; /* put that command into the database server manually */

or print the error directly

$stmt2 = sqlsrv_query($conn, $tsql2);
echo sqlsrv_errors();

I think there are 2 most common errors possible

• You are inserting values with different columns number then you have in table.
• You are inserting other datatypes then you have in your table (integer into string, etc)