Page 1 of 1

counting records

Posted: Mon Mar 28, 2005 8:48 am
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.

Posted: Mon Mar 28, 2005 8:51 am
by Chris Corbyn
Select all the books then use mysql_num_rows() to see how many you have :wink:

Posted: Mon Mar 28, 2005 8:53 am
by John Cartwright
or!

SELECT COUNT(*) AS `total` FROM ...

Posted: Mon Mar 28, 2005 9:04 am
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.

Posted: Mon Mar 28, 2005 9:17 am
by John Cartwright
not quite sure, but try GROUP BY `domain`

Posted: Mon Mar 28, 2005 9:24 am
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;

Posted: Mon Mar 28, 2005 1:11 pm
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

Posted: Mon Mar 28, 2005 4:07 pm
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]

Posted: Mon Mar 28, 2005 4:32 pm
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)

Posted: Mon Mar 28, 2005 4:46 pm
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;

Posted: Mon Mar 28, 2005 5:05 pm
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

Posted: Mon Mar 28, 2005 5:09 pm
by Think Pink
ok, seems like now is working.
thx for having the patience to help me.