Justtechjobs.com Find a programming school near you






Online Campus Both


php-db | 2001072

[PHP-DB] Counting Number of Instances in a One2Many Relationships From: Mike Gifford (mike <email protected>)
Date: 07/23/01

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 @ <email protected>
$q2 = "SELECT publisherID FROM WLPpublisher";
$q2 = "SELECT @ <email protected> 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 <email protected>
For additional commands, e-mail: php-db-help <email protected>
To contact the list administrators, e-mail: php-list-admin <email protected>