0

I just want to insert all of the products records that are added to cart page. but when I press checkout button only one record is inserted and my query is like that. so if anyone could help me out please.

$sql = "insert into products (Product_id, Name) VALUES('$productid', '$name'  where Customer_id='$customer_session_id');
user5631466
  • 11
  • 5

2 Answers2

0

First of all, MySQL INSERT Syntax doesn't supports "Where".

Then, answering to your question, here is an example: Insert multiple rows with one query MySQL

Community
  • 1
  • 1
dimasdmm
  • 318
  • 4
  • 15
  • the link you have provided is not what i am lokiing for because o that link it shows manually but officially my website will be live so it should insert all of them which are selected from database – user5631466 Jan 30 '17 at 23:26
0

There are many problems with your query.

  1. The WHERE clause should be after the closing paranthesis because it is not one of the values that you want to insert.

  2. If you want the Customer_id of your purchase to be set to $customer_session_id, you don't need a WHERE clause, you just insert it like all the other fields, for example Product_id and Name.

  3. Concatenating values into the sql query like that is not safe, as you are vulnerable to SQL injection. I suggest you use PDO insetead.

  4. You probably need to insert some sort of $customer_id instead of $Customer_session_id.

    <?php

$server_name = "server_name";
$db_name = "db_name";
$username = "username";
$password = "password";

try {
    $db = new PDO("sqlsrv:server=$server_name ; Database = $db_name", $username, $password);
} catch (PDOException $e) {
    echo $e->getMessage;
}

$sql = "insert into products (Product_id, Name, Customer_id) VALUES(:product_id, :name, :customer_session_id";
$sth = $db->prepare($sql);

foreach($products_in_cart as $product) {
    try {
        $result = $sth->execute($product);

        if($result === false) {
            echo $sth->errorInfo;
        }
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}

This assumes:

  1. The data for the db connection is correct and you are using SQL Server.

  2. The table products has the fields Product_id, Name and Customer_id.

  3. $products_in_cart has the following structure (but any values that are valid for the db table), including the keys used:

    $products_in_cart = [
    ["product_id" => "4928",
     "name" => "Fruit blender",
     "customer_session_id" => "1389"],
    ["product_id" => "0840",
     "name" => "Potato peeler",
     "customer_session_id" => "1389"],
    ["product_id" => "7348",
     "name" => "Set of forks",
     "customer_session_id" => "1389"]
];

If any of my asumptions isn't true, you can probably fix it without too much trouble.

MicSokoli
  • 841
  • 6
  • 11