php - 如果我订购两种产品，它只会在我的数据库中保存一种产品

Question

当我点击结账时，它会向我发送一封包含我的订单的电子邮件，并将我订购的产品发送到 mysql 数据库。但只有一个(最后一个产品)产品被发送并保存在数据库中。我该如何解决这个问题？

//connect to database
$connection = mysql_connect("localhost","root","") or die ("Can't connect");
mysql_select_db("shoppingcart", $connection) or die ("Can't connect");

//get order id ONLY to get order id <<<<<<<
$vol = mysql_query("SELECT orderid FROM ordertracking WHERE email='$email'");
while($volume=mysql_fetch_array($vol))
{
 $orderid = $volume['orderid'];
} // end of getting order id from table ordertracking
    // add new order
    $order = "INSERT INTO `order` (orderid, customerid, productid, brand, model, price, amount, totalcost, image) VALUES ('$orderid', '$customerid', '$productid', '$brand' , '$model', '$price', '$amount', '$totalcost', '$image')";
    if (!mysql_query($order,$connection))
        {
            die('Error: ' . mysql_error());
            echo "Sorry, there was an error";
        }
        echo "New order added" . "<br />";

        mysql_close($connection);

整个脚本:

<?php
error_reporting(-1);ini_set('display_errors', 'stdout');
var_dump($_GET);

//collect all information
$name = $_GET["name"];
$surname = $_GET["surname"];
$city = $_GET["city"];
$postalcode = $_GET["postalcode"];
$phonenumber = $_GET["phonenumber"];
$email = $_GET["email"];

$i = 1;
while (isset($_GET["Product_ID_".$i])) {
    $productid = $_GET["Product_ID_".$i];
    $brand = $_GET["Brand_".$i];
    $model = $_GET["Model_".$i];
    $price = $_GET["Price_".$i];
    $amount = $_GET["Amount_products_".$i];
    $totalcost = $_GET["Total_cost_".$i];
    $i++;
}

$image = "includes/images/mouse_4.jpg";


$date = date("F j, Y, g:i a");



//connect to database
$connection = mysql_connect("localhost","root","") or die ("Can't connect");
mysql_select_db("shoppingcart", $connection) or die ("Can't connect");  

//check if already customer
$result = mysql_query("SELECT * FROM customer WHERE email='$email'");
$rows = mysql_num_rows($result);


    if ($rows) 
    {
      echo '<br>Welcome back ' . $name .' '. $surname. '<br>';
    }
    else
    {
        //if new customer, add to database
        $customer = "INSERT INTO customer (customerid, name, surname, email, city, postalcode, phonenumber) VALUES ('', '$name', '$surname', '$email', '$city', '$postalcode', '$phonenumber')";
        if (!mysql_query($customer,$connection))
        {
            die('Error: ' . mysql_error());
            echo "Sorry, there was an error";
        }
        echo "New customer added" . "<br />";
        echo '<br>Welcome as our new customer ' . $name . ' '. $surname;

        mysql_close($connection);   
    }

//connect to database
$connection = mysql_connect("localhost","root","") or die ("Can't connect");
mysql_select_db("shoppingcart", $connection) or die ("Can't connect");


//get customer id
$res = mysql_query("SELECT customerid FROM customer WHERE email='$email'");
while($row=mysql_fetch_array($res))
{
 $customerid=$row['customerid'];
}
    //add new ordertracking
    $ordertracking = "INSERT INTO `ordertracking` (orderid, customerid, email, progress, date) VALUES ('', '$customerid', '$email', 'Pending', '$date')";
    if (!mysql_query($ordertracking,$connection))
        {
            die('Error: ' . mysql_error());
            echo "Sorry, there was an error";
        }
        echo "New order added" . "<br />";

        mysql_close($connection);

//connect to database
$connection = mysql_connect("localhost","root","") or die ("Can't connect");
mysql_select_db("shoppingcart", $connection) or die ("Can't connect");

