I am building a data analysis tool in php. I am querying our production server which is Postgres and displaying data.
I'm running my select query (date is passed as a variable) in a loop which display output like this:
1st Iteration :
Country sum(yesterday)
India 4500
Southafrica 5000
2nd Iteration :
country sum(day before)
India 5000
Southafrica 7000
Japan 4000
I want to display it in a table like this .
Country yesterday daybefore
India 4500 5000
Southafrica 5000 7000
Japan empty 4000
I have written the DAL based on this tutorial
http://net.tutsplus.com/tutorials/php/simple-php-class-based-querying/
any help would be great .
Query sample : this is run twice in a loop where $date = array('1','2')
query : select c.country_name, sum(tf.tx_amount_usd)
from
table1 tf,
table2 tp,
table3 d,
table4 c
where
tf.condition = tp.condition
and d.day = current_date-$date
group by 1,2 order by 2
Fetching Data :
$results = array();
while ($row = pg_fetch_array($res)){
$result = new DALQueryResult();
foreach ($row as $k=>$v){
$result->$k = $v;
}
$results[] = $result;
}
return $results;
Displaying data :
$dal = new DAL();
$dates = array('1','2');
foreach ($dates as $date) {
$results = $dal->get_trans_by_date($date);
echo "<h1>Data</h1>";
// check if there were any results
if ($results) {
// cycle through results
foreach ($results as $model) {
/* echo "<pre>";print_r($results);echo "</pre>"; */
echo "<li>$model->country_name ".number_format($model->sum,2)."</li>";
}
}
else {
// Display a message concerning lack of data
echo "<p>No Query Output.</p>";
}
}
It's a bit unclear of what you are trying to do. All those tags and we do not know wether you're asking for table-builing the data in PHP, or doing it immediately in Postgres.
Assuming you mean in Postgres, here is one way to achieve what you want:
@> SELECT name AS country, yesterday_sum, daybefore_sum \
FROM countries C \
INNER JOIN iteration2 A ON C.id = A.country \
LEFT JOIN iteration1 B ON C.id = B.country;
country | yesterday_sum | daybefore_sum
-------------+---------------+---------------
India | 4500 | 5000
Southafrica | 5000 | 7000
Japan | | 4000
(3 rows)
(I created the tables and inserted the test-data according to your output)
But again, due to the vague question I'm not sure this is what you're asking for so it's just a shot.