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