Re: Counting Number of Instances in a One2Many Relationships

Hi Paul (both of you), Thanks for your suggestions: Paul DuBois wrote: SELECT publisherID, COUNT(*) FROM WLPpublisher GROUP BY publisherID Paul Burney wrote: SELECT COUNT(bibID) FROM bookDB WHERE publisherID='$your_publisher_id'; And the final code that did the trick was actually a combination of the two: SELECT publisherID,COUNT(bibID) AS bibCount FROM WLPbib GROUP BY publisherID Thank you both for your suggestions. Not sure that either one would have allowed me to get the results I wanted. Mike Paul DuBois wrote:

Hello All, I'd like a simple query to determine how MANY books in a database are released by A publisher. This shouldn't be a big deal, but I've looked at a number of solutions and am coming up short. MySQL is being accessed via phplib, but I'm sure that it's just how I'm expressing the general MySQL query. The bookID & publisherID are both listed in the bookDB. I just want a query which will go through the database and return the publisherID and a count of the number of books. I'd like to end up with an array that expresses $publisherID => Number of books so that I can tap this later...

How about: SELECT publisherID, COUNT(*) FROM WLPpublisher GROUP BY publisherID

I first tried to approach it by doing this which fell because of @@IDENTITY $q2 = "SELECT publisherID FROM WLPpublisher"; $q2 = "SELECT @@IDENTITY AS publisherID FROM WLPpublisher"; echo $q2 . "<br>"; $lastPublisherID = $this->db->query($q2); $q3 = "SELECT bibID,publisherID FROM WLPbib WHERE publisherID='$i'"; echo $q3 . "<br>"; for ($i=0; ($i < $lastPublisherID); $i++) {

    $pub_count_ary[$i] = $this->db->num_rows($q3);

} I next tried to use a query like this, but couldn't figure out how to express the relationship between the two: $q2 = "SELECT bibID,publisherID,COUNT(*)

    FROM WLPbib
    GROUP BY publisherID";

$this->db->query($q2); while ($this->db->next_record()) { $pub_count_ary[$i] = $this->db->Record[publisherID]; $i++; } Finally, I tried this which didn't work either: $q2 = "SELECT bibID,publisherID FROM WLPbib"; $this->db->query($q2); $i = 0; while ($this->db->next_record()) {

    $pub_count_ary[$i] = $this->db->Record[publisherID];
    $i++;

} The relevant table's here: CREATE TABLE WLPbib (

    bibID mediumint(9) NOT NULL,
    languageID varchar(5),
    publisherID mediumint(9),
    categoryID smallint(6),
    type varchar(55),
    title varchar(255),
    pageNumber varchar(55),
    source_bibID varchar(55),
    publicationDate varchar(5),
    dateAdded date,
    publishedLanguage varchar(5),
    URL varchar(100),
    status varchar(5),
    PRIMARY KEY (bibID)

); Suggestions are appreciated! Mike -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net For additional commands, e-mail: php-db-help@lists.php.net To contact the list administrators, e-mail: php-list-admin@lists.php.net

-- Mike Gifford, OpenConcept Consulting, http://openconcept.ca Offering everything your organization needs for an effective web site. Abolish Nuclear Weapons Now!: http://pgs.ca/petition/ It is a miracle that curiosity survives formal education. - A Einstein