php - 如何在一行中显示SQL查询结果？

Question

我正在努力让它工作，在这里，我有 3 个表。我认为向您展示表是“tblhosting”、“tblhostingconfigoptions”和“tblcustomfieldsvalues”会更好。它可以在 SQLFiddle 设置中看到:http://sqlfiddle.com/#!9/6c153/1

我想要完成的是在一行中完成，见图:

下面是我的 SQL 查询:

SELECT DISTINCT 
t1.domainstatus,
t1.server,
t1.dedicatedip,
t1.packageid,
t1.regdate,
t1.nextduedate,
t2.value,
t2.fieldid,
t3.configid,
t3.qty
FROM tblhosting t1
INNER JOIN tblcustomfieldsvalues t2 
ON t2.relid = t1.id 
INNER JOIN tblhostingconfigoptions t3 
ON t3.relid = t1.id
WHERE t3.configid IN (126,127,128) AND t2.fieldid IN
(83,84,85,86,87,88,90,91,92,93,208) ORDER by t1.id -- I use to have GROUP by t1.id and get 1 line for the 126 but then 127 128 will not be produced.

我将隔离 <td>就问题而言，这一切都在这段 PHP 代码中:

$sql = mysql_query($queryText);

while($row = mysql_fetch_array($sql)) {

  $qty = $row['qty'];
  $configid = $row['configid'];

  echo '<td id="sid">' . $row['value'] . '</td>';
  echo '<td id="smip">' . $dedicatedIP . '</td>';
  echo '<td id="10g">'; if ($configid == 126) { echo '4' } echo'</td>'; // the qty matching 126 should display there
  echo '<td id="40g">'; if ($configid == 127) { echo '0'} echo'</td>'; // the qty matching 127 should display there 
  echo '<td id="100g">'; if ($configid == 128) { echo '0' } echo'</td>'; // the qty matching 128 should display there

.... 上面的想法是制作$configid匹配 126 或 127 或 128，并将行 qty 输出到字段中因此这是 4 0 0 而不是输出“ded13526”的 3 倍，然后将 4 放在下一行 0 然后放在下一行 0。

希望它有足够的解释，我不想发布整个代码来混淆，很明显 IN语句有 80 到 93，然后是 208，而这个确切的条目只有 93..

我只是不知道出了什么问题..

Answer 1

试试这个:

SELECT DISTINCT 
t1.domainstatus,
t1.server,
t1.dedicatedip,
t1.packageid,
t1.regdate,
t1.nextduedate,
t2.value,
t2.fieldid,
t3.configid,
t3.qty
FROM tblhosting t1
INNER JOIN tblcustomfieldsvalues t2 
ON t2.relid = t1.id 
INNER JOIN tblhostingconfigoptions t3 
ON t3.relid = t1.id
WHERE t3.configid IN (126,127,128) AND t2.fieldid IN
(83,84,85,86,87,88,90,91,92,93,208) GROUP BY t2.fieldid