2 Attachment(s)
[RESOLVED] Format MySQL Output
Hi All,
It's my first time working with PHP and MySQL. So far, I've managed to get all of my scripts to work, however, i've run in to a bit of a bump in the road.
What i'm trying to do is to display a list of libraries and the services that the library has available in a table on to a PHP page. So far I have managed to achieve this and the output looks like Table1. See Attachment.
The problem is that I don't want the name of the library to repeat itself and I would like to display the services in a single cell rather than have them repeated with the name of the library. I'm aiming to have the webpage produce the following output like Table 2 (which I modified in paint). See Attachment.
Here is the PHP code that I am using to display the data in the table:
Code:
<div id="Content">
<h1>Results</h1>
<table cellspacing="4px" cellpadding="6px">
<tr>
<th align="left" bgcolor="E8F5FD">Location</th>
<th align="left" bgcolor="E8F5FD">Services</th>
</tr>
<?php
mysql_connect("localhost", "root", "password") or die(mysql_error());
mysql_select_db("libraryservices") or die(mysql_error());
$uLoc = $_POST['Location'];
$result = mysql_query("SELECT Name, ServName FROM (Library LEFT JOIN AvailableServices ON Library.LID = AvailableServices.LID) LEFT JOIN ServiceList ON AvailableServices.SLID = ServiceList.SLID WHERE Library.AID = $uLoc");
while($data = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td bgcolor=\"F3F3F3\">".$data['Name']."</td>";
echo "<td bgcolor=\"F3F3F3\">".$data['ServName']."</td>";
echo "</tr>";
}
?>
</table>
</div>
Can anyone offer any help for this problem. A solution or a point in the right direction?
Kind regards,
Jenova
Re: [RESOLVED] Format MySQL Output
just for fun, an example using HTML rowspans calculated using SQL.
PHP Code:
<?php
$dbh = new PDO('mysql:host=localhost;dbname=test', 'test', '');
$ds = $dbh->query('
select
s.location "Location",
s.services "Service",
t.c "__Location_count"
from sample s
left join (
select
location,
count(*) c
from sample
group by location
) as t on (t.location = s.location)
order by s.location asc, s.services asc
');
?>
<table>
<thead>
<?php
$rowstogo = array();
$row = $ds->fetch(PDO::FETCH_ASSOC);
foreach ($row as $field => $value):
if (strpos($field, '__') !== 0):
$rowstogo[$field] = 0;
?>
<th><?php echo $field ?></th>
<?php
endif;
endforeach;
?>
</thead>
<tbody>
<?php
while ($row !== false):
?>
<tr>
<?php
$rowspans = array();
foreach ($row as $field => $value)
if (strpos($field, '__') === 0)
{
$f = substr($field, 2, -6);
$rowspans[$f] = $value;
if ($rowstogo[$f] == 0)
$rowstogo[$f] = $value;
}
foreach ($row as $field => $value):
if (strpos($field, '__') !== 0):
if (array_key_exists($field, $rowspans)):
$c = $rowspans[$field];
if ($c = $rowstogo[$field]--):
?>
<td rowspan="<?php echo $c ?>"><?php echo $value ?></td>
<?php
endif;
else:
?>
<td><?php echo $value ?></td>
<?php
endif;
endif;
endforeach;
?>
</tr>
<?php
$row = $ds->fetch(PDO::FETCH_ASSOC);
endwhile;
?>
</tbody>
</table>