Search the Community

I have an existing two table structure where sub-data is related to main data in a many-to-one relationship. There are several identically named columns in both tables. (The PHP database class will return an associative array that did not deal with identical keys in the row.) I'm LEFT JOINing the sub-data to the main-data tables. When using a stand-alone query browser (to experiment), the display shows all columns, even those column names that appear more than once in the row. Is there a general approach to making keys (column names) unique strictly using a MySQL SELECT statement? Perhaps using table aliases, how can I concat the alias to the columns that come from each table? What I am trying to avoid is giving an alias to each column in a list of column names, such as: SELECT C.name AS C_name C.numb AS C_numb D.name AS D_name D.numb AS D_numb etc. I am wanting more like the result one could guess would be from: C.* AS C_* D.* AS D_* Or better: * AS CONCAT(__TABLE__, '_', *) if __TABLE was a magic constant (like PHP's __LINE__, __FILE__, etc).

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.