Mysql Select problem

Hi

Currently im using this

$query = "select * from news, users where news.User_ID = users.User_ID order
by news.News_ID DESC"

only problem is both tables have User_ID, how can I make aliases of these?

Donno how to do it :S

re: Mysql Select problem

On 19-Mar-2004, "Hayden Kirk" <spam@spam.com> wrote:
[color=blue]
> Currently im using this
>
> $query = "select * from news, users where news.User_ID = users.User_ID
> order
> by news.News_ID DESC"
>
> only problem is both tables have User_ID, how can I make aliases of these?[/color]

select news.User_ID as userid, * from ...

--
Tom Thackrey
www.creative-light.com
tom (at) creative (dash) light (dot) com
do NOT send email to jamesbutler@willglen.net (it's reserved for spammers)

re: Mysql Select problem

Can you give me an exampl, I cant get it :(

Thanks,
Hayden

"Tom Thackrey" <use.signature@nospam.com> wrote in message
news:BZQ6c.26434$z35.16512@newssvr29.news.prodigy. com...[color=blue]
>
> On 19-Mar-2004, "Hayden Kirk" <spam@spam.com> wrote:
>[color=green]
> > Currently im using this
> >
> > $query = "select * from news, users where news.User_ID = users.User_ID
> > order
> > by news.News_ID DESC"
> >
> > only problem is both tables have User_ID, how can I make aliases of[/color][/color]
these?[color=blue]
>
> select news.User_ID as userid, * from ...
>
> --
> Tom Thackrey
> www.creative-light.com
> tom (at) creative (dash) light (dot) com
> do NOT send email to jamesbutler@willglen.net (it's reserved for spammers)[/color]

re: Mysql Select problem

On 20-Mar-2004, "Hayden Kirk" <spam@spam.com> wrote:
[color=blue]
> "Tom Thackrey" <use.signature@nospam.com> wrote in message
> news:BZQ6c.26434$z35.16512@newssvr29.news.prodigy. com...[color=green]
> >
> > On 19-Mar-2004, "Hayden Kirk" <spam@spam.com> wrote:
> >[color=darkred]
> > > Currently im using this
> > >
> > > $query = "select * from news, users where news.User_ID = users.User_ID
> > > order
> > > by news.News_ID DESC"
> > >
> > > only problem is both tables have User_ID, how can I make aliases of[/color][/color]
> these?[color=green]
> >
> > select news.User_ID as userid, * from ...
> >[/color]
> Can you give me an exampl, I cant get it :([/color]

$query = "select news.User_ID as userid,* from news,users where
news.UserID=users.UserID order by news.News_ID desc";

When you fetch the results the value of news.UserID will be in the result
array as index ['userid'] (or [0])


--
Tom Thackrey
www.creative-light.com
tom (at) creative (dash) light (dot) com
do NOT send email to jamesbutler@willglen.net (it's reserved for spammers)

re: Mysql Select problem

Tom Thackrey wrote:[color=blue]
> On 19-Mar-2004, "Hayden Kirk" <spam@spam.com> wrote:
>
>[color=green]
>>Currently im using this
>>
>>$query = "select * from news, users where news.User_ID = users.User_ID
>>order
>>by news.News_ID DESC"
>>
>>only problem is both tables have User_ID, how can I make aliases of these?[/color]
>
>
> select news.User_ID as userid, * from ...
>[/color]
Since the WHERE clause is making them equal it does not matter which
variable ends up in the Select list; both is ok too. In order to better
abbreviate the WHERE predicates you could correlate the tables to an
alias.

$query = "select * from news a, users b where a.User_ID = b.User_ID
order by a.News_ID DESC"

What particular problem were you having?
Not absolutely sure of the syntax....you MAY need the AS keyword for the
correlation variable: ... news AS a, users AS b ....

Disclaimer: Not tested

Chris
Always remember, you are unique...just like everyone else.

re: Mysql Select problem

Thanks both, been a great help.

The problem I was having is what if I wanted to get both User_ID's from both
tables... the array is only going to hold one of them.

So I was wondering If I could use an alias, but everything I tried I got
errors. So far this is what I have:

select * from users, news where users.User_ID = news.User_ID order by
news.News_ID DESC;

But there will only be one User_ID in my array... how would I change this to
make an alias? So like News.User_ID, User.User_ID?

Maybe I should have a seperate table to take this out of the news table as
it shouldn't really be there, compound enitity :S

"pomasl<nospam> @starband.net>" <"pomasl<nospam> wrote in message
news:GUi7c.1372$oD.1030@fe25.usenetserver.com...[color=blue]
> Tom Thackrey wrote:[color=green]
> > On 19-Mar-2004, "Hayden Kirk" <spam@spam.com> wrote:
> >
> >[color=darkred]
> >>Currently im using this
> >>
> >>$query = "select * from news, users where news.User_ID = users.User_ID
> >>order
> >>by news.News_ID DESC"
> >>
> >>only problem is both tables have User_ID, how can I make aliases of[/color][/color][/color]
these?[color=blue][color=green]
> >
> >
> > select news.User_ID as userid, * from ...
> >[/color]
> Since the WHERE clause is making them equal it does not matter which
> variable ends up in the Select list; both is ok too. In order to better
> abbreviate the WHERE predicates you could correlate the tables to an
> alias.
>
> $query = "select * from news a, users b where a.User_ID = b.User_ID
> order by a.News_ID DESC"
>
> What particular problem were you having?
> Not absolutely sure of the syntax....you MAY need the AS keyword for the
> correlation variable: ... news AS a, users AS b ....
>
> Disclaimer: Not tested
>
> Chris
> Always remember, you are unique...just like everyone else.[/color]

re: Mysql Select problem

On 21-Mar-2004, "Hayden Kirk" <spam@spam.com> wrote:
[color=blue]
> The problem I was having is what if I wanted to get both User_ID's from
> both
> tables... the array is only going to hold one of them.
>
> So I was wondering If I could use an alias, but everything I tried I got
> errors. So far this is what I have:
>
> select * from users, news where users.User_ID = news.User_ID order by
> news.News_ID DESC;
>
> But there will only be one User_ID in my array... how would I change this
> to
> make an alias? So like News.User_ID, User.User_ID?[/color]

I think I answered your question before, but here it is again

select users.User_ID as userid, * from ...

This will create an extra column called userid which will contain
users.User_ID.

You say you want both users.User_ID and news.User_ID but your select will
ensure they have the same value, so why do you need both??

If you really want both you would code something like

select users.User_ID as usersuserid, news.User_ID as newsuserid, * from...

This will create two extra columns, usersuserid and newsuserid.

--
Tom Thackrey
www.creative-light.com
tom (at) creative (dash) light (dot) com
do NOT send email to jamesbutler@willglen.net (it's reserved for spammers)