Echo data from mysql base to page

Question

I have this query:

$query = "SELECT ads.*,
       trafficsource.name AS trafficsource,
       placement.name AS placement,
       advertiser.name AS advertiser,
       country.name AS country
       FROM ads
           JOIN trafficsource ON ads.trafficsourceId = trafficsource.id
           JOIN placement ON ads.placementId = placement.id
           JOIN advertiser ON ads.advertiserId = advertiser.id
           JOIN country ON ads.countryId = country.id
       WHERE advertiserId = '$advertiser_id'";

and ads table

ads Table
      ad_id PK
      size
      price
      trafficsourceId FK
      placementId FK
      advertiserId FK
      countryId FK

For getting data I'm using

$result = mysql_query($query) or die('Invalid query: ' . mysql_error());
    while ($row = mysql_fetch_assoc($result)) {

}

I cant figure out how I need to print page so that it's not looking like rows but also need id's of for example trafficsource name. I want to make something like that:

EDITED:

<div id="adscontent">
    <h1>Advertiser:</h1> Advertiser name
    <h2>Traffic Sources:</h2> Company1, Company2, Company 3
    <h2>Placements:</h2> Like: Newspaper, radio, website, bla bla
</div>

Thanks

Could you add the code that you've written that doesn't work? — andrewsi, Oct 12 '12 at 15:35
[Don't write new code with `mysql_*`](http://stackoverflow.com/q/12859942/19068)! — Quentin, Oct 12 '12 at 15:47

Nikolaos Dimopoulos · Accepted Answer · 2012-10-12T18:34:31.383

You will need to play around with the printout but I think something like this will work:

$results = array();
while ($row = mysql_fetch_assoc($result)) {
    $results[$row['advertiser']]['countries'][]      = $row['country'];
    $results[$row['advertiser']]['trafficsources'][] = $row['trafficsource'];
    $results[$row['advertiser']]['placements'][]     = $row['placement'];
}

// And now print the data
foreach ($results as $arvertiser => $data)
{
    echo "<h1>{$advertiser}</h1>";

    // Print Placements
    echo "Placements: " . implode(", ", $data['placements']) . '<br />;

    // Print Countries
    echo "Countries: " . implode(", ", $data['countries']) . '<br />;

    // Print Placements
    echo "Traffic Sources: " . implode(", ", $data['trafficsources']) . '<br />;

}

EDIT: If you need to add the IDs you will need to change your select to:

$query = "SELECT ads.*,
       trafficsource.name AS trafficsource,
       trafficsource.id AS trafficsourse_id,
       placement.name AS placement,
       placement.id AS placement_id,
       advertiser.name AS advertiser,
       advertiser.id AS advertiser_id,
       country.name AS country
       country.id AS country_id
       FROM ads
           JOIN trafficsource ON ads.trafficsourceId = trafficsource.id
           JOIN placement ON ads.placementId = placement.id
           JOIN advertiser ON ads.advertiserId = advertiser.id
           JOIN country ON ads.countryId = country.id
       WHERE advertiserId = '$advertiser_id'";

From then on you can include this information in the $results array like so:

$results[$row['advertiser']['countries'] = array(
                                               'id'    => $row['country_id'], 
                                               'value' => $row['country')
                                           );

and print out whatever you need from there.

This explains a lot, thanks. Is there a way to get ID's for placement, countries and traffic sources too? — zokopog, Oct 12 '12 at 17:39
I don't understand what do you mean by IDs. If you are referring to the `placement.id` or `trafficsource.id` then you will need to change your SELECT statement to include those fields and include them in the array. — Nikolaos Dimopoulos, Oct 12 '12 at 18:28

Echo data from mysql base to page

1 Answers1