ids and syntax

I'm trying to get the winners of an auction and their credit card info.

I used this query:
$query = "SELECT name,nick,DECODE(creditcard,'$MD5_PREFIX') as CCN,
card_zip, exp_month,exp_year,PHPAUCTIONXL_users.id as usersid,
PHPAUCTIONXL_winners.id as winnersid,phone,auction, title FROM
PHPAUCTIONXL_users, PHPAUCTIONXL_winners, PHPAUCTIONXL_auctions WHERE
PHPAUCTIONXL_users.id = PHPAUCTIONXL_winners.winner ";

The syntax checks out, but it gives me columns where the winnersid !=
the users id. How do change it to get only columns where winnersid = usersid



This is another attempt- same thing but I added the last line:

"SELECT name,nick,DECODE(creditcard,'$MD5_PREFIX') as CCN,
card_zip,exp_month,exp_year,PHPAUCTIONXL_users.id as usersid,
PHPAUCTIONXL_winners.id as winnersid,phone,auction, title FROM
PHPAUCTIONXL_users, PHPAUCTIONXL_winners, PHPAUCTIONXL_auctions WHERE
PHPAUCTIONXL_users.id = PHPAUCTIONXL_winners.winner
AND PHPAUCTIONSXL_ winners.auction = PHPAUCTIONXL_auctions.id";

why is this a syntax error ? I can't find the error.

"meltedown" <fake@address.com> wrote in message
news:zeQxd.1228292$SM5.94317@news.easynews.com...
> I'm trying to get the winners of an auction and their credit card info.
>
> I used this query:
> $query = "SELECT name,nick,DECODE(creditcard,'$MD5_PREFIX') as CCN,
> card_zip, exp_month,exp_year,PHPAUCTIONXL_users.id as usersid,
> PHPAUCTIONXL_winners.id as winnersid,phone,auction, title FROM
> PHPAUCTIONXL_users, PHPAUCTIONXL_winners, PHPAUCTIONXL_auctions WHERE
> PHPAUCTIONXL_users.id = PHPAUCTIONXL_winners.winner ";
>
> The syntax checks out, but it gives me columns where the winnersid != the
> users id. How do change it to get only columns where winnersid = usersid
>
>
>
> This is another attempt- same thing but I added the last line:
>
> "SELECT name,nick,DECODE(creditcard,'$MD5_PREFIX') as CCN,
> card_zip,exp_month,exp_year,PHPAUCTIONXL_users.id as usersid,
> PHPAUCTIONXL_winners.id as winnersid,phone,auction, title FROM
> PHPAUCTIONXL_users, PHPAUCTIONXL_winners, PHPAUCTIONXL_auctions WHERE
> PHPAUCTIONXL_users.id = PHPAUCTIONXL_winners.winner
> AND PHPAUCTIONSXL_ winners.auction = PHPAUCTIONXL_auctions.id";
>
> why is this a syntax error ? I can't find the error.

Question 1: kind of hard to tell without your table layouts, but... did you
mean "where PHPAUCTIONXL_users.id = PHPAUCTIONXL_winners.id" ?

Question 2: perhaps the problem is the first "S" in
PHPAUCTIONSXL_winners.auction. Looks like a typo.

George

George King wrote:
> "meltedown" <fake@address.com> wrote in message
>>
>>"SELECT name,nick,DECODE(creditcard,'$MD5_PREFIX') as CCN,
>>card_zip,exp_month,exp_year,PHPAUCTIONXL_users.i d as usersid,
>>PHPAUCTIONXL_winners.id as winnersid,phone,auction, title FROM
>>PHPAUCTIONXL_users, PHPAUCTIONXL_winners, PHPAUCTIONXL_auctions WHERE
>>PHPAUCTIONXL_users.id = PHPAUCTIONXL_winners.winner
>>AND PHPAUCTIONSXL_ winners.auction = PHPAUCTIONXL_auctions.id";
>>
>>why is this a syntax error ? I can't find the error.
>
>
> Question 1: kind of hard to tell without your table layouts, but... did you
> mean "where PHPAUCTIONXL_users.id = PHPAUCTIONXL_winners.id" ?
>
Thanks for responding.
No, I really meant winners.winner
Each winnerr generates another winners id on the winners table. I
really do want the id of the winning user, not the winning auction.

winners table:
id auction seller winner bid closingdate fee

userstable:
id nick password name firstname lastname address city prov
country zip phone email reg_date rate_sum rate_num birthdate
suspended nletter balance auc_watch item_watch creditcard
exp_month exp_year card_owner card_zip accounttype endemailmode
startemailmode trusted lastlogin

