[SOLVED] Table Display data from 2 tables help!

[phpbeginner]

As you can see from my horrendous looking code that I am trying to display data from 2 tables and in a table. The problem I am having is that the code from the 2nd table is displaying in one column under the intended column in the table.

 

Any help would be appreciated.

 

<div class="header_02">Meeting Agendas & Minutes</div>
<table width="100%" cellpadding="2" cellspacing="0" border="1" bordercolor="#496DA6"><tr><td><b>Meeting Name</b></td><td><b>Date</b></td><td><b>Time</b></td><td><b>Location</b></td><td><b>Agenda</b></td><td><b>Minutes</b></td></tr>
<?php
$rs=mysql_query("select * from tblminutes ORDER BY date DESC");                       
while($row=mysql_fetch_row($rs)){
$date = $row[3]; 
$row[3] = strtotime($date); 
$row[3] = date('M. d/y', $row[3]);
foreach ($row as $k => $v){
$row[$k] = nl2br($v);

}
                            
                              echo("<tr><td>" . $row[1] . "</td>");      
                              echo("<td>" . $row[3] . "</td>");
                              echo("<td>" . $row[2] . "</td>");
                              echo("<td>" . $row[4] . "</td>");
                              if($row[5]!="")
			      {
                              echo("<td><a href='minutes/" . $row[5] . "' target='_new'>View</a></td>");
                                      }
                              else
                                      {
                              echo("<td>-</td>");
                                      }
                           }
                              ?>
<?php
$rs=mysql_query("select * from tblagenda JOIN tblminutes on tblagenda.mid=tblminutes.id");                        
while($row=mysql_fetch_row($rs)){
foreach ($row as $k => $v){
$row[$k] = nl2br($v);

}
                            
                              if($row[1]!="")
                                    {
                              echo("<td><a href='agenda/" . $row[2] . "' target='_new'>View</a></td></tr>");    
                                    }
                              else
                                      {
                              echo("<td>-</td></tr>");
                                      }
                              }                     
						  
                              ?>
  	
</table>

[phpbeginner]

waaaa!!! Anyone??

[phpbeginner]

I've tried everything and still have same results. Any help would be much appreciated!

[phpbeginner]

bump

[Mark Baker]

Why not reduce it to a single query rather than inefficient nested queries...

SELECT t.*,a.*
  FROM tblagenda a, 
       tblminutes t 
WHERE a.mid=t.id
ORDER BY t.date DESC

[kickstart]

Hi

 

Agree with the above if you are doing .

 

The problem you have with your current code is that your first loop doesn't appear to throw out any close table rows (ie, no </tr>), while the 2nd loop doesn't throw out any open rows. You also process all the first SELECT then all the 2nd SELECT.

 

Can't really give you more details as I have no idea what the columns are in the 2 tables, nor whether there are possibly multiple adgenda rows for each minutes, or the other way round.

 

All the best

 

Keith

[phpbeginner]

There is only 1 agenda and or minutes per meeting......there is  date, time, location info. If they have the agenda posted then they can view the agenda. Down the road, when they have minutes then they would be posted in the minutes column for that row (meeting time, location, agenda). 

[kickstart]

Hi

 

Do you have a table of meetings as well?

 

Using a single query would need a full outer join, something mysql doesn't properly support.

 

All the best

 

Keith

[phpbeginner]

There are 2 tables.....

 

1: Includes : id (auto-increment) tblmeetingname, tbllocation, tbldate, uploadedfile (for agendas).

 

This works fine. Table 2 is for posting minutes at later date.

 

2: Includes: tblmid, uploadedfile

 

When entering this data, they select meeting name from the list and then upload the minutes. This adds the auto-increment id from table1 to tblmid and then a file is uploaded.

[phpbeginner]

To add to this......my table has 6 columns and contains a row for each specific meeting by date.

 

So would have

 

meeting name, date, time, location, agenda, minutes.

 

Everything works fine until we get to minutes .....and if there were 10 rows in the list, the minutes from table 2 are displaying in the last row in the table and the remaining minutes are just seperate rows under the column.

 

I need to associate any minutes that may be posted from table2 (mid) to the appropriate row (id) and have them display in the last column of that row.

