Page 1 of 1

MySQL Simple join, duplicate field names

Posted: Wed Apr 18, 2007 7:36 am
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

Posted: Wed Apr 18, 2007 7:48 am
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.

Posted: Wed Apr 18, 2007 7:56 am
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