CODEWITHSUNDEEP
phphtmlcsspostgresql
roykasa
roykasa

Reputation: 3027

Displaying php query in column instead of row

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.

Upvotes: 0

Views: 717

Answers (4)

Suhel Meman
Suhel Meman

Reputation: 3852

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>";

Upvotes: 1

Roger
Roger

Reputation: 1693

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

Upvotes: 1

Dino Babu
Dino Babu

Reputation: 5809

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>";

Upvotes: 1

cartina
cartina

Reputation: 1419

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>

Upvotes: 2

Related Questions