//get order id
$vol = mysql_query("SELECT orderid FROM ordertracking WHERE email='$email'");
while($volume=mysql_fetch_array($vol))
{
 $orderid = $volume['orderid'];
}
    // add new order
    $order = "INSERT INTO `order` (orderid, customerid, productid, brand, model, price, amount, totalcost, image) VALUES ('$orderid', '$customerid', '$productid', '$brand' , '$model', '$price', '$amount', '$totalcost', '$image')";
    if (!mysql_query($order,$connection))
        {
            die('Error: ' . mysql_error());
            echo "Sorry, there was an error";
        }
        echo "New order added" . "<br />";

        mysql_close($connection);


$to = $email;
$subject = "Order information of: ";

$headers = "From: " . "postmaster@localhost" . "\r\n";
$headers .= "Reply-To: ". "postmaster@localhost" . "\r\n";
$headers .= "MIME-Version: 1.0\r\n";
$headers .= "Content-Type: text/html; charset=ISO-8859-1\r\n";

$message = '<html><body>';
$message .= '<h1>Dear ' . $name . ' ' . $surname .  ',</h1>' . '<br />';
$message .= 'Order date and time: ' . $date . '<br />';
$message .= 'Thank you for your order at our online shop!' . '<br />';
$message .= 'Your order information: ' . '<br /><br /><br />';
$i = 1;
while (isset($_GET["Product_ID_".$i])) {
    $productid = $_GET["Product_ID_".$i];
    $brand = $_GET["Brand_".$i];
    $model = $_GET["Model_".$i];
    $price = $_GET["Price_".$i];
    $amount = $_GET["Amount_products_".$i];
    $totalcost = $_GET["Total_cost_".$i];

    $message .= ' Product ID: ' . $productid . "<br />" .
                'Brand: '. $brand . "<br />" .
                'Model: ' . $model . "<br />" .
                'Price per item: ' . $price . "<br />" .
                'Amount of item: ' . $amount . "<br />" .
                'Total cost: ' . $totalcost . "<br />" .
                '_________________________________________________| ' . "<br />" .
    $i++;
}
$message .= 'To follow your odertracking please remember your order ID and customer ID' . '<br />';
$message .= 'Order ID: ' . $orderid . '<br />';
$message .= 'Customer ID: ' . $customerid . '<br />';
$message .= 'Link to track your order: ' . '<a href="http://localhost/school/shoppingcart/ordertracking.php">Ordertracking system</a>' . '<br />';

$message .= '</body></html>';

mail($to, $subject, $message, $headers);
?>

Answer 1

您在这里所做的是:

• 从 orderTracking 表中获取所有订单 ID，其中 email 是给定的电子邮件。我假设提取了不止一行。

代码

while($volume=mysql_fetch_array($vol))
{
 $orderid = $volume['orderid'];
}

效果很好，但是当您尝试将 orderid 的值插入到 [order] 表时，只会更新一行，因为变量 $orderid 现在只包含一个字符串(查询的最后一行的 orderid)。

所以解决方案是在 while 循环内运行 insert

类似这样的事情

$vol = mysql_query("SELECT orderid FROM ordertracking WHERE email='$email'");
while($volume=mysql_fetch_array($vol))
{
 $orderid = $volume['orderid'];
// add new order
    $order = "INSERT INTO `order` (orderid, customerid, productid, brand, model, price, amount, totalcost, image) VALUES ('$orderid', '$customerid', '$productid', '$brand' , '$model', '$price', '$amount', '$totalcost', '$image')";
    if (!mysql_query($order,$connection))
        {
            die('Error: ' . mysql_error());
            echo "Sorry, there was an error";
        }
}

希望你能得到一个想法。

第二种解决方案可以是使用数组变量，而不是使用简单的变量 $orderid。然后循环遍历该变量以获取 orderid。

简而言之，根据您的代码，您需要一个循环来在数据库中插入多个记录。