MySQL Simple join, duplicate field names

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

MySQL Simple join, duplicate field names

Post by batfastad »

Hi guys

This has been bugging me for a while but I've been unable to find the definitive answer.

I'm doing a simple join with the following query...

Code: Select all

SELECT * FROM `adinserts`, `publications` WHERE adinserts.booking_id = 2 AND publications.publication_id = adinserts.publication_id ORDER BY publications.art_date ASC LIMIT 0, 100
Where the 2 tables are related by a field called publication_id

Then I access the data by doing the following...

Code: Select all

	// LOOP THROUGH ADINSERTS
	while ($adinserts = mysql_fetch_assoc($sql_result)) {

		// GET DATA
		$id = $adinserts['id'];
		$publication_id = $adinserts['publication_id'];
		$publication = $adinserts['publication'];
		$web = $adinserts['web'];
		$cancel_name = $adinserts['cancel_name'];
		$cancel_stamp = $adinserts['cancel_stamp'];
...
The problem is... as well as the publication_id field, both tables also have an id column which are auto_increment fields. These id fields are not related to each other.

The code above gets the $id value from the publications table, but how do I get $id to be the value from the adinserts table?
Where you have 2 fields with the same name, how do I access the first field?

Thanks
Ben
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Post by jayshields »

There are a few ways to do this. One way would be to modify your SQL query to select the fields you want (instead of selecting them all) and using an AS clause to rename the fields it fetches.

So it would look something like this:

Code: Select all

SELECT `table1`.`id` AS `t1_id`, `table2`.`id` AS `t2_id` FROM `table1`, `table2`
Then you can reference the fields as t1_id and t2_id in the array instead of having a naming clash for id.
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Post by batfastad »

Hi Jay

Great tip - works a treat :lol: :lol:

This is what I modified my query to...

Code: Select all

SELECT *, `adinserts`.`id` AS `adinserts_id` FROM `adinserts`, `publications` WHERE `adinserts`.`booking_id` = 2 AND `publications`.`publication_id` = `adinserts`.`publication_id` ORDER BY `publications`.`art_date` ASC LIMIT 0, 100
In total I have about 80 fields I need to access here so referencing each one individually would have created some horrible looking query.

I took a guess on doing this... SELECT *, `adinserts`.`id` and it worked.

Thanks so much for your help!
Ben
Post Reply