Re: One2Many Logic Problem - phpMySQL bibliography

Hello Steve, Thanks for the response. I tried to reply to this earlier, but seems like Mozilla ate it. :( Steve Brett wrote:

wouldn't the structure of the database be determined by the fact you have a many to many relationship ? i.e. the two tables would decompose into three with a 'link' table defining the many to may part ?

Sorry if I wasn't clear about this. You are right there is a linking table to manage the relationship. I can create a query which will find duplicate author's for each book listed in this linking table. However, I don't know how to effectively use this data to affect how information is extracted from the database in the second query. CREATE TABLE WLPprofile ( profileID mediumint(9) NOT NULL, languageID varchar(5), addressID mediumint(9), firstName varchar(255), middleName varchar(255), lastName varchar(255), organization varchar(255), nationality varchar(255), professionID smallint(3), bio text, status varchar(5), PRIMARY KEY (profileID) ); 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) ); CREATE TABLE WLPbib2profile ( bibID mediumint(9), profileID mediumint(9) ); Thanks. Mike

"Mike Gifford" <mike@openconcept.ca> wrote in message news:3B5B6615.8090801@openconcept.ca...

Hello, I'm not sure if anyone else as the need for a php/MySQL based

bibliography, but

the application that I am developing (which is basically a module for

phpSlash)

is coming along fairly well. If anyone is interested in seeing the code I

can

bundle it up and send it out to them (still a long way from finished

mind). The

draft version of it is up and running here: http://openconcept.ca/WLP/biblio/index.php3 I'm running into a couple problems though that I don't have the logic

language

for and I was hoping folks here could help me (as you have done in the

past).

The problem is that I've set up a many to many table to ensure that I can

have

an author be listed as writing many books and a book be written by many

authors.

However in listing the books as a bibliography (as the URL above), I want

to be

able to list multiple authors, and do so in a different format for the

first

author than for all subsequent ones. I'm hoping to be able to produce output like this: Young, D. and N. Dixon. Helping Leaders Take Effective Action: A Program Evaluation. Greensboro, North Carolina: Center for Creative Leadership, 1996. So, I think the first challenge should be to calculate which books have

multiple

authors by doing something like this (It's using phplib structures,

sorry):

$q2 = "SELECT * FROM WLPbib2profile ORDER BY bibID"; $this->db->query($q2); // Performs above query while ($this->db->next_record()) { if ($this->db->Record["bibID"] != $last_bibID) { echo "<br>" . $this->db->Record["bibID"] . " : " .

$this->db->Record["profileID"];

} else { // Multiple Authors echo ", " . $this->db->Record["profileID"]; $multiple_profileID .= array ($this->db->Record["bibID"] => $this->db->Record["profileID"]); $multiple_profileID .= array($this->db->Record["bibID"] => "yes"); } $last_bibID = $this->db->Record["bibID"]; } } With this I can then use the value $multiple_profileID to determine if

this

record has duplicates or not and use something like this to include both

authors

and not repeat the bibliography item: if ($multiple_profileID[$bibID]=="yes") { while (list($key, $val) = each($multiple_profileID)) { $written_by = $key . " : " . $val . "<br>"; if ($this->db->Record["firstName"]) { $written_by .= stripslashes($this->db->Record["firstName"]) . " "; } if($this->db->Record["lastName"]) { $written_by .= stripslashes($this->db->Record["lastName"]); } } } else { if($this->db->Record["lastName"]) { $written_by .= stripslashes($this->db->Record["lastName"]) . ", "; } if ($this->db->Record["firstName"]) { $written_by .= stripslashes($this->db->Record["firstName"]); } } But this seems really awkward (at the best of times) and (worst of all) it

isn't

working. I've got another related problem in that I've set up the following fields

in one

table: firstName lastName organization and I need to be able to order them by a combination of lastName &

organization.

I could list the organization field simply as the lastName, but that

seems

like it would be confusing the data types... Must be a way around this..

I

suspect it is limited by my logic & not PHP's. Any ideas would be appreciated. Mike -- 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

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