when I try to insert data to my database using PHP, echoing out the query, it echoes a query that can be inserted(I inserted the data directly using PHPMyadmin no problems). But still it gave me some errors using php, so i deleted the relation on the DB and tried again, and then i saw what was the issue, but i don't understand what is causing it.
The Problem
My code:
echo "<b>mysql_query(\"INSERT INTO EncomendaCab(utilizador_id,data_encomenda,data_vencimento, envio_id, pagamento_id, total,obs) VALUES('$cliente_id','$data_e','$data_v', '$envio_id', '$pagamento_id','$total','$obs')\");</b>";
mysql_query("INSERT INTO EncomendaCab(utilizador_id,data_encomenda,data_vencimento, envio_id, pagamento_id, total,obs) VALUES('$cliente_id','$data_e','$data_v', '$envio_id', 'pagamento_id','$total','$obs')") or die(mysql_error());
My echo:
"mysql_query("INSERT INTO EncomendaCab(utilizador_id,data_encomenda,data_vencimento, envio_id, pagamento_id, total,obs) VALUES('1','2012-12-03 11:13:08','2013-01-03 11:13:08', '1', '1','2400','Observações')");"
what i get inserted:
+++++++++++++++
+EncomendaCab +
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
|utilizador_id | data_encomenda | data_vencimento | envio_id | pagamento_id | total | obs |
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
| 1 |2012-12-03 11:13:08|2013-01-03 11:13:08| 1 | 1 | 2400 |Observações|
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
So this is for an e commerce site, that is going to receive the orders and get the payment or via Paypal, or postal service or directly in the physical store, the fields you are in Portuguese, so I am translating and explaining them below(some may be straight forward others may not be that easy):
utilizador_id:(int)this is the user id it is the key of the users table.
data_encomenda (order_date)(datetime): this is the date time of the moment when the client(user) submits the form confirming an order.
data_vencimento(datetime): this is equivalent to due date it is a request from "my client"
envio_id(int): this is the kind of shipment that the client of the store can choose(he can ask to send to his house or pick the things in the physical store);
pagamento_id(int): this is the table that stores the king of payments the client can choose(paypal, check, postal service, etc), and is where the problem is, on the echoed query it echoes a 1 or other number of the select element in the form(witch is getting the values from the database), but when is submitting to the database it is replaced with a 0, and caused a "foreign key constraint fails" error. and i don't understand why...
total(float): this stores the total of the order to display in the back-end,
obs(varchar(500)): this is the observations field where people can ask for things(like: "draw a unicorn on the box").
thank you for your time...
EDIT:
i forgot the data types, but already added them...
the error message was a foreign key constraint fails but i deleted the relation on the table pagamento now it inserts a zero this means(AFAIK) that the data inserted is invalid, but if it is echoing an integer why isn't it accepting it, somewhere when the mysql_query() is executed that variable is "corrupted" and i don't understand where or why...
it is given by a $_POST from the form it maybe important...
mysql_xxx()functions are considered obsolete and insecure. It is recommended to switch to using the newermysqli_xxx()functions, or the PDO library instead. See stackoverflow.com/questions/12859942/…mysqli_xxx(), since you wouldn't need to make many changes to your code. PDO is completely different, and would need a lot of changes, butmysqliis quite similar. The quick way to do it is add anito all the function calls. The only major change is that all the functions need you to supply the connection object ass a parameter (ie the object generated bymysqli_connect()). It can get more involved than that, but that should sort you out at a basic level. Hope that helps.