Inserting in the mysql table using a foreign key with PHP


Hi I have two tables,

users_table and orders table.

users_id is in both tables

as a primary key in users_table and as foreign key in orders_table(referencing users_id in users_table)

When I try to place an order if it's the first time the user is able to place an order but if a user already placed an order the data is not saved to the database in the second attempt.. any Idea why? or any solutions?

I apologise for the bad english


$query = "INSERT INTO orders_table(users_id, orders_postDate, orders_category, orders_categoryId, orders_name, orders_description, orders_deliveryDate) VALUES('$users_id', '$orders_postDate', '$orders_category', '$orders_categoryId', '$orders_name', '$orders_description', '$orders_deliveryDate')";

    $result = mysqli_query($connection, $query);

        echo "SUCCESS";
        echo "fail";

so Let's say that I created an account, signed in and placed an order, the data is successfully on the database. if I try to place a new order with the same user I get the fail message.. so for some reason

mysqli_query($connection, $query);

fails the second time, I am assuming that it is because my foreign key is a primary key? how can I fix this?

Without code/error message it is difficult to say what the issue is. However, you mentioned that user_id is the foreign key for the orders_table, but if it is also used for the primary key for your orders_table it would cause duplication when creating any subsequent orders, hence, insertion of a new order would fail.

These might help you to help us to get a better understanding of whats going on.

  • How is your orders_table created?
  • What is the primary key for Orders_table?
  • How do you assign the primary key (if you do) upon insertion of a new record?
  • Since you are able to insert the first record that means you get a successful connection. So instead of echoing "success" and "fail", extract the mysqli error instead.

Also, since you are dealing with MySQL and PHP you might want to take a look into using PDO objects instead, if you haven't already. Being able to use prepared statements is a huge plus.