在Update MySql，PHP上将值从一个表插入另一个表

问题描述：

I was wondering if there was a way to take one value from a column in a Table and add it to a column in another table.

The Scenario: I have a table (Shopping_Cart). It stores all of my customers shopping cart items in the columns CustomerID, PID, Price, Status (Status can be "IN_CART" which means it was added and "OPEN" means that it has been paid for and ready to be processed and shipped). Then I have a table (Products). It stores product information in columns PID, Price,Weight`. When My customers place an item in the shopping cart I do not capture the price. Prices can change daily so if they add something today, when they come back tomorrow the price may increase or decrease in their shopping cart. When my customer checks out and payment is confirmed, I am trying to grab the Price in the (Products) table where Products.PID = Shopping_Cart.PID and then apply that price to the Shopping_Cart.Price to lock in that final price since the customer has paid for it. Any Ideas? I got it set up to change the items for that customers cart to "OPEN" but I am a little lost on getting my script to grab a value from one table and applying it to another table. Below is my PHP Script for changing the Lines from "IN_CART" to "OPEN".

if($The_Function=="CLOSE_LINES"){

    $cart_close_session_id = $_GET['close_session_id'];

    $response = array();


    require_once __DIR__ . '/db_connect.php';

    //connect to my db
    $db = new DB_CONNECT();

    $result = mysql_query("UPDATE shopping_cart SET Status = 'OPEN' WHERE SessionID LIKE '$cart_close_session_id' AND Status LIKE 'IN_CART'");

    if ($result) {
        $response["close_success"] = 1;

//GRAB PRICE FROM "PRODUCTS TABLE" AND UPDATE SHOPPING_CART 

    }else{
        $response["close_success"] = 0;
    }
    echo json_encode($response);
}

我想知道是否有办法从表中的列中取一个值并将其添加到列中在另一个表中。 p>

场景：我有一张桌子（Shopping_Cart）。它将我的所有客户购物车项目存储在列CustomerID code>， PID code>， Price code>， Status code>（状态可以是“IN_CART”，这意味着它被添加，“OPEN”意味着它已经支付并准备好进行处理和运输）。然后我有一张桌子（产品）。它将产品信息存储在 PID code>，Price ， code> Weight`列中。当我的顾客在购物车中放置物品时，我没有记录价格。价格可以每天变化，所以如果他们今天添加一些东西，当他们明天回来时，价格可能会在他们的购物车中增加或减少。当我的客户退房并确认付款时，我正试图在Products（产品）表中获取Products.PID = Shopping_Cart.PID的价格，然后将该价格应用于Shopping_Cart.Price以锁定自客户以来的最终价格已经付了钱。有任何想法吗？我把它设置为将客户购物车的商品更改为“打开”，但是我让我的脚本从一个表中获取值并将其应用到另一个表时有点迷失。下面是我的PHP脚本，用于将行从“IN_CART”更改为“OPEN”。 p>

  if（$ The_Function ==“CLOSE_LINES”）{
 
 $ cart_close_session_id =  $ _GET ['close_session_id']; 
 
 $ response = array（）; 
 
 
 require_once __DIR__。  '/db_connect.php'; 
nn //连接到我的db 
 $ db = new DB_CONNECT（）; 
 
 $ result = mysql_query（“UPDATE shopping_cart SET Status ='OPEN'WHERERE SessionID LIKE'$  cart_close_session_id'和状态LIKE'IN_CART'“）; 
 
 if if（$ result）{
 $ response [”close_success“] = 1; 
 
 //从”产品表“获取GRAB PRICE并更新SHOPPING_CART \  n 
} else {
 $ response [“close_success”] = 0; 
} 
 echo json_encode（$ response）; 
} 
  code>  pre> 
  div>

答

Assuming that you have a *shopping_cart* table with a price and a product_id column, you can update it with the price of the products table, like this:

UPDATE shopping_cart SET price = 
    (SELECT price FROM products WHERE id = shopping_cart.product_id)

答

You can join against the products table and update the shopping_cart upon purchase as seen below:

UPDATE shopping_cart c
  JOIN products p 
    ON p.pid = c.pid
   SET c.price = p.price
 WHERE c.customer_id = ?

在Update MySql，PHP上将值从一个表插入另一个表

相关推荐