Help displaying results from joined tables

[ali_p]

Hi,

I am cuurently being a site that lists publications and their authors. My problem is displaying the results of my databases.

I have 3 tables publications, people and peoplexpublications

Publications - contains all the info about the publication, title year etc and publication_code

People - contains a list of people and people_code

peoplexpublications - contains the publication_code and people_code when a person is associated with a publication, this table can contain one publication_code to many people_codes as more than one person can be a part of a publication.

My problem is when i try to display all this information from my 3 tables.
here is the code

$sql = "
SELECT *
FROM publications, peoplexpublications as pxp, people
WHERE publications.publication_status = 'live'
AND publications.publication_code = pxp.publication_code
AND pxp.people_code = people.people_code
GROUP BY pxp.publication_code
ORDER BY publications.publication_year DESC
";
$result = mysql_query($sql, $conn)or die(mysql_error());

// Set variables from the query for use in the html
while ($row =mysql_fetch_array($result)){
	$publication_code = $row['publication_code'];
	$publication_type=$row['publication_type'];
	$publication_summary=$row['publication_summary'];
	$publication_title=$row['publication_title'];
	$publication_year=$row['publication_year'];
	$pkey = $row['pkey'];
	$publication_status=$row['publication_status'];
	//pertaining to people table
	$peoplexpublication_id=$row['peoplexpublication_id'];
	$people_code=$row['people_code'];
	$plast_name=$row['last_name'];
	$show_names .= "$plast_name<br>,";
	$display_block .= "<p>$show_names $publication_title,$publication_type,(<i>$publication_year</i>)</p>";
	}	

The problem is this is only bringing out one name per publication and i'm at a loss as to how to get all the names associted with the publication.

The '$display_block' is so that i can display my results like this:

name1,name2,name3,name4, Book Chapter, A Study into the Study of etc. 2006

Is there a particular table table join that i should be using or is it more of an Array issue.

All help appreciated.

[Houdini]

You could use either

while ($row =mysql_fetch_array($result,MYSQL_ASSOC)){

or

while ($row =mysql_fetch_assoc($result)){

[ali_p]

Hi Houdini,

Thanks for your suggestion.

Tried that however that seems to repeat the first name that it pulls out for all the names that follow. For example:

name1,name1,name1,name1, Book Chapter, A Study into the Study of etc. 2006

name1,name1, Book Chapter, Another book title here. 2006

[chuddyuk]

$result =
@("select *FROM publications, peoplexpublications as pxp, people
WHERE publications.publication_status = 'live'
AND publications.publication_code = pxp.publication_code
AND pxp.people_code = people.people_code
GROUP BY pxp.publication_code
ORDER BY publications.publication_year DESC
";");
while ($ex =
@MySQL_Fetch_Array($result)){

!!!

set variables

!!

try that way

[ali_p]

I'm sorry , you've lost me can you explain your code please.

Many thanks

[ali_p]

Hi again,

I tried your code chuddyuk but this is the result i got but the code is returning empty values. If you could explain what is happening i may get a better undrstanding as to how it works.

Thanks

[ali_p]

I've got me code to a stage where it will print the following result ,

De Souza, a publications teste,Peer Review,(2006)

Craig, a publications teste,Peer Review,(2006)

Murnaghan, a publications teste,Peer Review,(2006)

De Souza, zebra zebra,zebra,(2005)

Craig, zebra zebra,zebra,(2005)

De Souza, PostGrad Couses 2006,Book Chapters,(2005)

Craig, PostGrad Couses 2006,Book Chapters,(2005)

Gallagher, PostGrad Couses 2006,Book Chapters,(2005)

Murnaghan, PostGrad Couses 2006,Book Chapters,(2005)

Craig, Study of a woman on mars,Paper,(2005)

Regan, Study of a woman on mars,Paper,(2005)

Prenter, Study of a woman on mars,Paper,(2005)

Cassidy, Study of a woman on mars,Paper,(2005)

Murphy, Study of a woman on mars,Paper,(2005)

But as you can see its is repeating the titles for every author, wheras i need it to list the authors altogether with one title printed ofr it. ie:
Craig, Study of a woman on mars,Paper,(2005)

Regan, Prenter,Cassidy,Murphy, Study of a woman on mars,Paper,(2005)

Any ideas anyone.

Thanks

[chuddyuk]

hi ali please post wat code you have got so far

[ali_p]

Hi,

This is the code that gives me the above result:

$sql = "
SELECT *
FROM publications
WHERE publications.publication_status = 'live'

ORDER BY publications.publication_year DESC
";
$result = mysql_query($sql, $conn)or die(mysql_error());

// Set variables from the query for use in the html
while ($row =mysql_fetch_array($result,MYSQL_ASSOC)){
	$publication_code = $row['publication_code'];
	$publication_type=$row['publication_type'];
	$publication_summary=$row['publication_summary'];
	$publication_title=$row['publication_title'];
	$publication_year=$row['publication_year'];
	$pkey = $row['pkey'];
	$publication_status=$row['publication_status'];

$sql = "
SELECT *
FROM peoplexpublications as pxp, people
WHERE pxp.publication_code = $publication_code
AND pxp.people_code = people.people_code

";
$result2 = mysql_query($sql, $conn)or die(mysql_error());

while ($row2 =mysql_fetch_array($result2,MYSQL_ASSOC)){
$peoplexpublication_id=$row2['peoplexpublication_id'];
	$people_code=$row2['people_code'];
	$plast_name=$row2['last_name'];
	$display_block .= "<p>$plast_name, $publication_title,$publication_type,(<i>$publication_year</i>)</p>";

	}	

}

[chuddyuk]

im pretty new to all this so my advice could be rubbish to you, not too sure what you mean but could it be the way you have made your table that it is working like it is, i would also try displaying the data without your "display_block" and work the code out correctly then put it into you block. sorry i cant be naymore help like i said im a newb to this stuff

[ali_p]

No worries,
thanks for your time 🙂

I'll keep at it and will post up if i get a result.

[ahundiak]

Hi ali_p,

Your first try was pretty close but you may be expecting to much of the group by clause. The bottom line is that when you have relations you will always get one record per relation. That is
pub_a author_a1 Pub A has only one author

pub_b author_b1 Pub b has two authors
pub_b author_b2

Short of using some sort of stored procedure there is just no generic way to return a record like
pub_b author_b1 author_b2

You need to generate the author list in your php code by checking to see if a given record refers to the same publication as the record before.

Psuedo-code

$sql = SELECT * from PUB,AUTHOR where PUB_ID = AUTHOR_PUB_ID ORDER BY PUB_ID
$rows = mysql_query($sql);

$pub_id = NULL;
$pub_last_id = NULL;
while($row = mysql_fetch($rows)) {

  /* Pull out id for the current row */
  $pub_id = $row['pub_id'];

  /* Is it the same as the last row ? */
  if ($pub_id == $pub_last_id) {

/* Yes so we only save the new author */
$pub_authors[] = $row['author_id'];
  }
  else {
    /* Dealing with a new publication, first need to output the previous publication */
    if ($pub_id)  { /* Need to make sure not doing the very first record */
       echo $pub_id;
       foreach($pub_authors as $author) echo $author;
    }
    /* Pull out pub information */
    $pub_whatever = $row['whatever'];

/* Start new author array */
$pub_authors = NULL;
$pub_authors[] = $row['author_id'];

/* Reset the last id holder */
$pub_last_id = $pub_id;
}
/* Need to put out the very last publication */
if ($pub_id) {
    echo $pub_id etc.
}