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 
>
>
> Question 1:  kind of hard to tell without your table layouts, but... did y
ou
> 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: 
> 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
>
> 
> 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... 
the 
>
> 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
>
>