php - 将参数从 PHP 页面传递到 MySQL View

Question

我正在努力将参数('User_ID')从PHP页面传递到MySQL DB的 View (SELLER_INFORMTION)。你能指导一下吗？

代码如下:

<?php
session_start();
userid = $_SESSION['userid'];
include '../Database.class.php';
$dbe_obj = new Database_Executer();
$query =  "SELECT * FROM SELLER_INFORMATION('$userid')";
$data = $dbe_obj-> select_query_executer($query);
?>

CREATE VIEW SELLER_INFORMATION
AS SELECT `PRODUCT`.PRODUCT_NAME,`CATEGORY`.CATEGORY_NAME,`SELLER_PRODUCT`.QUANTITY,
`SELLER_PRODUCT`.UNIT_PRICE,`SELLER_PRODUCT`.CURRENCY 
FROM `PRODUCT`,`SELLER_PRODUCT`,`CATEGORY`,`USERS`
 WHERE SELLER_PRODUCT.Category_ID=CATEGORY.CATEGORY_ID 
    AND SELLER_PRODUCT.Product_ID = PRODUCT.PRODUCT_ID 
    AND SELLER_PRODUCT.User_ID=`USERS`.USER_ID 
    AND SELLER_PRODUCT.User_ID = '$userid'
    GROUP BY
    `PRODUCT`.PRODUCT_NAME,`CATEGORY`.CATEGORY_NAME,`SELLER_PRODUCT`.QUANTITY,
    `SELLER_PRODUCT`.UNIT_PRICE,`SELLER_PRODUCT`.CURRENCY 
    ORDER BY `CATEGORY`.CATEGORY_NAME DESC

Answer 1

将您的 View 更改为:

CREATE VIEW SELLER_INFORMATION
AS SELECT DISTINCT
          `PRODUCT`.PRODUCT_NAME,
          `CATEGORY`.CATEGORY_NAME,
          `SELLER_PRODUCT`.QUANTITY,
          `SELLER_PRODUCT`.UNIT_PRICE,
          `SELLER_PRODUCT`.CURRENCY,
          SELLER_PRODUCT.User_ID
FROM `PRODUCT`
JOIN `SELLER_PRODUCT` ON SELLER_PRODUCT.Product_ID = PRODUCT.PRODUCT_ID 
JOIN `CATEGORY` ON SELLER_PRODUCT.Category_ID=CATEGORY.CATEGORY_ID 
JOIN `USERS` ON SELLER_PRODUCT.User_ID=`USERS`.USER_ID 
ORDER BY `CATEGORY`.CATEGORY_NAME DESC

然后使用查询:

SELECT * FROM SELLER_INFORMATION WHERE User_ID = '$userid'

View 定义中不能有 PHP 变量。您需要返回 View 的 SELECT 列表中的列，以便可以在 WHERE 子句中与它进行比较。