Page 1 of 1

Query and PHP Question

Posted: Tue Apr 14, 2009 2:36 pm
by khymera
Hello,

This is my first time in this forum, I have found some very useful information so far I have been searching for and answer to this particular query problem for a few days now and any help would be much appreciated. Using PHP to output the rows into variables.

I have the following query, I am wondering how I can get an extra column that displays the count, So far I can only get the first 2 columns in the table below but I would like to get the count "Displayed in Red Below" how I would like to see it - :banghead:

Code: Select all

SELECT users.id, bids.auction_id
    FROM ubidamin_devapp.users
    INNER JOIN ubidamin_devapp.bids ON ( users.id = bids.user_id )
    INNER JOIN ubidamin_devapp.auctions ON ( bids.auction_id = auctions.id )
    WHERE (
    
    users.gender_id !=0
    
    AND bids.auction_id !=0
    )
    
    ORDER BY bids.auction_id ASC
 
Equals the following results:

id | auction_id | count
602 | 55 | 2
602 | 55 | 2
602 | 58 | 1
53 | 71 | 1
53 | 82 | 1
53 | 121 | 2
53 | 121 | 2
53 | 139 | 1

Thank You

Re: Query and PHP Question

Posted: Tue Apr 14, 2009 3:41 pm
by webgrrl
Is the count a field in your table or do you want the query to count for you? If you want the query to count bids then this is what it would look like:

SELECT users.id, bids.auction_id, COUNT(bids.auction_id) as count
FROM ubidamin_devapp.users
INNER JOIN ubidamin_devapp.bids ON ( users.id = bids.user_id )
INNER JOIN ubidamin_devapp.auctions ON ( bids.auction_id = auctions.id )
WHERE (

users.gender_id !=0

AND bids.auction_id !=0
)

GROUP BY users.id
ORDER BY bids.auction_id ASC

This should return:

id | auction_id | count
602 | 55 | 2
602 | 58 | 1
53 | 71 | 1
53 | 82 | 1
53 | 121 | 2
53 | 139 | 1

Re: Query and PHP Question

Posted: Tue Apr 14, 2009 4:21 pm
by khymera
:D Thanks so much I did a variation of what you posted and it worked.

Here is the final output in case anyone is looking to output the count results on a Join query.

Code: Select all

SELECT users.id, bids.auction_id, COUNT( bids.auction_id ) AS count
FROM ubidamin_devapp.users
INNER JOIN ubidamin_devapp.bids ON ( users.id = bids.user_id )
INNER JOIN ubidamin_devapp.auctions ON ( bids.auction_id = auctions.id )
WHERE (
users.gender_id !=0
AND bids.auction_id !=0
)
GROUP BY bids.auction_id
ORDER BY bids.auction_id ASC
LIMIT 0 , 30