Dedupe a mysql output using php

akatsuki27
akatsuki27 used Ask the Experts™
on
Hi,

I'm trying to figure out how to dedupe a query that I call using php that gives me duplicates. The reason being is because the ID from the main table has a one-to-many relationship with another table.

So when I

select main.column, other.column from main, other where main.ID=other.foreignkey;

I get this result:

fruit apple
fruit lemon
fruit grape
meat chicken
meat beef

I want to get rid of the duplicates in the first column. Well, not only the first because in reality I have a join where I'm using multiple tables and I don't want duplicates in those columns either.

So basically what I'm saying is I want to be able to print out 1 row per result for every column except the column from that one table.

example:

fruit     juice     red         tree      apple
                                                      lemon
                                                      grape
meat    sauce   brown   animal   chicken
                                                          beef

This is what I'm using to ouput the query:

echo "<table>";

        while($row = mysql_fetch_row($query)) {
                echo "<tr'>";
                foreach ($row as $str) {
                        echo "<td>".$str."</td>";
                }
                echo "</tr>";
        }
        echo "</table>";


I tried, and failed to do it using mysql so I'm trying now with php.  Can anybody help me out with this please? Is it clear what I'm asking for?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Really you should be using a join if you are using a key from one table to another, but to follow what you have now you could do something like the following:

select main.column as main, group_concat(other.column) as other from main, other where main.id=other.id group by main.column;

which would return something like the following:


+------+--------------------------+
| main | other                           |
+------+--------------------------+
| fruit   | apple,pear,lemon        |  
| meat | cow,pig                       |
+------+--------------------------+

you could then use <?php explode(',' , $result[other]) ?> to turn it into an array for however you want to use it.

best of luck!
MurfurFull Stack Developer

Commented:
You need a JOIN in the SQL query and a nested repeat in the PHP that only shows the group headings once until it changes.

Here is a simple example that I've done for two columns (food_group and flavour) but there is no reason why you can't extend it to the other columns, just remember to increment or change the $first_group & $last_group variable names for each different column you use otherwise you will get an interesting display i.e. $fist_colour and $last_colour, etc.
<?php
require_once( "dbconn.php" );

$strSQL = "SELECT foodgroups.food_group, flavours.flavour_name 
FROM foodgroups
INNER JOIN flavours ON foodgroups.id = flavours.foodgroup_id
ORDER BY food_group, flavour_name ASC";

$query = mysql_query( $strSQL, $conn )
or die( "Unable to query table:<br/>" . mysql_error() );
?>

<style type="text/css">
td{
	font-family: verdana, arial, sans-serif;
	font-size: 11px;
}
.heading{
	font-weight: bold;
}
</style>

<table border="1" bordercolor="#ccc" cellspacing="0" cellpadding="3" align="center">
	<tr class="heading">
		<td>Food group</td>
		<td>Flavours</td>
	</tr>
<?
#	iterate through query result to display data
while ( $row = mysql_fetch_array( $query ) ) {
?>
	<tr>
		<td>
			<?php
			#	Nested repeat - only show group name once
			$first_group = $row['food_group'];
			if ( $last_group != $first_group ){
				$last_group = $first_group;
				echo( $row['food_group'] );
			}
			?>		
		</td>
		<td><? echo( $row['flavour_name'] );?></td>
	</tr>
<?
#	END while
}
?>
</table>

Open in new window

food-groups.sql

Author

Commented:
Regality,

I like that idea. The thing is, I'm getting the data from a form which would be anywhere from 1 to 20 columns. How can I single out the group_concat column so that I can <br /> it where the commas are and it still prints in the order it was selected.

I was thinking of something like:

while($row = fetch_mysql_row($query)){
    $arr = explode(",",$row[$count]);   // $count where the group_concat column appears in the array
    echo "<tr>";
    if($arr){          
         foreach($row as $str){
               echo "<td>".$str."</td>";
               echo "<td>".$arr."<br /></td>";
           }
     } else
     {
          echo "<td>".$str."</td>";
     echo "</tr>";
}


I dont know if that works, I just coded it as I'm typing this.  Also, that sets the place of where the column data will go in the printout. I dont want that since I dont know in which position the user will select that column.  He could put it first in which case it will come out backwards. Actually, now that I think about it, wouldnt that print out the group_concat column twice?  I'm not very smart...
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Author

Commented:
btw, I just noticed I'm missing a closing curly bracket for the "else". That's what happens when you write impromptu code, I guess.

Author

Commented:
...And I wrote fetch_mysql_row instead of mysql_fetch_row
Commented:
If you wanted to put the group_concat() results into on table cell and have them separated by <br /> tags, then the following should work.
You will need to change the order by clauses according to your needs.

<?php

$query = "select main.column as main,
          group_concat(other.column
                       order by other.column
                       separator '<br />')
          from main
          left join other
          on main.id = other.id
          group by main.column
          order by main.column";

$result = mysql_query($query, $db);

echo "<table>";
while ($next = mysql_fetch_array($result)) {
    echo "<tr>";
    echo "<td>" . $next['main'] . "</td>";
    echo "<td>" . $next['other'] . "</td>";
    echo "</tr>";
}
echo "</table>";

?>

Open in new window

Commented:
I forgot to add a small piece of the query, that code won't work as is. It really should be:
$query = "select main.column as main,
          group_concat(other.column
                       order by other.column
                       separator '<br />') as other
          from main
          left join other
          on main.id = other.id
          group by main.column
          order by main.column";

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial