Field data repetition when combining tables

ali_p

I have a query that joins 5 tables to pull out the relevent results.


SELECT *
FROM publications, publicationsXpeople as pxp, people, publicationsXprojects as xpx, projects

WHERE publications.publication_code = pxp.publication_code

AND pxp.people_code = people.people_code

AND publications.publication_code = xpx.publication_code 

AND xpx.project_code = projects.project_code

AND publications.publication_code = 7075

My problem is that whatever the number of results in one table , say the people table , the result from the projects table is duplicated to match.

So if i have 1 project that get pulled from the query and 3 people that get pulled from the query them my 3 people will be listed and the one project will be listed 3 times.

I'm finding this rather frustrating as the query pulls out the correct data its just i'm stuck with how it is displayed.

Could anyone shed some light on this?...

Here is the php that i am using to display the results if this is of any help.



while ($row =mysql_fetch_array($result)){
	$publication_code = $row['publication_code'];
	$publication_category=$row['publication_category'];
	$publication_number=$row['publication_number'];
	$publication_details=$row['publication_details'];
	$publication_abstract=$row['publication_abstract'];
	$publication_topic = $row['publication_topic'];
	$publication_date_elements = explode("-",$row['publication_date']);
	$sql_publication_date = $publication_date_elements[2]."-".$publication_date_elements[1]."-".$publication_date_elements[0];
	$file_name=$row['file_name'];

$publication_url=$row['publication_url'];
$status=$row['status'];
//pertaining to people table
	$publicationsXpeople_id=$row['publicationsXpeople_id'];
	$people_code=$row['people_code'];
	$pfirst_name=$row['first_name'];
	$plast_name=$row['last_name'];
//pertaining tp projects table
	$publicationXproject_id=$row['publicationXproject_id'];
	$project_code=$row['project_code'];
	$acronym=$row['acronym'];

$display_block .= "<table><tr><td>$plast_name $pfirst_name</td><tr></table>";

$display_block2 .= "<table><tr><td>$acronym</td><tr></table>";
}	
}

xblue

Hi,

first a few general things: outside functions, I would carefully avoid creating huge number of variables if possible - the risk of accidently overwriting something or adding to something that is not what you believe it to be at that moment.

If you want to keep with it, extract() may be of halp to slimline your code - use mysql_fetch_assoc() instead of mysql_fetch_array() to be on the safe side.

The way I'd do it is simply use the variables as they are, like

$display_block .= "<table><tr><td>".$row['plast_name']." ".$row['pfirst_name']."</td><tr></table>";

(BTW, do you actually mean to create separate tables each time instead of new rows in the same table?)

Mysql's DATE_FORMAT function may also be more convenient for converting.

Now, back to problem: do I get it right that data is displayed similar to
Person 1 - Project xy
Person 2 - Project xy
Person 3 - Project xy
Person n - Project abc

while you'd like it to be
Person 1 - Project xy
Person 2
Person 3
Person n - Project abc
i.e. do not repeat the project unless it is new?

ali_p

Thanks for your helpful comments xblue. What i would like to see displayed is , if person x, person y and person z are associated with a publication then display them as;

people
person x
person y
person z

if project m is associated with the publication then display it as;

projects
project m

Currently this is what would be displayed in this scenario;

people
person x
person y
person z

projects
project m
project m
project m

As you can see the projects are duplicated to match the number of people associated with the publication. I have experimented with different ORDER BY , but without any success.

xblue

If I understand it correctly now (no guarantee, I'm getting tired), it seems like something to handle in the output.

So, in your loop, do only display the acronym if it is different from the last acronym, then store the current acronym in a variable to compare the next row with. I hope the picture is getting clear.

ali_p

Many thanks for your reply.

I understand what i need to do, but i am a little uncertain as to how to go about coding this