CODEWITHSUNDEEP
mysql
Daisy de Melker
Daisy de Melker

Reputation: 29

Show results from mysql query with php

I have following code: 

$query = "SELECT ads.*,
       trafficsource.name AS trafficsource,
       trafficsource.id AS trafficsourse_id,
       FROM ads
           JOIN trafficsource ON ads.trafficsourceId = trafficsource.id
        WHERE advertiserId = '$advertiser_id'";

        $mysqli = new mysqli();
        $mysqli->connect('localhost', 'root', '', 'adsbase');
        $result = $mysqli->query($query);
        while ($row = $result->fetch_assoc()) {
            echo "<h2>Traffic Sources: {$row['trafficsource']}</h2>";
        }

This code show results like: 

Traffic Sources: Example1
Traffic Sources: Example2
Traffic Sources: Example2
Traffic Sources: Example1
Traffic Sources: Example2
Traffic Sources: Example1

What I want and can't figure out is to show results like: 

Traffic Sources: Example1, Example2

So without duplicates and also all in one line.

Upvotes: 0

Views: 132

Answers (4)

RobMasters
RobMasters

Reputation: 4148

You nearly had it:

    $result = $mysqli->query($query);
    $trafficeSources = array();
    while ($row = $result->fetch_assoc()) {
        $trafficSources[] = $row['trafficsource'];
    }

    echo '<h2>Traffic Sources: ' . implode(', ', $trafficSources) . '</h2>';

EDIT: Query using DISTINCT

I'm assuming you only need the traffic source name, but feel free to add more columns back in if they're required. Bear in mind though that the DISTINCT applies to a distinct combination of all rows returned, so it may be possible that you could end up with duplicate traffic sources if other selected columns differ.

$query = "SELECT DISTINCT trafficsource.name AS trafficsource
          FROM ads
          JOIN trafficsource ON ads.trafficsourceId = trafficsource.id
          WHERE advertiserId = '$advertiser_id'";

Upvotes: 2

ganesh
ganesh

Reputation: 1066

Use Group by in 'trafficsource' in query and then in while loop concat each value of $row['trafficsource'] to other and echo it outside the while loop.

Upvotes: 0

GarethL
GarethL

Reputation: 1493

$trafficSources = array();
while ($row = $result->fetch_assoc()) {
    if(!in_array($row['trafficsource'], $trafficSources) {
        $trafficSources[] = $row['trafficsource'];
    }
}

echo "<h2>Traffic Sources: ".implode(', ', $trafficSources)."</h2>";

Upvotes: 0

RDK
RDK

Reputation: 4560

Try this small solution:

     $trafic = array();
     while ($row = $result->fetch_assoc()) {
                if(!in_array($trafic)) $trafic[] = $row['trafficsource']
    }
    if(!empty($trafic)){
      echo  '<h2>Traffic Sources:' . imlode(',', $trafic) .'</h2>';
    }

Upvotes: 0

Related Questions