0

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...

Cœur
  • 37,241
  • 25
  • 195
  • 267
Fernando Andrade
  • 795
  • 1
  • 7
  • 19
  • what is your error message? –  Dec 03 '12 at 11:48
  • what are the datatype for all the fields.. can you please tell us? – Pankit Kapadia Dec 03 '12 at 11:49
  • Be aware that the `mysql_xxx()` functions are considered obsolete and insecure. It is recommended to switch to using the newer `mysqli_xxx()` functions, or the PDO library instead. See http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-function-in-php – SDC Dec 03 '12 at 11:57
  • @SDC I am aware of that, i am studing it to implement ASAP but first i would like to see this working locally before implementing mysqli or PDO. I'm a little unsure of which one to pick since i'm a noob in this if you can advise me it would be great :) – Fernando Andrade Dec 03 '12 at 12:03
  • 1
    @FernandoAndrade - I'd suggest in the first place, switching to `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, but `mysqli` is quite similar. The quick way to do it is add an `i` to 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 by `mysqli_connect()`). It can get more involved than that, but that should sort you out at a basic level. Hope that helps. – SDC Dec 03 '12 at 12:08
  • @SDC thank you for your help i will go with mysqli later i will read more about PDO... thanks once again have a good day :) – Fernando Andrade Dec 03 '12 at 12:19

1 Answers1

2
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());

pagamento_id is the problem because it is not a php variable.

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());
SmasherHell
  • 894
  • 8
  • 19
  • i really am stupid, this was the issue thank you, i changed the echo and not the actual query... this append because i am exhausted... and am still training my eyes to catch this details... – Fernando Andrade Dec 03 '12 at 12:17