[RESOLVED] Format MySQL Output

[Jenova]

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:

	<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

[menre]

The problem is clear from the attachment, but I think the solution will be implemented with your code. So, I suggest that you add a snippet of your code to the post, so that someone can tell you what to do to correct the error.

Hope that helps.

[Jenova]

Thanks, menre for your suggesstion. I've added the code i've been using to my thread.

[Jenova]

Excellent! Thank you SambaNeko for that idea. You've saved me a headache and a half. Had to tweek it a little but your method worked like a charm. Thank you so much for your help .

Rep added

Regards,

Jenova

[SambaNeko]

There's nothing special you need to do with SQL in this case; you'll just need some logic when you're outputting the results with PHP. Essentially, you'll need to loop through the results and check on the library name each time; if it's the same as the name on the previous loop, don't start a new "group," and if it isn't the same, do start one. Something like:

<table>
<tr>
<?php
$previousLibrary = '';
while($row = mysql_fetch_assoc($mysql_result)){
    if($previousLibrary != $row['libraryName']){
        if($previousLibrary != '') echo '</td></tr><tr>';
        echo '<td>'.$row['libraryName'].'</td><td>';
    }
    echo $row['LibraryService'].'<br/>';
    $previousLibrary = $row['libraryName'];    
}
?>
</td>
</tr>
</table>

Untested code, so no guarantee of perfection, but hopefully communicates the idea at least.

Depending on how you want to format the output markup, it could also be useful to use a while() loop to get your result into a multidimensional array in the form of $array['LibraryName'] = array('Feature1','Feature2','Feature3');

[penagate]

just for fun, an example using HTML rowspans calculated using SQL.

<?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>