用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代码，以便在PHP页面中显示14,11。

Answer 1

当你想要数组的时候，你在使用阿索克

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

另外，基于您的php，您的mysql_query()函数可能也会产生一个错误，因为您的查询是一个带有单引号的字符串，而且您的SQL中有单引号，不能转义。

Answer 2

我是在这里遗漏了什么吗?还是您真的只想要one,two输出到PHP？

while($row = mysql_fetch_assoc($q)) {
  echo implode("," array($row['one'], $row['two']));
  echo "<br>";
}