Query and PHP Question

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
khymera
Forum Newbie
Posts: 2
Joined: Fri Apr 10, 2009 6:10 pm

Query and PHP Question

Post 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
webgrrl
Forum Newbie
Posts: 8
Joined: Mon Apr 13, 2009 4:27 pm

Re: Query and PHP Question

Post 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
khymera
Forum Newbie
Posts: 2
Joined: Fri Apr 10, 2009 6:10 pm

Re: Query and PHP Question

Post 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
Post Reply