Joins and same field names

[lajkonik86]

What do do when you have some Joins in your sql and databases with similar field names. How do i make a distinction between the ones with the similar names.

EG:

$sql = "SELECT c. * , f. * , l. *
FROM csite_companies c
LEFT JOIN csite_filerefs f ON ( c.id = f.compid )
LEFT JOIN csite_locations l ON ( c.id = l.compid ) WHERE
LIMIT 0 , 30";
$recordset = mysql_query($sql) or die(mysql_error());
$count = 1;
$count2 = 1;
		while ($results = mysql_fetch_array($recordset))
					{
                             if($count2==1) {
                             $company['id'] = $results['id'];
                               }

Problem being that all 3 tables have an id field and i want the one from the company table.

Thanks🙂

[NogDog]

My first suggestion would be to only retrieve the fields you actually need, instead of using the "*" to retrieve everything. Where needed, you could then assign an alias to make it easier to reference it:

 $sql = "SELECT c.id AS company_id, c.another_field,  f.some_field <etc....>
FROM csite_companies c
LEFT JOIN csite_filerefs f ON ( c.id = f.compid )
LEFT JOIN csite_locations l ON ( c.id = l.compid ) WHERE
LIMIT 0 , 30";
//
// ...
//
$company['id'] = $results['comapny_id'];

This also has the advantage of only retrieving the data you need, rather than every field from every table, thus decreasing the time and memory needed transfer data between MySQL and your script.

[lajkonik86]

i need all, but thanks for this solution🙂

is it however possible to do something with the c.id f.id and similar constructions

[NogDog]

How about trying a print_r() or var_dump() of the result of a mysql_fetch_assoc() of your query result, and see how it labels each field. If that does not provide an obvious solution, then just add a specific field to your list:

SELECT c. *, c.id AS company_id, f. * , l. * FROM <...etc....>