counting records

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
User avatar
Think Pink
Forum Contributor
Posts: 106
Joined: Mon Aug 02, 2004 3:29 pm

counting records

Post by Think Pink »

hello, pls help.

i have 2 table
tbl_domains
id_domain
domain

tbl_books
id_book
book_name
code_domain

i have a place on my page, where all my domains are listed, but I don't know how i could write near the domain how manny books I have in that domain.

something like that
sf (15)
programming (135)
design (17)

thx in advance for any help.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

Select all the books then use mysql_num_rows() to see how many you have :wink:
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

or!

SELECT COUNT(*) AS `total` FROM ...
User avatar
Think Pink
Forum Contributor
Posts: 106
Joined: Mon Aug 02, 2004 3:29 pm

Post by Think Pink »

well, thx, but the ideea was that i don't know how to print the corect books number from a certain domain.
i know about select count, but the other part I don't know.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

not quite sure, but try GROUP BY `domain`
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

Phenom wrote:not quite sure, but try GROUP BY `domain`
Why not inner join and use a WHERE clause?

EDIT | Forget that - you have no foreign key to use from what I can gather.

EDIT AGAIN | Is code_domain the foreign key for tbl_domain?

Code: Select all

&quote;SELECT COUNT(*) FROM tbl_domains, tbl_books WHERE
tbl_domains.id_domain = tbl_books.code_domain AND
tbl_domains.domain = '$domain'&quote;
User avatar
Think Pink
Forum Contributor
Posts: 106
Joined: Mon Aug 02, 2004 3:29 pm

Post by Think Pink »

i'm sorry, but i don't know what do you mean when you say foreign key.
The sql query looks ok, but still is not working for me.
Could someone pls. tell me how do i make the output for the domain name, and for the appropriate number of books in each domain.
thx
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

Think Pink wrote:i'm sorry, but i don't know what do you mean when you say foreign key.
Maybe off topic but...

You have two tables. Both relate to each other. Both have a primary key. Now you need a foriegn key surely to get the books for that domain.

Foreign key = a key which identifies a record in another table, typically the other table's primary key.

So lets say I have two tables, one is names and dates of births, the other is favourite colors.

people
person_id
color_id (our foreign key)
first_name
last_name
dob

colors
color_id
color_name

To get the favourite color of "John Smith" who's ID is 54:

Code: Select all

SELECT color_name FROM people, colors WHERE people.color_id=colors.color_id AND people.person_id='54'
:wink:

PHENOM| Queries should be in

Code: Select all

tags [/color][/size]
User avatar
Think Pink
Forum Contributor
Posts: 106
Joined: Mon Aug 02, 2004 3:29 pm

Post by Think Pink »

ok i got it.

tbl_domains
id_domain
domain

tbl_books
id_book
book_name
code_domain (foreign key in this case)
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

In which case the book count should be (where $domain_id is the primary key from tbl_domains):

Code: Select all

&quote;SELECT COUNT(tbl_books.code_domain) AS total_books FROM tbl_domains AS d, tbl_books AS b WHERE d.id_domain=b.code_domain AND d.id_domain='$domain_id'&quote;
User avatar
Pyrite
Forum Regular
Posts: 769
Joined: Tue Sep 23, 2003 11:07 pm
Location: The Republic of Texas
Contact:

Post by Pyrite »

I would use a GROUP BY with an INNER JOIN. Like this?

Code: Select all

SELECT
	domain,
	count(*) AS 'Number of Books'
FROM
	tbl_domains AS d
	INNER JOIN tbl_books AS b ON b.code_domain = d.id_domain
GROUP BY
	domain
This will output like:

Code: Select all

domain : Number of Books
-------:----------------
yahoo    37
google   29
amazon   6
User avatar
Think Pink
Forum Contributor
Posts: 106
Joined: Mon Aug 02, 2004 3:29 pm

Post by Think Pink »

ok, seems like now is working.
thx for having the patience to help me.
Post Reply