How to display SQL query results in one line?

Steini Petur

I am going out of my mind trying to get this to work and here it is, I have 3 tables. I think it's much better to show you the tables are "tblhosting", "tblhostingconfigoptions" and "tblcustomfieldsvalues" It can be seen here in the SQLFiddle setup: http://sqlfiddle.com/#!9/6c153/1

What I am trying to accomplish is making it in one line, see 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'll isolate the <td> to just the problem, it's all 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 make the $configid match 126 or 127 or 128, and output into the field the row qty accordingly which is 4 0 0 and not output 3 times the "ded13526" and put 4 then next line 0 then next line 0.

Hope it comes off with enough explanation, I didn't want to post the entire code to confuse, it's obvious the IN statement has 80 to 93 then 208 and this exact entry is only 93..

I just don't know what is wrong..

dened

If I understand your problem correctly, here is how it can be solved:

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>';
  ...

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

How to display two results within one query?

How to insert sql query results in one single line in csv from php?

Display output in one line SQL

How to merge two sql queries results? One query has GROUP BY

SQL: Use results of one query in another query

SQL - Query results that display a record with multiple rows of data on one row horizontally

display multiple results from a sql query

Display output of MySQL query in one line/ horizontally

How to Query by line in SQL

Hive - How to display Hive query results in the Command Line along with column names

How do I get Ubuntu to display `apt search` results on one line

SQL Pivot Table Not Putting Results In One Line

How to get the JavaScript display results "on click" query to display ALL results

How to extract results from multiple query results tabs into one excel in SQL Developer?

How to use the results of one query in the next query?

Rounding in SQL - inconsistent results for one query?

Getting two different results by one SQL query

How to filter results of this SQL query?

How to display a long Select statement to display on one line, preferably in SQL Server Management Studio, or other editor?

Use multiple SQL query results in one SQL query

How to add query to display the results? / TNTSearch

How to fix query in order to display results?

How to display results of intermediate transformations of streaming query?

Django How to display error if the query results are empty

How to display the results of an API query in Svelte?

How to get two Query in one JSON to display on Chart.js line Chart?

How to pass tuple results in one line

Display one entry of each type SQL Query

How to display several columns of sql results from the same column in one search