problems with overwrites while executing a query in multiple tables

Jack_McSlay

let's say I have this query

SELECT data.*, art.*
FROM `comic_pages` data, `artwork_picture` art
WHERE data.art_id = art.uid

however both comic_pages and artwork_picture contains an 'uid' field, and I need both uids. I've made up this function, but I think it's kinda hacky (and slow, taking over 6x the speed of a normal mysqli_fetch_assoc in this case)

function db_multi_fetch_assoc ($result){
	if (!is_object ($result))return false;
	for ($x = 0;@mysqli_field_seek($result,$x);$x++){
		$field = mysqli_fetch_field ($result);
		$arr[$x] = $field->table.'.'.$field->name;
	}
	$data = mysqli_fetch_row ($result);

for ($y=0;$y<$x;$y++)$out[$arr[$y]] = $data[$y];
return $out;
}

if anyone can suggest me a better way I appreciate it

mrmigu

perhaps you could try listing all the variables in the select line instead of using *'s, and then renaming each one, ie data.uid as duid art.uid as auid

and then you could pull the uid's as $data['duid'] and $data['auid']

Roger_Ramjet

One thing that I have found usefull to do is to name foreign keys the same as the primary key it relates to. This helps to reinforce the relationship in my mind when viewing table schemas and stops me joining the wrong columns by mistake.
It also help to write joins using the USING(col) syntax - which has some benefits in the way joins are optimised and the columns that are returned.

So your query could (and should) be rewritten as

 SELECT data.*INNER JOIN art.* USING(auid)
FROM `comic_pages` data, `artwork_picture` art

Now one benefit of this is that you will only get 1 column named auid in the results.

Jack_McSlay

thanks for the reply, I've asked this more out of curiosity, since I found out it would be easier to simply rename the friggin table parameters, since all of them having the main index set as uid only for estethical purposes