Displaying php query in column instead of row

Question

Hey guys i have a postgres database to which i am connecting and fetching data from using php. I am a newbie to php. But anyway i have this as my query

$sql = "SELECT to_char (a.CALLDATE,'yyyymm') as month,min(a.calldate) as   start_time,max(a.calldate) as end_time,
         ceil(SUM (a.CALLDURATION::integer) / 60) AS minutes,
         COUNT (DISTINCT a.IDENTIFIANT) AS distinct_callers,
a.zoneiddest as country_code,b.country
FROM cdr_data a,COUNTRY_CODES b
WHERE  a.CALLSUBCLASS = '002'
     AND  a.CALLCLASS = '008'
and a.zoneiddest::integer > 0
AND SUBSTR (a.CALLEDNUMBER, 1, 2) NOT IN
('77', '78', '75', '70', '71', '41', '31', '39', '76','79')
and not substr(a.zoneiddest , 1 ,3) in ('254','255','256','211','257','250','256')
and trim(a.zoneiddest)  = trim(b.country_code)
GROUP BY to_char (a.CALLDATE,'yyyymm') ,a.zoneiddest,b.country
ORDER BY 1";

And i have this to return the data that is queried:

// iterate over result set
// print each row
while ($row = pg_fetch_array($result)) {
     echo "Month: " . $row[0] . "<br />";
     echo "Start Time: " . $row[1] . "<br />";
     echo "End Time: " . $row[2] . "<br />";
     echo "Minutes: " . $row[3] . "<br />";
     echo "Distinct Callers: " . $row[4] . "<br />";
     echo "Country Code: " . $row[5] . "<br />";
     echo "Country: " . $row[6] . "<br />";

  }

This returns my data in this format

Month: 201212
Start Time: 2012-12-28 15:51:04
End Time: 2012-12-28 15:51:04
Minutes: 0
Distinct Callers: 1
Country Code: 44
Country: United Kingdom of Great Britain and Northern Ireland

But would love to have this data in the table where month, start time, end time, minutes, distinct callers, country code and country are the column headers in the table. How can i achieve this.

cartina · Accepted Answer · 2013-01-15 07:37:01Z

2

Please check the code below. Let me know if my understanding of your requirements is incorrect.

   <table>
    <tr>
    <td>Month</td>
    <td>Start Time</td>
    <td>End Time</td>
    <td>Minutes</td>
    <td>Distinct Callers</td>
    <td>Country Code/td>
    <td>Country</td>
    </tr>
   <?php 
    while ($row = pg_fetch_array($result)) {
      echo '<tr>';
         echo "<td>" . $row[0] . "</td>";
         echo "<td>" . $row[1] . "</td>";
         echo "<td>" . $row[2] . "</td>";
         echo "<td>" . $row[3] . "</td>";
         echo "<td>" . $row[4] . "</td>";
         echo "<td>" . $row[5] . "</td>";
         echo "<td>" . $row[6] . "</td>";
     echo '</tr>';
        }
    ?>
</table>

answered Jan 15, 2013 at 7:37

cartina

1,43914 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dino Babu · Accepted Answer · 2013-01-15 07:37:06Z

1

dont forget to add loop

echo "<table cellpadding='5' cellspacing='1' style='background-color: #D7D2D2;font-size:12px;font-family:Tahoma;'>";
echo "<tr>";
echo "<th style='background-color: #FFFFFF;font-size:11px' align='left'>Month</th>";
echo "<th style='background-color: #FFFFFF;font-size:11px' align='left'>Start Time</th>";
echo "<th style='background-color: #FFFFFF;font-size:11px' align='left'>End Time</th>";
echo "<th style='background-color: #FFFFFF;font-size:11px' align='left'>Minutes</th>";
echo "<th style='background-color: #FFFFFF;font-size:11px' align='left'>Distinct Callers</th>";
echo "<th style='background-color: #FFFFFF;font-size:11px' align='left'>Country Code</th>";
echo "<th style='background-color: #FFFFFF;font-size:11px' align='left'>Country</th>";
echo "</tr>";
echo "<tr>";
echo "<td style='background-color: #FFFFFF;'>$row[0]</td>";
echo "<td style='background-color: #FFFFFF;'>$row[1]</td>";
echo "<td style='background-color: #FFFFFF;'>$row[2]</td>";
echo "<td style='background-color: #FFFFFF;'>$row[3]</td>";
echo "<td style='background-color: #FFFFFF;'>$row[4]</td>";
echo "<td style='background-color: #FFFFFF;'>$row[5]</td>";
echo "<td style='background-color: #FFFFFF;'>$row[6]</td>";
echo "</tr>";
echo "</table>";

answered Jan 15, 2013 at 7:37

Dino Babu

5,8093 gold badges26 silver badges33 bronze badges

1 Comment

roykasa Over a year ago

Yes this will work thanks alot. This has some styling which it a plus. Thanks

Roger · Accepted Answer · 2013-01-15 07:39:03Z

You can do it like this

$output = "<table>";
$output .= "<tr>";
$output .= "<td> Month </td>";
$output .= "<td> Start Time </td>";
$output .= "<td> End Time </td>";
$output .= "<td> Minutes </td>";
$output .= "<td> Distinct Callers </td>";
$output .= "<td> Country Codes </td>";
$output .= "<td> Country </td>";
$output .= "</tr>";

while ($row = pg_fetch_array($result)) {
     $output .= "<tr>";
     $output .= "<td>". $row[0] . "</td>";
     $output .= "<td>". $row[1] . "</td>";
     $output .= "<td>". $row[2] . "</td>";
     $output .= "<td>". $row[3] . "</td>";
     $output .= "<td>". $row[4] . "</td>";
     $output .= "<td>". $row[5] . "</td>";
     $output .= "<td>". $row[6] . "</td>";
     $output .= "</tr>";

  }
  $output .= "</table>";

  echo $output;

Hope this helps

Suhel Meman · Accepted Answer · 2013-01-15 07:39:38Z

1

echo "<table>";
   echo "<tr>";
       echo "<td>Month</td>";
       echo "<td>Start Time</td>";
       echo "<td>End Time</td>";
       echo "<td>Minutes</td>";
       echo "<td>Distinct Callers</td>";
       echo "<td>Country Code</td>";
       echo "<td>Country</td>";
   echo "</tr>";

   while ($row = pg_fetch_array($result)) 
   {
    echo "<tr>";
      echo "<td>".$row[0]."</td>";
      echo "<td>".$row[1]."</td>";
      echo "<td>".$row[2]."</td>";
      echo "<td>".$row[3]."</td>";
      echo "<td>".$row[4]."</td>";
      echo "<td>".$row[5]."</td>";
      echo "<td>".$row[6]."</td>";
   echo "</tr>";
  }
echo "</table>";

answered Jan 15, 2013 at 7:39

Suhel Meman

3,8621 gold badge20 silver badges27 bronze badges

Collectives™ on Stack Overflow

Displaying php query in column instead of row

4 Answers 4

Comments

dont forget to add loop

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

dont forget to add loop

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related