Displaying Multiple File Attachments

[thedeal56]

I have a table named 'attachment' that looks like this:

 

attachment:

 

RecNum.....RelatedRecNum.....FileName

1......................500.................test.jpg

2......................500.................test.pdf

 

I have a table named project that looks like this:

 

project:

 

RecNum.....ProjectName

500..........Sample_Project

 

This is the query I'm using to join the tables together:

$result = "select project.ProjectName as 'Name', attachment.FileName as 'Attachment' from project left join attachment on project.RecNum=attachment.RelatedRecNum where attachment.RelatedRecNum=500";
$row=mysql_query($result) or die(mysql_error());

$array = mysql_fetch_array($row);

 

I'm using this to display the attachment result:

<?php 
while ($array = mysql_fetch_array($row))
{ 
echo $array['Attachment'];
} 
php?>

 

I want the two (or however many) attachments to show up with the associated project, but the above code will only post 1 attachment result per project.  Can anyone help? Let me know if I need to explain myself better. Thanks

[fenway]

Sounds like a job for group_concat().

[thedeal56]

Thanks for your reply.  I've been looking into group_concat, but I can't find a way to make it apply to my query.  If I take this out of the original query, the while loop will return both attachments:

 

$result = "select project.ProjectName as 'Name', attachment.FileName as 'Attachment' from project left join attachment on project.RecNum=attachment.RelatedRecNum where attachment.RelatedRecNum=500";

$row=mysql_query($result) or die(mysql_error());

 

$array = mysql_fetch_array($row);

 

However,  if I take that out, I have no way of displaying the name of the project.  I can't add the project name to the loop, because I only want it to display once.  Thanks 

[thedeal56]

I was able to fix it by splitting the single query into two queries.

[aschk]

Alternatively use the 1 query you had to begin with, put all the mysql data into an array and use a static index to get the project name.

e.g.

<?php
$sql = "
SELECT p.ProjectName as 'Name'
      ,a.FileName as 'Attachment'
FROM project p
LEFT JOIN attachment a on p.RecNum = a.RelatedRecNum 
WHERE a.RelatedRecNum=500
";

$query=mysql_query($sql) or die(mysql_error());

// Create dataset.
while ($row = mysql_fetch_assoc($query))
{ 
  $data[] = $row;
}

// Output project name.
echo $data[0]['Name'];

// Output all data.
foreach($data as $d){
  echo $d['Attachment'];
}
?>

[thedeal56]

Thanks a lot for the help, guys. My example that I posted is a very generic one.  I thought that if I learned the methodology of the code through the example, I would then be able to apply it to my real code.  It looks like I'm too dumb to do that  .  The code that I'm about to post is generated by a program, and I'm trying to figure out how to assign the attachment.FileName field to the variable that is being used. Unfortunately, I'm not sure what all I need to post in order or you to see what I need to do.  I'm going to post what I think is the necessary information, and maybe we can go from there.  Again, thanks a lot for looking at this; it has been driving me crazy for a while. 

 

This is the code from the file (dml.php) that is inserting the sql data into a variable that will be used later. (It's still on the two query system, I'm hoping to learn how to use the array example today)

if($selected_id)
{
$result = "SELECT attachment.FileName as 'Attachment' FROM project LEFT JOIN attachment on project.RecNum=attachment.RelatedRecNum WHERE project.RecNum='$selected_id'";

$queryb = "SELECT project.ProjectName as 'RecNum' FROM project LEFT JOIN attachment on 	project.RecNum=attachment.RelatedRecNum WHERE project.RecNum='$selected_id'";

	 $row=mysql_query($result) or die(mysql_error());
	 $rowb=mysql_query($queryb) or die(mysql_error());
	 $arrayb = mysql_fetch_array($rowb);


}

The "$selected_id" string value comes from a record that is clicked by the user.  After the record is clicked, the record ID number is used to open a table containing more information on that record.  It is in that table that I'm trying to put my multiple attachment info.

 

In the same file (dml.php), Here is where I think the variable is being assigned:

// process values
if($selected_id){
	$templateCode=str_replace('<%%VALUE(RecNum)%%>', htmlspecialchars($arrayb['RecNum'], ENT_QUOTES), $templateCode);
	$templateCode=str_replace('<%%VALUE(Attachment)%%>', htmlspecialchars(($????['Attachment'], ENT_QUOTES), $templateCode);
}else{
	$templateCode=str_replace('<%%VALUE(RecNum)%%>', '', $templateCode);
	$templateCode=str_replace('<%%VALUE(Attachment)%%>', '', $templateCode);
}

// process translations
foreach($Translation as $symbol=>$trans){
	$templateCode=str_replace("<%%TRANSLATION($symbol)%%>", $trans, $templateCode);
}

 

Finally, this is the file (detail_view.html) that is using the information inserted into <%%VALUE(Attachment)%%> and <%%VALUE(RecNum)%%>

 

 
<br>
<table border=1 bordercolor=navy cellpadding=0 cellspacing=0>

<tr>
	<td>
		<table>
			<tr>
				<td colspan=2></td>
				<td rowspan=6 valign=top>
					<div><%%UPDATE_BUTTON%%></div>
					<div><%%DELETE_BUTTON%%></div>
					<div><%%DESELECT_BUTTON%%></div>
					<br>
					<div><%%INSERT_BUTTON%%></div>
					</td>
				</tr>
			<tr>
				<td class=TableHeader valign=top>
					<div class=TableHeader style="text-align:right;">Address</div>
					</td>
				<td class=TableBody width=300><input size=30 type=text class=TextBox name=RecNum value="<%%VALUE(RecNum)%%>">  </td>
				</tr>
			<tr>
				<td class=TableHeader valign=top>
					<div class=TableHeader style="text-align:right;">Attachment</div>
					</td>
				<td class=TableBody width=300><a href="<%%TRANSLATION(Link)%%><%%VALUE(Attachment)%%>" target=mainFrame><%%VALUE(Attachment)%%></a>  </td>
				</tr>
			</table>
		</td>
	</tr>
</table>

 

I could be way off base on what I need to post in order for it to make sense, so please let me know if I need to provide more information.  Thanks for checking this out, I know it's probably a pain to sort this stuff out.

[thedeal56]

I tried putting the while loop here:

// process values
if($selected_id){
	$templateCode=str_replace('<%%VALUE(RecNum)%%>', htmlspecialchars($arrayb['RecNum'], ENT_QUOTES), $templateCode);
WHILE ($array = mysql_fetch_array($row)){
	$templateCode=str_replace('<%%VALUE(Attachment)%%>', htmlspecialchars($array['Attachment']), $templateCode);}
}else{

	$templateCode=str_replace('<%%VALUE(RecNum)%%>', '', $templateCode);
	$templateCode=str_replace('<%%VALUE(Attachment)%%>', '', $templateCode);
}

// process translations

foreach($Translation as $symbol=>$trans){
	$templateCode=str_replace("<%%TRANSLATION($symbol)%%>", $trans, $templateCode);
}

 

it still only displays one attachment result.  It seems like putting the while loop there should work.  What am I missing? Thanks.

[thedeal56]

Well, I'm done messing with it.  Thanks for your help, guys.