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" <[EMAIL PROTECTED]> wrote in message
> [EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
>
>> 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
--
PHP General 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]