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: [EMAIL PROTECTED]
>> For additional commands, e-mail: [EMAIL PROTECTED]
>> To contact the list administrators, e-mail: [EMAIL PROTECTED]
> 
> 
> 



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


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Reply via email to