Help with MYSQL Join and Group Concat

[madjack87]

Below is my query:

<?php
if (isset($_POST['search_all_submit'])){
  $search_all = strtolower($_POST['search_all']);
  echo "<table id='qbook'>";
  	echo "<tr>";
    echo "<td> CID </td>";
	  echo "<td> Company </td>";
	  echo "<td> Property </td>";
	  echo "<td> Contact </td>";
	  echo "</tr>";
  $sql = "
  SELECT contract.*, customer.*, contact.* FROM customer
  INNER JOIN contract ON customer.customer_id = contract.customer_id
  INNER JOIN contact ON customer.customer_id = contact.customer_id  
  WHERE 
  customer.company_name like '%".$search_all."%' or
  customer.billing_address like '%".$search_all."%' or
  contract.prop_name like '%".$search_all."%' or
  contract.prop_address like '%".$search_all."%' or
  contact.contact_fname like '%".$search_all."%' or
  contact.contact_lname like '%".$search_all."%'
  ";
  $results = mysql_query($sql)or die(mysql_error());
  while ($row = mysql_fetch_array($results)){
$company_name = implode('<span class="high">'.$search_all.'</span>',explode($search_all,strtolower($row['company_name'])));
$billing_address = implode('<span class="high">'.$search_all.'</span>',explode($search_all,strtolower($row['billing_address'])));
$prop_name = implode('<span class="high">'.$search_all.'</span>',explode($search_all,strtolower($row['prop_name'])));
$prop_address = implode('<span class="high">'.$search_all.'</span>',explode($search_all,strtolower($row['prop_address'])));
$fname = implode('<span class="high">'.$search_all.'</span>',explode($search_all,strtolower($row['contact_fname'])));
$lname = implode('<span class="high">'.$search_all.'</span>',explode($search_all,strtolower($row['contact_lname'])));

$CID = $row['CID'];
	 
	  echo "<tr>";
    echo "<td> $CID </td>";
	  echo "<td> $company_name <br /> $billing_address </td>";
	  echo "<td> $prop_name <br /> $prop_address </td>";
	  echo "<td> $fname $lname</td>";
	  echo "</tr>";
  }
  echo "</table>";
}
?>

The query above gives me results like:

 

10008 | jw property management | creekside crossing | mike pudelek

           | 4816 green bay rd            | 9219 66th ave        |

 

10008 | jw property management | creekside crossing | sam johnson

           | 4816 green bay rd            | 9219 66th ave        |

 

10008 | jw property management | creekside crossing | dean zierk

           | 4816 green bay rd            | 9219 66th ave        |

What I would like to see is:

 

10008 | jw property management | creekside crossing | dean zierk

           | 4816 green bay rd            | 9219 66th ave        | mike pudelek

                                                                                       | sam johnson

 

I am pretty sure that I need to use some type of GROUP CONCAT and possibly a GROUP BY. However all of my attemps have failed.

 

Any help would be appreciated.

 

 

 

[akphidelt2007]

You are going to have to do this on the PHP end. Changing the query with group by won't give you that desired result.

Edited September 5, 2013 by akphidelt2007

[madjack87]

Im pretty sure this can be done with mysql concat.

 

The reason that I need it to be completed within the query is because I am searching for the terms.