How to display SQL query results in one line?

Question

I'll go crazy trying to get this to work, and here it is, I have 3 tables. I think it’s much better to show you that tables are “tblhosting”, “tblhostingconfigoptions” and “tblcustomfieldsvalues”. This can be seen here in the SQLFiddle setting: http://sqlfiddle.com/#!9/6c153/1

What I'm trying to do is make it on one line, see the picture:

Below is my SQL query:

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. 

I isolate <td> only from the problem, everything inside this PHP code:

 $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 

.... The idea above is to match $configid 126 or 127 or 128 and print the qty line in the field, respectively, which is 4 0 0, and not output "ded13526" 3 times and put 4, then the next line 0 , then the next line is 0.

Hope this works with enough explanation, I didn’t want to publish all the code to confuse, it is obvious that the IN statement has from 80 to 93, then 208, and this exact entry is only 93.

I just don’t know what is wrong.

Answer 1

If I understand your problem correctly, here's how to solve it:

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

 while($row = mysql_fetch_array($sql)) { ... echo '<td id="sid">' . $row['value'] . '</td>'; echo '<td id="smip">' . $row['dedicatedip'] . '</td>'; echo '<td id="10g">' . $row['qty126'] . '</td>'; echo '<td id="40g">' . $row['qty127'] . '</td>'; echo '<td id="100g">' . $row['qty128'] . '</td>'; ... 

Answer 2

Try the following:

 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