Display specific field in non-linear format

[enderhegemon]

Hi,

I noticed similar questions, but I'm having trouble figuring out a solution for my specific problem.

Basically, I have a film database. It contains the usual information: director, writer, cast, etc.

There is an information box on the page that shows the following: director, writer, cast, and director of photography. The layout of the table is similar to the following (the writer and director are assumed to be the same person):
--------------------------------------
Writer & Director

*name of writer & director*

Cast

*name of character* - *name of person*

Director of Photography

*name of director of photography*
--------------------------------------

I understand that I can use "do...while", but that doesn't work because my information isn't in sequence. Also, the script needs to handle future films that have the same people who do different roles.

Thanks for the help.

 

[jpadie]

not sure what your question is.

do you want help outputting your data in the form you show above or in some other format (if so which)?

please expand on what you mean by:

doesn't work because my information isn't in sequence

Also, the script needs to handle future films that have the same people who do different roles.

i would guess this is a function of your database structure but i may have misunderstood your intentions.

 

[enderhegemon]

Let me try to clarify the situation.

The format is a table with the same strcture as seen above.

In the statement, "not in sequence" I meant that they way I'm displaying my data isn't in the same format as one would get when doing a do...while loop.

If I create a do...while loop I get:

*Writer*
*Name*
*Director*
*Name*
*Cast*
*Name*
*Cast*
*Name*
*Director of Photography*
*Name*

All the information is pulled from the database, jobs are in the job table and the people's names are from the person table.

I'd like to have the information displayed as the table above shows in my original post. Also, I realized that the term Cast will be displayed for every person who is a cast member. The term should only be displayed once.

The layout is my main concern, any other questions in my original post can be ignored.

Apologies for being unclear.

Thanks.

 

[jpadie]

i'm still not sure i get you, but here goes a guess at what you might mean...

$sql = "Select * from table where filmid = '1'"; //probably some join instead
$result = mysql_query($sql);
$row=mysql_fetch_assoc($result)):
//now just echo them out as follows
?>
<tr><td>Writer</td><td><?=$row['writer']?></td></tr> 
//etc in whatever order you want.

if your question is not answered by the above, please try rearticulating in a different way. provide us with your table structure and the code you are using to generate the output. explain what is wrong with the current output and give an example (perhaps with data) of what the output is that you want. post links to a web page if that is easier.

 

[enderhegemon]

Okay here goes...thanks for being so patient.

I have a database with the following tables:
film, person, job, etc. (other tables aren't necessary here)

In the person table the fields are:
person_id person_first person_last
1 Homer Simpson
2 Marge Simpson
3 Bart Simpson
4 Lisa Simpson

In the job table the fields are:
job_id job_type
1 Writer
2 Director
3 Cast
4 Director of Photography

in an association table, pja, (because of many-to-many relationship):
film_id job_id person_id
1 1 1
1 2 1
1 3 2
1 3 3
1 4 4

When a person views a film specific information about the film is displayed, e.g. director, stock, medium, cast, etc. This information is displayed in a table:

<table width=100%>
<tr>
	<td>Writer &amp; Director<p></p></td>
	<td class="right">Medium<p><?php echo $row_Recordset1['medium_name']; ?></p></td>
</tr>
<tr>
	<td>Cast<p>&nbsp;</p></td>
	<td class="right">Stock<p><?php echo $row_Recordset1['stock_name']; ?></p></td>
</tr>
<tr>
	<td>Director of Photography<p>&nbsp;</p></td>
	<td class="download">Download Movie<p><a href="/assets/films/<?php echo $row_Recordset1['file_mpeg']; ?>">QuickTime - MPEG4</a><br /><a href="<?php echo $row_Recordset1['file_h264']; ?>">QuickTime - H264</a><br /><a href="<?php echo $row_Recordset1['file_divx']; ?>">DivX</a><p></td>
</tr>
</table>

The output looks like:

Writer & Director Medium
16mm Kodak B&W


Cast Stock
Double-X Negative


Director of Photography Download Film
QuickTime - MPEG4
QuickTime - H264
DivX

Where there are blanks under (Writer & Director, Cast, and Director of Photography) is where I'm having trouble.

Please let me know if more information would help...

Thanks

 

[jpadie]

and i guess the issue is that there may be more than one person for each role.

i assume you are already working with multiple recordsets as there will be a single result for the medium etc. so there are two plausible solutions:

1. do a different query for each bit of information you want to retrieve. ie for writer and director do the query that pulls the name when the role id 1 OR 2 and filmid is whatever. then echo each result row out in the relevant place with a basic loop

2. on the assumption that your query looks something like the below, try the code below for dumping the recordset into neat array elements that can be reused at the right points.

<?
//connect to db server
//select db

$sql = "
SELECT 
		films.film as film, 
		roles.role as role, 
		persons.firstname as firstname, 
		persons.lastname as lastname
FROM 
		((films 
		INNER JOIN 
			fpr ON films.filmid = fpr.filmid) 
		INNER JOIN 
			persons ON fpr.personid = persons.personid) 
		INNER JOIN roles ON fpr.roleid = roles.roleid
ORDER BY films.film, roles.role;";

$newarray=array();
$results = mysql_query($sql) or die(mysql_error());
while ($row= mysql_fetch_assoc($results)):
	$newarray[$row['role']]= 
		(isset($newarray[$row['role']]))
		? $newarray[$row['role']] . "<p>{$row['lastname']}, {$row['firstname']}</p>"
		: "<p>{$row['lastname']}, {$row['firstname']}</p>";
endwhile;
?>

<table width=100%>
<tr>
    <td>Writer &amp; Director
	<?=$newarray["Writer"]?>
	<?=$newarray['Director']?>
	</td>
    <td class="right">
		Medium
		<p><?php echo $row_Recordset1['medium_name']; ?>
		</p>
	</td>
</tr>
<tr>
    <td>
		Cast
		<?=$newarray['Cast']?>
	</td>
    <td class="right">
		Stock
		<p><?php echo $row_Recordset1['stock_name']; ?></p>
	</td>
</tr>
<tr>
    <td>
		Director of Photography
		<?=$newarray['Director of Photography']?>
	</td>
    <td class="download">Download Movie<p><a href="/assets/films/<?php echo $row_Recordset1['file_mpeg']; ?>">QuickTime - MPEG4</a><br /><a href="<?php echo $row_Recordset1['file_h264']; ?>">QuickTime - H264</a><br /><a href="<?php echo $row_Recordset1['file_divx']; ?>">DivX</a><p></td>
</tr>
</table>