php - 在PHP中显示mysql表数据

Question

INSERT INTO `stock` (`stock_id`, `product_attributes`, `product_id`, `qty`) VALUES
(43, '9,11', 2, 0),
(43, '9,12', 2, 10),
(44, '9,13', 2, 20),
(45, '10,11', 2, 0),
(46, '10,12', 2, 30),
(47, '10,13', 2, 50),
(48, '14,11', 2, 0),
(49, '14,12', 2, 0),
(50, '14,13', 2, 0);

我有一个像这样的数据表，产品列数据采用子字符串格式，即 (9,11)

我必须列出所有出现的数量为零的产品。

SELECT 
    product_attributes,
    substring_index(product_attributes,',',-1) as one,
    substring_index(product_attributes,',',1)as two
FROM 
    (SELECT * 
     FROM stock 
     ORDER BY substring_index(product_attributes,',',1) ASC,
              substring_index(product_attributes,',',-1) ASC) AS ordered
WHERE 
    substring_index(product_attributes,',',1) NOT IN (
    SELECT substring_index(product_attributes,',',1)as one FROM stock WHERE qty!='0')
GROUP BY one'

我在数据库中运行了这个查询并得到了正确的输出!

现在我想显示第一列，(逗号)第二列......即(14,11)

mysql_select_db('fly_stock');

// run query
$q = mysql_query('select product_attributes, substring_index(product_attributes,',',-1)as one,substring_index(product_attributes,',',1)as two from(SELECT * FROM stock order by substring_index(product_attributes,',',1)ASC, substring_index(product_attributes,',',-1)asc)as ordered where substring_index(product_attributes,',',1)not in (select substring_index(product_attributes,',',1)as one from stock where qty!='0')group by one');

//print the items   
while($row = mysql_fetch_array($q)) {
  echo $row['one'] . " " . $row['two'];
  echo "<br>";
}
mysql_close($con);
?>

帮我编辑一下php代码，让14,11显示在PHP页面中。

Answer 1

您正在使用mysql_fetch_array当你想要mysql_fetch_assoc

//print the items   
while($row = mysql_fetch_assoc($q)) {
  echo $row['one'] . " " . $row['two'];
  echo "<br>";
}

顺便说一句，根据您的 php，您的 mysql_query() 函数也可能会产生错误，因为您的查询位于带有单引号的字符串中，并且您的 SQL 中有单引号，您没有转义。