auction table:
id user title starts description pict_url category minimum_bid
reserve_price buy_now auction_type duration increment location
location_zip shipping payment international ends current_bid
closed photo_uploaded quantity suspended private relist relisted
num_bids sold shipping_terms


> Question 2: perhaps the problem is the first "S" in
> PHPAUCTIONSXL_winners.auction. Looks like a typo.
>
> George
>
Yes, I has fixed that before I posted but didn't fix it in the message,
here is the corrected version:

SELECT name,nick,DECODE(creditcard,'$MD5_PREFIX') as CCN, card_zip,
exp_month,exp_year,PHPAUCTIONXL_users.id as usersid,
PHPAUCTIONXL_winners.id as winnersid,phone,auction, title
FROM PHPAUCTIONXL_users, PHPAUCTIONXL_winners, PHPAUCTIONXL_auctions
WHERE PHPAUCTIONXL_users.id = PHPAUCTIONXL_winners.winner

I still have the same problem - too many columns .
I don't understand why its giving me columns where the
PHPAUCTIONXL_users.id IS NOT EQUAL to the PHPAUCTIONXL_winners.winner
when I specifically told it to look where
WHERE PHPAUCTIONXL_users.id = PHPAUCTIONXL_winners.winner
here is a sample result
Ed simmons did not win an auction for a wigit, but mysql returned it
anyway. Notice that the winnersid does not equal the usersid

[Array
(
[name] => Ed Simmons
[nick] => rodeored
[CCN] => 424242424242
[card_zip] => 12404
[exp_month] => 07
[exp_year] => 07
[usersid] => 46
[winnersid] => 4
[phone] => 8456255555
[auction] => 32
[title] => wigit
)
]

I found another mistake,actually George was on the right track, but it
was winners.id before the WHERE that needed to be changed to winners.id
Thanks George. I still have the syntax problem.

This works good, it gives me only the winners:
$query = "SELECT name,nick,DECODE(creditcard,'$MD5_PREFIX') as CCN,
card_zip, exp_month,exp_year,PHPAUCTIONXL_users.id as usersid,
PHPAUCTIONXL_winners.winner as winnersid,phone,auction, title
FROM PHPAUCTIONXL_users, PHPAUCTIONXL_winners
WHERE PHPAUCTIONXL_users.id = PHPAUCTIONXL_winners.winner ";

However, I also want the title of the item won. So I added title to
the fields and PHPAUCTIONXL_auctions to the list of tables:

SELECT name,nick,DECODE(creditcard,'md5string') as CCN, card_zip,
exp_month,exp_year,PHPAUCTIONXL_users.id as usersid,
PHPAUCTIONXL_winners.winner as winnersid,phone,auction, title
FROM PHPAUCTIONXL_users, PHPAUCTIONXL_winners, PHPAUCTIONXL_auctions
WHERE PHPAUCTIONXL_users.id = PHPAUCTIONXL_winners.winner AND
PHPAUCTIONXL_ winners.auction = PHPAUCTIONXL_auctions.id

You have an error in your SQL syntax near 'winners.auction =
PHPAUCTIONXL_auctions.id ' at line 4

