id # to queries a field

[ravingcommie]

I'm not sure that the subject makes sense, but here goes:

My news script attempts to get the user id (im my case 1) and then display it as a name, well thats what I would like it to do, I've had a play, but no luck.

My query is:

SELECT news.id, news.user_id, news.subject, news.short, news.body, news.date, users.user_id, users.realname FROM news,users WHERE news.user_id = users.user_id ORDER BY news.date DESC

Whereas the news function is:

function mainnews() {  

$news = "SELECT news.id, news.user_id, news.subject, news.short, news.body, news.date, users.user_id, users.realname FROM news,users WHERE news.user_id = users.user_id ORDER BY news.date DESC";  

$nav_news = mysql_query($news) or die(mysql_error());  

while($row = mysql_fetch_array($nav_news)){ 
       $author = $row['user_id'];
       $body = $row['body'];
       $subject = $row['subject']; 
       $date = $row['date']; 
       $date = gmdate("l, jS F Y \a\\t g:ia ",strtotime($date) +3600) ;

echo("<tr><td width=\"100%\" class=\"newsheader\">$subject</td></tr>"); 
echo("<tr><td width=\"100%\" class=\"news-date\">$author - $date</td></tr>"); 
echo("<tr><td width=\"100%\" class=\"news-body\">$body</td></tr>"); 
    	} 
	} 

Any help would be great. Thanks

[paulnaj]

Give us an example of the output you want ... and the output you're getting.

[ravingcommie]

I'm getting an output of:

"1 - Friday, 22nd August 2003 at 11:15am"

and would like an output of:

"Adam - Friday, 22nd August 2003 at 11:15am"

so the users.realname instead of users.user_id

[paulnaj]

You have: $author = $row['user_id'];
Surely, you should have: $author = $row['realname'];

[r270ba]

Do you have a users.realname field? If so then instead of users.user_id just set the Select statment to get users.realname....if not then you will need to add this field or manually code if users.id = 1 then name=Adam (which is very inefficient and won't work if you don't know the name of the user for each id.

r2

[ravingcommie]

Originally posted by paulnaj
You have: $author = $row['user_id'];
Surely, you should have: $author = $row['realname'];

That works, thanks alot. I didn't think of that, thought it would be in the SQL query

thanks

[r270ba]

Raving....mark this thread resolved if you dont' mind