2 MySQL tables => 1 HTML table

notset

Hello,

Situation: banner advertising system, statistics page.

I have 2 MySQL tables: impressions and hits:

CREATE TABLE `impressions` (
  `id` bigint(20) NOT NULL default '0',
  `datetime` datetime NOT NULL default '0000-00-00 00:00:00',
  `ad` bigint(20) NOT NULL default '0',
  PRIMARY KEY  (`id`)
);
CREATE TABLE `hits` (
  `id` bigint(20) NOT NULL default '0',
  `datetime` datetime NOT NULL default '0000-00-00 00:00:00',
  `ad` bigint(20) NOT NULL default '0',
  PRIMARY KEY  (`id`)
);

I want to put all information about ad's impressions and hits into the one HTML table.

<table>
<tr>
<td>Date</td>
<td>Impressions</td>
<td>Hits</td>
</tr>
{ loop start }
<td>$date</td>
<td>$impressions[$date]</td>
<td>$hits[$date]</td>
{ loop end }
</table>

Exactly: I have ad's id (ad - in MySQL table) and i need to get all data (when the ad was shown or/and clicked) from 2 MySQL tables.

Could anybody help?

Thank's.

sneakyimp

your post is still not clear. are the impressions and hits in a single table row all from the same ad? or from the same date?

scrupul0us

use an inner-join on the tables

notset

sneakyimp wrote:
your post is still not clear. are the impressions and hits in a single table row all from the same ad? or from the same date?

In a single table row all from the same date.

sneakyimp

Assuming there is no need to correlate any particular hit with any particular impression, you are going to need two queries.

Let's assume $ad_id contains the id of the ad you are interested in.

<?
$aggregate = array();

// count up the impressions
$sql = "SELECT * FROM impressions WHERE ad=" . $ad_id;
$result = mysql_query($sql)
  or die ("impressions query failed");

while ($row = mysql_fetch_assoc($result)) {
  $date_parts = explode(" ", $row['datetime']);
  if (!isset($aggregate[$date_parts[0]]['impressions'])) {
    $aggregate[$date_parts[0]]['impressions'] = 0;
  }
  $aggregate[$date_parts[0]]['impressions']++;
}


// count up the hits
$sql = "SELECT * FROM hits WHERE ad=" . $ad_id;
$result = mysql_query($sql)
  or die ("hits query failed");

while ($row = mysql_fetch_assoc($result)) {
  $date_parts = explode(" ", $row['datetime']);
  if (!isset($aggregate[$date_parts[0]]['hits'])) {
    $aggregate[$date_parts[0]]['hits'] = 0;
  }
  $aggregate[$date_parts[0]]['hits']++;
}

// now spit out the results
?>
<table>
 <tr>
  <td>Date</td>
  <td>Impressions</td>
  <td>Hits</td>
 </tr>
<?
foreach($aggregate as $date=>$value) {
?>
 <tr>
  <td><?=$date ?></td>
  <td><?=$value['impressions'] ?></td>
  <td><?=$value['hits'] ?></td>
 </tr>
<?
} // foreach
?>
</table>

I haven't tested this, so it may not work.