PHP Display MYSQL Results within a loop

Question

I have a left join, code shown below that takes,

id
referrer
search term
client_id

From table 1 and then takes the following columns from table 2 using the left join query underneath.

client_id
visit_id
timedate

url1

 $query = "SELECT table1.id, table1.search_term, table1.referrer, table1.client_id,    table2.client_id, table2.url1, table2.visit_id, table2.timedate ".
  "FROM table1 LEFT JOIN table2 "
 "ON table1.id = table2.visit_id WHERE table1.ip_address = '$ip_address' AND table1.client_id='$client_id' Group BY visit_id, timedate";

   $result = mysql_query($query) or die(mysql_error());

      while($row = mysql_fetch_array($result)){ ?>
     <div id=''>
   <?php "<br />";
   echo $row['referrer']. " - ". $row['search_term']; 
   echo $row['timedate']. " - ". $row['url1']. " - ". $row['visit_id'];
   echo "<br />"; ?>
       </div>
   <?php
        }

What I am trying to do is format the rows so the referrer and search term only shows once and not on every line so that the results would look like this.

Referrer Search term

timedate url1 1 timedate Url1 1 timedate url1 1

referrer Search Term

timedate Url1 2 timedate Url1 2 timedate Url1 2

the numbers 1 and 2 are to represent different visit id's by which the results are grouped. At the moment i get the referrer and search term after every row because it is in the loop and understand that. Just don't know if i can show the referrer and searc term just once per group of results.

TiMESPLiNTER · Accepted Answer · 2013-11-01 13:47:09Z

You have to save the current pending referrer-searchterm combination and check if it changes, if yes, print out the referrer-searchterm line:

$query = "SELECT table1.id, table1.search_term, table1.referrer, table1.client_id,    table2.client_id, table2.url1, table2.visit_id, table2.timedate ".
  "FROM table1 LEFT JOIN table2 "
 "ON table1.id = table2.visit_id WHERE table1.ip_address = '$ip_address' AND table1.client_id='$client_id' Group BY visit_id, timedate" .
 "ORDER BY referrer, search_term";

$result = mysql_query($query) or die(mysql_error());
$currentReferrerSeatchTerm = null;

while($row = mysql_fetch_array($result)){   
    $newReferrerSearchTerm = $row['referrer']. " - ". $row['search_term'];

    echo '<div id=""><br>';

    if($currentReferrerSeatchTerm != $newReferrerSearchTerm){
       echo $newReferrerSearchTerm . '<br>'; 
       $currentReferrerSeatchTerm = $newReferrerSearchTerm
    }

    echo $row['timedate']. " - ". $row['url1']. " - ". $row['visit_id'];

    echo '<br></div>';
}

thomasruns · Accepted Answer · 2013-11-01 13:51:31Z

0

I usually set a var to store the referrer then check that on each loop through. If it's the same as the previous, do nothing. If it's different, display the new header info (referrer & search_term in your case) and then update the var.

$query = "SELECT table1.id, table1.search_term, table1.referrer, table1.client_id,    table2.client_id, table2.url1, table2.visit_id, table2.timedate ".
  "FROM table1 LEFT JOIN table2 "
 "ON table1.id = table2.visit_id WHERE table1.ip_address = '$ip_address' AND table1.client_id='$client_id' Group BY visit_id, timedate";

   $result = mysql_query($query) or die(mysql_error());

      $prevRef = '';
      while($row = mysql_fetch_array($result)){ ?>
     <div id=''>
   <?php "<br />";

   if($prevRef != $row['referrer']) {
       echo $row['referrer']. " - ". $row['search_term'];
       $prevRef = $row['referrer'];
   }

   echo $row['timedate']. " - ". $row['url1']. " - ". $row['visit_id'];
   echo "<br />"; ?>
       </div>
   <?php
        }

answered Nov 1, 2013 at 13:51

thomasruns

613 bronze badges

2 Comments

Sam Williams Over a year ago

Thanks, that makes more sense, the only problem is that it shows the referrer and search term for the first set of results but not the second. If you look at the link you will see that after the first row there is a different visit id but it doesn't show the referrer or search term for that row. goo.gl/gwekrU

thomasruns Over a year ago

Ah, I misunderstood. So instead of setting $prevRef to $row['referrer'], set it to $row['visit_id'] and then update the if statement accordingly. Of course you'll probably want to change the $prevRef var name to something more accurate too.

Collectives™ on Stack Overflow

PHP Display MYSQL Results within a loop

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related