[kickstart]

Hi

 

Quick hack around and something like this should do it:-

 

<div class="header_02">Meeting Agendas & Minutes</div>
<table width="100%" cellpadding="2" cellspacing="0" border="1" bordercolor="#496DA6"><tr><td><b>Meeting Name</b></td><td><b>Date</b></td><td><b>Time</b></td><td><b>Location</b></td><td><b>Agenda</b></td><td><b>Minutes</b></td></tr>
<?php
$rs=mysql_query("select a.id, a.tblmeetingname, a.tbllocation, a.tbldate, a.uploadedfile AS Minutes, b.tblmid, b.uploadedfile AS Agenda from tblminutes a LEFT OUTER JOIN tblagenda b ON a.id = b.mid ORDER BY date DESC");                       
while($row=mysql_fetch_row($rs))
{
$date = $row['tbldate']; 
$row['tbldate'] = strtotime($date); 
$row['tbldate'] = date('M. d/y', $row['tbldate']);
foreach ($row as $k => $v)
{
	$row[$k] = nl2br($v);
}
echo("<tr><td>" . $row['id'] . "</td>");      
echo("<td>" . $row['tbllocation'] . "</td>");
echo("<td>" . $row['tblmeetingname'] . "</td>");
echo("<td>" . $row['tbldate'] . "</td>");
if($row['Minutes']!="")
{
	echo("<td><a href='minutes/" . $row['Minutes'] . "' target='_new'>View</a></td>");
}
else
{
	echo("<td>-</td>");
}
if($row['tblmid']!="")
{
	echo("<td><a href='agenda/" . $row['Agenda'] . "' target='_new'>View</a></td></tr>");    
}
else
{
	echo("<td>-</td></tr>");
}
}
?>
</table>

 

Note that you could simplify it by moving the date manipulation into the SQL.

 

All the best

 

Keith

[phpbeginner]

Hi Keith,

 

Thanks so much for the help. I tried the code but could not get it to display anything so made a few changes. I can get all to display now except any agendas or meetings (revamped code below).......I think it is getting close and I appreciate all your help.

 

<div class="header_02">Meeting Agendas & Minutes</div>
<table width="100%" cellpadding="2" cellspacing="0" border="1" bordercolor="#496DA6"><tr><td><b>Meeting Name</b></td><td><b>Date</b></td><td><b>Time</b></td><td><b>Location</b></td><td><b>Agenda</b></td><td><b>Minutes</b></td></tr>
<?php
$rs=mysql_query("select a.id, a.title, a.time, a.date, a.location, a.uploadedfile AS Minutes, b.mid, b.uploadedfile AS Agenda from tblminutes a LEFT OUTER JOIN tblagenda b ON a.id = b.mid ORDER BY date DESC");                       
while($row=mysql_fetch_row($rs))
{
$date = $row[3]; 
$row[3] = strtotime($date); 
$row[3] = date('M. d/y', $row[3]);
foreach ($row as $k => $v)
        {
$row[$k] = nl2br($v);	
}

echo("<tr><td>" . $row[1] . "</td>");      
echo("<td>" . $row[3] . "</td>");
echo("<td>" . $row[4] . "</td>");
echo("<td>" . $row[2] . "</td>");
if($row['Minutes']!="")
{
	echo("<td><a href='minutes/" . $row['Minutes'] . "' target='_new'>View</a></td>");
}
else
{
	echo("<td>-</td>");
}
if($row['tblmid']!="")
{
	echo("<td><a href='agenda/" . $row['Agenda'] . "' target='_new'>View</a></td></tr>");    
}
else
{
	echo("<td>-</td></tr>");
}
}
?>
</table>          

[kickstart]

Hi

 

I would say you are best off using column names rather than numbers when retrieving values.

 

However to do that you need to use mysql_fetch_assoc instead of mysql_fetch_row (sorry). If you take my original code and do that minor change and give that a try.

 

All the best

 

Keith

[phpbeginner]

Damn! I always forget to change that! Will let you know.

[phpbeginner]

Keith, works like a charm!!! I appreciate all of your help and patience!!