从PHP、MySql、Android两个表中检索数据

Question

有没有人知道我可以使用PHP从MySql数据库中的两个表中获取数据的方法？我的android应用程序允许用户在购物车（MySql中的表）中放置物品。当用户点击应用程序中的购物车按钮时，它会加载“购物车activity”，并根据“会话ID”或“客户ID”（如果他们已经登录）加载一个列表视图，其中包含他们添加到购物车中的所有内容。我现在所做的是，当项目被添加到购物车时，我捕获产品ID、产品名称、图像URL、数量和价格。它工作得很好，而且很快。我想做的是，由于产品名称、图像url和价格已经在“Products表”中，我想做同样的事情，但是当用户将一个项目添加到他们的购物车中时，只捕获产品ID和会话ID。当客户到购物车activity时，我希望我的PHP脚本在Shopping_Cart表中获取产品ID，然后在Products表中搜索该产品ID，并返回价格、图像URL、名称等。

下面是我的PHP脚本：

if($The_Function=="LOAD_CART"){
    $response = array();

    require_once __DIR__ . '/db_connect.php';

    $con = new DB_CONNECT();

    $user_session = $_GET['session'];
    $user_item_status = $_GET['status'];
    $order_total = 0.00;
    $result = mysql_query("SELECT * FROM shopping_cart WHERE SessionID LIKE '$user_session' AND Status LIKE '$user_item_status'");

    if(!empty($result)){
        if (mysql_num_rows($result) > 0) {
            $response["cart_contents"] = array();
            //$result = mysql_fetch_array($result);
            while ($row = mysql_fetch_array($result)) {
                $myItems = array();

                $myItems["product_id"] = $row["PID"];
                $myItems["product_name"] = $row["Item"];
                $myItems["product_price"] = $row["Price"];
                $myItems["product_qty"] = $row["Qty"];
                $myItems["image_url"] = $row["URL"];
                $myItems["line_id"] = $row["ItemCount"];
                $line_total = $row["Price"] * $row["Qty"];
                $order_total = $order_total + $line_total;
                array_push($response["cart_contents"], $myItems);
            }
            // success
            $response["success"] = 1;
            $response["order_total"] = $order_total;
            // user node
            //  $response["products"] = array();
            // echoing JSON response
            echo json_encode($response);
        } else {
            // no product found
            $response["success"] = 0;
            $response["message"] = "No product found";

            // echo no users JSON
            echo json_encode($response);
        }
    } else {
        // no product found
        $response["success"] = 0;
        $response["message"] = "No product found";

        // echo no users JSON
        echo json_encode($response);
    }
}

这就是我要做的。此代码不起作用。

if($The_Function=="LOAD_CART2"){
    $response = array();

    require_once __DIR__ . '/db_connect.php';

    $con = new DB_CONNECT();

    $user_session = $_GET['session'];
    $user_item_status = $_GET['status'];
    $order_total = 0.00;
    $result = mysql_query("SELECT * FROM shopping_cart WHERE SessionID LIKE '$user_session' AND Status LIKE '$user_item_status'");

    if(!empty($result)){
        if (mysql_num_rows($result) > 0) {
            $response["cart_contents"] = array();
            //$result = mysql_fetch_array($result);
            while ($row = mysql_fetch_array($result)) {
                $line_total = $row["Price"] * $row["Qty"];
                $line_ID = $row["ItemCount"];
                $Quantity = $row["Qty"];
                $Prod_ID = $row["PID"];


                $result2 = mysql_query("SELECT * FROM products WHERE pid LIKE '$Prod_ID'");
                $myItems = array();
                $theItem=$row["name"];
                $thePrice=$row["price"];
                $theImage=$row["prod_image"];

                $myItems["line_id"] = $line_ID;
                $myItems["product_qty"] = $Quantity;
                $myItems["product_id"] = $Prod_ID;
                $myItems["product_name"] = $theItem;
                $myItems["product_price"] = $thePrice;
                $myItems["image_url"] = $theImage;

                $order_total = $order_total + $line_total;
                array_push($response["cart_contents"], $myItems);
            }




            }
            // success
            $response["success"] = 1;
            $response["order_total"] = $order_total;
            // user node
            //  $response["products"] = array();
            // echoing JSON response
            echo json_encode($response);
        } else {
            // no product found
            $response["success"] = 0;
            $response["message"] = "No product found";

            // echo no users JSON
            echo json_encode($response);
        }
    } else {
        // no product found
        $response["success"] = 0;
        $response["message"] = "No product found";

        // echo no users JSON
        echo json_encode($response);
    }

这就是我刚才试过的：

if($The_Function=="LOAD_CART2"){
    $response = array();

    require_once __DIR__ . '/db_connect.php';

    $con = new DB_CONNECT();

    $user_session = $_GET['session'];
    $user_item_status = $_GET['status'];
    $order_total = 0.00;

    $result = mysql_query("SELECT s.*, p.* FROM shopping_cart AS s LEFT JOIN products AS p ON p.pid = s.PID WHERE s.SessionID ='$user_session' AND s.Status = '$user_item_status'");

    if(!empty($result)){
        if (mysql_num_rows($result) > 0) {
            $response["cart_contents"] = array();
            //$result = mysql_fetch_array($result);
            while ($row = mysql_fetch_array($result)) {
                $myItems = array();
                //FROM shopping_cart Table
                $line_total = $row["Price"] * $row["Qty"];
                $myItems["line_id"] = $row["ItemCount"];
                $myItems["product_qty"] = $row["Qty"];
                $myItems["product_id"] = $row["PID"];

                //FROM product TABLE
                $myItems["product_name"]=$row["p.name"];
                $myItems["product_price"]=$row["p.price"];
                $myItems["image_url"]=$row["p.prod_image"];


            }

            $order_total = $order_total + $line_total;
            array_push($response["cart_contents"], $myItems);


            }
            // success
            $response["success"] = 1;
            $response["order_total"] = $order_total;
            // user node
            //  $response["products"] = array();
            // echoing JSON response
            echo json_encode($response);
        } else {
            // no product found
            $response["success"] = 0;
            $response["message"] = "No product found";

            // echo no users JSON
            echo json_encode($response);
        }
    } else {
        // no product found
        $response["success"] = 0;
        $response["message"] = "No product found";

        // echo no users JSON
        echo json_encode($response);
    }

我的计划：

products TABLE

pid|name|price|created_at|prod_image|description|catagory



shopping_cart TABLE

FirstName|LastName|OrderNumber|CustomerID|Email|Price|Qty|Status|URL|PID|SessionID|CustomerType|ItemCount

Answer 1

您应该使用左联接，而不是对每个项执行单独的查询。

类似于：“选择s.*,p.*FROM shopping_cart AS s LEFT JOIN products AS p ON p.pid=s.pid，其中s.sessionid='$user_session'和s.status='$user_item_status'

假设小写pid在products表中，大写pid在shopping_card表中。