[Resolved] Join query, how to output?

[liam_d]

join query, how to output?

Hi all, i have this query i need to echo out all the contents in a loop:

SELECT 
	n.poster_id,
	n.date,
	n.subject,
	n.post,
	m.mid,
	m.username 
FROM 
	news n INNER JOIN  members m ON n.poster_id = m.mid
ORDER BY 
	n.nid DESC 
LIMIT 6

Although my while loop using a mysql_fetch_array gives this error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/altercod/public_html/mecha00.com/index2modules/news.php on line 19

What is the correct way to loop through the data of a joined sql statement? And then echo it out?

 

[Skeeter]

aliases

I see you resolved this but just in case someone else wants to know...

When you use a join query in PHP you have to alias the column names

example:

SELECT
n.poster_id AS Poster_ID,
n.date AS Date_Whatever,
n.subject AS Subject,
n.post AS Post,

You can then reference your columns in your PHP code by using the aliases

$str_PosterID=$row_Recordset1['Poster_ID'];

If you resolved the issue some other way please share. Thanks

 

[Eric]

I see you resolved this but just in case someone else wants to know...

When you use a join query in PHP you have to alias the column names

example:

SELECT
n.poster_id AS Poster_ID,
n.date AS Date_Whatever,
n.subject AS Subject,
n.post AS Post,

You can then reference your columns in your PHP code by using the aliases

$str_PosterID=$row_Recordset1['Poster_ID'];

If you resolved the issue some other way please share. Thanks

Not necessarily. You'd only really need to alias if you're selecting/using columns from both tables, that are the same. Eg: if you need to use 'username', but you're SELECT'ing the 'username' field from both tables.

This could have been resolved with:

SELECT n.poster_id, n.date, n.subject, n.post, m.mid, m.username
FROM news n, members m
WHERE n.poster_id = m.mid
ORDER BY n.nid DESC
LIMIT 6

OR

SELECT n.poster_id, n.date, n.subject, n.post, m.mid, m.username
FROM news AS n
LEFT JOIN members AS m ON (n.poster_id = m.mid)
ORDER BY n.nid DESC
LIMIT 6

 

[liam_d]

Thanks for the replies and yes it is not needed to give each field a name unless they have the same name.