Having a merged table field every x rows

Craig321 · 30 Mar 2009 at 21:13

Hi,

Bit of a rubbish thread title, but I'm really not sure how to explain it

Basically I'm making a log of all our BT speed tests (we've been having tons of problems, so we're keeping a log of down time, connection speeds, throughput and IP profile).

Anyway, I currently have this:

PHP:

<?php
include "includes/config.php";

$sql = "SELECT date, downstream, upstream, profile, throughput, notes FROM entries ORDER BY date DESC";
$result = mysql_query($sql);

echo "<table border=\"1\">";
echo "  <tr>";
echo "    <th align=\"left\">Date</td>";
echo "    <th align=\"left\">Down Stream</td>";
echo "    <th align=\"left\">Up Stream</td>";
echo "    <th align=\"left\">IP Profile</td>";
echo "    <th align=\"left\">Throughput</td>";
echo "    <th align=\"left\">Additional Notes</td>";
echo "  </tr>";
  
while($row = mysql_fetch_array($result))
{
	$date = $row['date'];
	$downstream = $row['downstream'];
	$upstream = $row['upstream'];
	$profile = $row['profile'];
	$throughput = $row['throughput'];
	$notes = $row['notes'];
	
	echo "  <tr>";
	echo "   <td>" . $date . "</td>";
	echo "    <td>" . $downstream . " Kbps</td>";
	echo "    <td>" . $upstream . " Kbps</td>";
	echo "    <td>" . $profile . " Kbps</td>";
	echo "    <td>" . $throughput . " Kbps</td>";
	echo "    <td>" . $notes . "</td>";
	echo "  </tr>";
}

echo "</table>";
echo "<br /><a href=\"new.php\">New Speed Entry</a>";
echo "<br /><a href=\"new_note.php\">New Note</a>";
?>

Now, basically I want to have another table which includes additional notes (notes that are separate from the speed logs), but these notes should be included within the same table in a merged field so I get something along the lines of the following:

So basically the query needs to SELECT from both the "entries" table and the "notes" table and include the results, in date order, within the table. All entries from the "notes" table should be in a merged field.

I just can't think how to do it.

[Edit]
The "Additional Notes" column I currently have is for adding a note with a speed entry.

Any ideas?
Thanks,
Craig.

Mullins · 30 Mar 2009 at 22:23

You need to have a common field between the tables, if there isn't one you need to create one e.g. an id number for each speedtest that you store in both tables, this will allow you to correlate the data.

Then you need to change your query to something along the lines of this:

Code:

SELECT entries.date, entries.downstream, entries.upstream, entries.profile, entries.throughput, entries.notes, notes.separate_notes 
FROM entries INNER JOIN notes
ON entries.speedtest_id = notes.speedtest_id
ORDER BY entries.date DESC;

Craig321 · 31 Mar 2009 at 00:05

OK, but once I've done that how do I make it so the output from the "notes" table is specifically put into a merged table field?

Craig321 · 31 Mar 2009 at 00:12

Actually, sorted:

PHP:

<?php
include "includes/config.php";

$sql = "SELECT date, downstream, upstream, profile, throughput, notes FROM entries ORDER BY date DESC";
$result = mysql_query($sql);

echo "<table border=\"1\">";
echo "  <tr>";
echo "    <th align=\"left\">Date</td>";
echo "    <th align=\"left\">Down Stream</td>";
echo "    <th align=\"left\">Up Stream</td>";
echo "    <th align=\"left\">IP Profile</td>";
echo "    <th align=\"left\">Throughput</td>";
echo "	  <th align=\"left\">Additional Notes</td>";
echo "  </tr>";
  
while($row = mysql_fetch_array($result))
{
	$date = $row['date'];
	$downstream = $row['downstream'];
	$upstream = $row['upstream'];
	$profile = $row['profile'];
	$throughput = $row['throughput'];
	$notes = $row['notes'];
	
	echo "  <tr>";
	if(empty($downstream)) {
		echo "    <td colspan=\"6\">" . $notes . "</td>";
	} else {
		echo "   <td>" . $date . "</td>";
		echo "    <td>" . $downstream . " Kbps</td>";
		echo "    <td>" . $upstream . " Kbps</td>";
		echo "    <td>" . $profile . " Kbps</td>";
		echo "    <td>" . $throughput . " Kbps</td>";
		echo "    <td>" . $notes . "</td>";
	}
	echo "  </tr>";
}

echo "</table>";
echo "<br /><a href=\"new.php\">New Speed Entry</a>";
echo "<br /><a href=\"new_note.php\">New Note</a>";
?>

Little bit of a botch job, but it works perfectly

Now for some formatting, it looks absolutely disgusting at the moment with the default table styles