"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