"meltedown" <fake@address.com> schreef in bericht
news:I2Xxd.5085663$yk.767792@news.easynews.com...
> George King wrote:
>> "meltedown" <fake@address.com> wrote in message
>>>
>>>"SELECT name,nick,DECODE(creditcard,'$MD5_PREFIX') as CCN,
>>>card_zip,exp_month,exp_year,PHPAUCTIONXL_users. id as usersid,
>>>PHPAUCTIONXL_winners.id as winnersid,phone,auction, title FROM
>>>PHPAUCTIONXL_users, PHPAUCTIONXL_winners, PHPAUCTIONXL_auctions WHERE
>>>PHPAUCTIONXL_users.id = PHPAUCTIONXL_winners.winner
>>>AND PHPAUCTIONSXL_ winners.auction = PHPAUCTIONXL_auctions.id";
>>>
>>>why is this a syntax error ? I can't find the error.
>>
>>
>> Question 1: kind of hard to tell without your table layouts, but... did
>> you mean "where PHPAUCTIONXL_users.id = PHPAUCTIONXL_winners.id" ?
>>
> Thanks for responding.
> No, I really meant winners.winner
> Each winnerr generates another winners id on the winners table. I really
> do want the id of the winning user, not the winning auction.
>
> winners table:
> id auction seller winner bid closingdate fee
>
> userstable:
> id nick password name firstname lastname address city prov country zip
> phone email reg_date rate_sum rate_num birthdate suspended nletter balance
> auc_watch item_watch creditcard exp_month exp_year card_owner card_zip
> accounttype endemailmode startemailmode trusted lastlogin
>
> auction table:
> id user title starts description pict_url category minimum_bid
> reserve_price buy_now auction_type duration increment location
> location_zip shipping payment international ends current_bid closed
> photo_uploaded quantity suspended private relist relisted num_bids sold
> shipping_terms
>
>
>> Question 2: perhaps the problem is the first "S" in
>> PHPAUCTIONSXL_winners.auction. Looks like a typo.
>>
>> George
> Yes, I has fixed that before I posted but didn't fix it in the message,
> here is the corrected version:
>
> SELECT name,nick,DECODE(creditcard,'$MD5_PREFIX') as CCN, card_zip,
> exp_month,exp_year,PHPAUCTIONXL_users.id as usersid,
> PHPAUCTIONXL_winners.id as winnersid,phone,auction, title
> FROM PHPAUCTIONXL_users, PHPAUCTIONXL_winners, PHPAUCTIONXL_auctions WHERE
> PHPAUCTIONXL_users.id = PHPAUCTIONXL_winners.winner
>
> I still have the same problem - too many columns .
> I don't understand why its giving me columns where the
> PHPAUCTIONXL_users.id IS NOT EQUAL to the PHPAUCTIONXL_winners.winner
> when I specifically told it to look where
> WHERE PHPAUCTIONXL_users.id = PHPAUCTIONXL_winners.winner
> here is a sample result
> Ed simmons did not win an auction for a wigit, but mysql returned it
> anyway. Notice that the winnersid does not equal the usersid
>
> [Array
> (
> [name] => Ed Simmons
> [nick] => rodeored
> [CCN] => 424242424242
> [card_zip] => 12404
> [exp_month] => 07
> [exp_year] => 07
> [usersid] => 46
> [winnersid] => 4
> [phone] => 8456255555
> [auction] => 32
> [title] => wigit
> )
> ]

I think you have 2 problems here.

First, you're comparing winnersid to usersid in you result set. These fields
don't refer to the same value, so they should not necessarily match. usersid
is equal to winners.winner. That last field is not in your result set.
Second, you don't have a restriction on the join you do on the auction
table. There should be an addition to the WHERE clause, something like 'AND
PHPAUCTIONXL_auctions.id = PHPAUCTIONXL_winners.auction'. This explains why
the row with Ed Simmons and the wigit is returned.

Hope this helps
Hilde

There is a space in

'PHPAUCTIONSXL_ winners.auction'

which is probably not supposed to be there?

Regards

Frank


"George King" <news@geking.com> wrote in message
news:JbRxd.6856$rL3.5228@trnddc03...
>
> "meltedown" <fake@address.com> wrote in message
> news:zeQxd.1228292$SM5.94317@news.easynews.com...
> > I'm trying to get the winners of an auction and their credit card info.
> >
> > I used this query:
> > $query = "SELECT name,nick,DECODE(creditcard,'$MD5_PREFIX') as CCN,
> > card_zip, exp_month,exp_year,PHPAUCTIONXL_users.id as usersid,
> > PHPAUCTIONXL_winners.id as winnersid,phone,auction, title FROM
> > PHPAUCTIONXL_users, PHPAUCTIONXL_winners, PHPAUCTIONXL_auctions WHERE
> > PHPAUCTIONXL_users.id = PHPAUCTIONXL_winners.winner ";
> >
> > The syntax checks out, but it gives me columns where the winnersid !=
the
> > users id. How do change it to get only columns where winnersid = usersid
> >
> >
> >
> > This is another attempt- same thing but I added the last line:
> >
> > "SELECT name,nick,DECODE(creditcard,'$MD5_PREFIX') as CCN,
> > card_zip,exp_month,exp_year,PHPAUCTIONXL_users.id as usersid,
> > PHPAUCTIONXL_winners.id as winnersid,phone,auction, title FROM
> > PHPAUCTIONXL_users, PHPAUCTIONXL_winners, PHPAUCTIONXL_auctions WHERE
> > PHPAUCTIONXL_users.id = PHPAUCTIONXL_winners.winner
> > AND PHPAUCTIONSXL_ winners.auction = PHPAUCTIONXL_auctions.id";
> >
> > why is this a syntax error ? I can't find the error.
>
> Question 1: kind of hard to tell without your table layouts, but... did
you
> mean "where PHPAUCTIONXL_users.id = PHPAUCTIONXL_winners.id" ?
>
> Question 2: perhaps the problem is the first "S" in
> PHPAUCTIONSXL_winners.auction. Looks like a typo.
>
> George
>
>