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]
