CODEWITHSUNDEEP
phpmysqlpdo
Dyan
Dyan

Reputation: 313

Assign the order ID to to its products


I need to separately store the products purchased by customers and I use one primary key called order_id to know which order the products belong to.
In one table I store the order and in another the purchased products.
My orders table struct is:

order_id (PRIMARY KEY),
customer_id,
customer_name,
order_price,
order_date

My purchased_products table struct is:

order_id (FOREIGN KEY with REFERENCES orders(order_id)),
product_name,
product_price,
quantity

How it works
When someone buy something, I run this code to store all the data:

try
{
    $connection = new PDO("mysql:host={$HOST};dbname={$DB_NAME}", $USERNAME, $PASS);
}

$connection->beginTransaction();
$sql = "INSERT INTO orders (customer_id, customer_name, order_price, order_date)
        VALUES (?, ?, ?, ?)";

$query = $connection->prepare($sql);
$query->execute(array
(
    $user_id,
    $user['user_name'],
    $order_price,
    $date
));

$id_of_respective_order = $connection->lastInsertId();

$sql = "INSERT INTO purchased_products (order_id, product_name, product_price, quantity)
        VALUES (?, ?, ?, ?)";

$query = $connection->prepare($sql);

foreach($_SESSION['cart'] as $product)
{
    $query->execute(array
    (
        $id_of_respective_order,
        $product['product_name'],
        $product['product_price'],
        $product['quantity']
    ));
}

$connection->commit();

With this logic I lock the table, then the order is inserted and, to assign the correct order ID to the products that belongs to it I use PDO lastInsertId. After I can do a query using:

SELECT * FROM orders c, purchased_products pc WHERE c.customer_id LIKE {$user_id} AND pc.order_id = c.order_id ORDER BY c.order_id DESC

My questions are:

1. Is this method 100% secure? If not, what to do?

2. If too many customers buy at the same time the table will be locked but all data will be inserted correctly, one-by-one, in sequence, waiting for the previous order or the user that purchased when someone data is being inserted will receive an error and will be required to try finish his order later?

3. If too many customers buy at the same time, is there any chance of the products being assigned with the wrong order ID?

I appreciate the attention, I would like immensely to receive a concrete answer that take away all my doubts and assure me that I can use this method quietly without fear.

Upvotes: 0

Views: 85

Answers (1)

LeleDumbo
LeleDumbo

Reputation: 9340

  1. Is this method 100% secure? If not, what to do?

From SQL injection POV, at least you prevented 1 way by using bound parameters. I still suggest using manual pre-check, though. The golden rule is: never trust any user input, which I don't see in your code but I suppose it is so.

  1. If too many customers buy at the same time the table will be locked but all data will be inserted correctly, one-by-one, in sequence, waiting for the previous order or the user that purchased when someone data is being inserted will receive an error and will be required to try finish his order later?

In MySQL, this depends on both storage engine and transaction isolation level, but generally transactions may run in parallel, so the "one-by-one, in sequence" doesn't apply. There will be no error from DBMS level, however there could be lost update if you have stock field in the products table. Two (or even more!) users buying the same product may update the stock independently, without the one aware of the other. Result ranges from negative stock to inconsistent stock vs quantity. We experienced this before and need to use application level transaction locking to solve it. If you don't have it, though, you should be safe.

  1. If too many customers buy at the same time, is there any chance of the products being assigned with the wrong order ID?

You use transactions to secure it, so no.

Upvotes: 1

Related Questions