INNER JOIN and Repeat Region, show results only once.

[borisz]

Hi everyone,

I have fallowing problems for which Im sure some of your will find an solution. I ave fallowing MySQL request:

mysql_select_db($database_coupons_database, $coupons_database);
$query_ProfileHistory = "SELECT * FROM cd_brandopinions INNER JOIN cd_comments INNER JOIN users WHERE UserFID = '". $row_Profile['UserFID'] ."' and cd_brandopinions.MemberID = '". $row_Profile['UserFID'] ."' and cd_comments.AuthorID = '". $row_Profile['UserFID'] ."'";
$ProfileHistory = mysql_query($query_ProfileHistory, $coupons_database) or die(mysql_error());
$row_ProfileHistory = mysql_fetch_assoc($ProfileHistory);
$totalRows_ProfileHistory = mysql_num_rows($ProfileHistory);

so as you can see I INNER JOINED 2 tables from one database which works ok. Now for test I added fallowing repeat region with 2 results "BranOpinionTitle" and "CommentTitle":

<?php do { ?>
<?php echo $row_ProfileHistory['BranOpinionTitle']; ?><br />
<?php echo $row_ProfileHistory['CommentTitle']; ?><br />
<?php } while ($row_ProfileHistory = mysql_fetch_assoc($ProfileHistory)); ?>

and it does show results ok but when there is for e.g. 3 results for $row_ProfileHistory['CommentTitle'] it also repeats $row_ProfileHistory['BranOpinionTitle'] 3 times the same result. So what I want to do is to prevent it to repeat it same result more then once if other one have more results. I hope someone understood what Im trying to say/achieve and that someone can help me.

Thanks!

[coopster]

Do you mean that you only want to print the BranOpinionTitle once per set but print out the 3 CommentTitles? If so, you can initialize a comparison variable before starting your loop. I often use boolean false for this effect:

$BranOpinionTitle = false; // initialize 
do { 
  if ($BranOpinionTitle !== $row_ProfileHistory['BranOpinionTitle']) { 
    echo $row_ProfileHistory['BranOpinionTitle'] . '<br />'; 
  } 
  echo $row_ProfileHistory['CommentTitle'] . '<br />'; 
} while ($row_ProfileHistory = mysql_fetch_assoc($ProfileHistory));

[cheng830306]

study...

[rocknbil]

I don't see where you are joining on any of the table fields?

... inner join [joined_table] on [base_table_field]=[joined_table_field]

Inner Join Basics [w3schools.com]

[Angel33]

HI!
i have been looking for the same answer, i did use Coopster solution but it is still show all the
'BranOpinionTitle' repeated,

Here is my inner join:

mysql_select_db($database_exit, $exit);
$query_iaktresult = sprintf("SELECT jaktadmin_cat.name, Iakttagelser.JaktID, Iakttagelser.CatID,Iakttagelser.UppID, Iakttagelser.text, jaktadmin_cat.img, Iakttagelser.image FROM (Iakttagelser INNER JOIN jaktadmin_cat ON jaktadmin_cat.CatID=Iakttagelser.CatID) WHERE Iakttagelser.UppID=%s ORDER BY img ASC", GetSQLValueString($KTColParam1_iaktresult, "int"));
$iaktresult = mysql_query($query_iaktresult, $exit) or die(mysql_error());
$row_iaktresult = mysql_fetch_assoc($iaktresult);
$totalRows_iaktresult = mysql_num_rows($iaktresult);

and here is my repeat:

<?php 
$name = false;
do { 
if ($name !== $row_iaktresult['name']) {
echo $row_iaktresult['name'] . '<br />'; 
}
//echo $row_iaktresult['text']. '<hr/>'; 
} while ( $row_iaktresult = mysql_fetch_assoc($iaktresult));
?>

Thanks!

[coopster]

Yeah, I forgot to actually assign the value from the row to the $name variable so that on the next iteration of the loop the comparison would work. Update your loop construct. Using your code there ...

<?php 
$name = false; 
do { 
  if ($name !== $row_iaktresult['name']) { 
    $name = $row_iaktresult['name']; 
    echo $row_iaktresult['name'] . '<br />'; 
  } 
  //echo $row_iaktresult['text']. '<hr/>'; 
} while ($row_iaktresult = mysql_fetch_assoc($iaktresult)); 
?>

[Angel33]

Cheers!
thank yoo very much!