[RESOLVED] Sorting MySQL Query Results into Tables.

[daveyc]

Hi guys,

I've made a simple database, and im querying it using code which was very nicely pointed out to my be Gonwee in this thread http://phpbuilder.com/board/showthread.php?t=10329210

Now, that puts my results into a linear table. What im wondering is if i could make the script display my results, like the attached image.

Right, now the thing about the script is, I can't garuntee that each genre will have 6 channels, or even an even number of channels.. Will this cause a problem, or is there some way to do this?

Forgive my noobness! 🙂

Davey

[bpat1434]

SO basically, you want to list your information from a database into a table, that is X rows wide and Y rows long?

Simple enough:

<?php
$max_width = 2; // Define how many you want across here.

// Connect to mySQL here....

$query = ''; // Your query
$result = @mysql_query($query);
if(!$result)
    die('mySQL Errror ('.mysql_errno().'): '.mysql_error());

// Start the table
echo '<table>
<tr>';
$i=1; // And a counter
while($row = mysql_fetch_assoc($result))
{
  echo '<td>'.$row[1].'</td>';

  if($i%$max_width == 0) // Are we at our max width?  2 items across?
    echo '</tr><tr>';
}

?>

That's the easiest way. I also, a while ago, made a vertHoriz function to create, from an array of items, either a horizontal table, or a vertical table. I'll attach it as a courtesy in case you want to look at it, or need to make use of it. When you go to use it, just save it as ".php" instead of ".txt" and it will be a valid PHP file.

[daveyc]

Hi bpat,

Thanks for your help, although i'm having a little bit of trouble getting the page to display anything.

I've placed in my database connection string, and I've inserted my MySQL query. But i'm not getting any output at all 🙁

This is what i have after editing:

<?php 
$max_width = 2; // Define how many you want across here. 

$page = 'alt'; // This is the genre identifier. This will change from page to page.

include_once('connect.php'); // My file with the connection string

$query = 'SELECT * FROM `stations` WHERE genre=\'$page\''; 

$result = @mysql_query($query); 
if(!$result) 
    die('mySQL Errror ('.mysql_errno().'): '.mysql_error()); 

// Start the table 
echo '<table> 
<tr>'; 
$i=1; // And a counter 
while($row = mysql_fetch_assoc($result)) 
{ 
  echo '<td>'.$row[1].'</td>'; 

  if($i%$max_width == 0) // Are we at our max width?  2 items across? 
    echo '</tr><tr>'; 
} 

?> 

And the output on the page is blank. When i view the source of the page, i get this:

<table> 
<tr><td></td><td></td><td></td> 

Is there something i have missed?

Again, i apologise if i've missed something obvious! I am a bit of a noob! 😛

Thanks

Davey

[bpat1434]

One reason I see immediately is this line:

$query = 'SELECT * FROM `stations` WHERE genre=\'$page\'';

which should be:

$query = "SELECT * FROM `stations` WHERE genre='$page'";

Will edit as I see more....

EDIT
Oh, and after the if($i%$max_width==0) spot, it should increment the $i variable with:

$i++;

e.g.:

<?php
//....//

$i=1; // And a counter 
while($row = mysql_fetch_assoc($result)) 
{ 
  echo '<td>'.$row[1].'</td>'; 

  if($i%$max_width == 0) // Are we at our max width?  2 items across? 
    echo '</tr><tr>'; 

  // Increment the counter
  $i++;
} 
echo '</tr></table>';
?>

[daveyc]

Hi bpat1434,

I made the changes and i still got no output. So i thought about changing

echo '<td>'.$row[1].'</td>';

to:

echo '<td>'.$row[image].'</td>'; // "image" is the name of one of the rows in my database

and it worked perfectly 🙂

 <?php 
$max_width = 2; // Define how many you want across here. 

$page = 'alt'; // This is the genre identifier. This will change from page to page.

include_once('connect.php'); // My file with the connection string

$query = "SELECT * FROM `stations` WHERE genre='$page'";  

$result = @mysql_query($query); 
if(!$result) 
    die('mySQL Errror ('.mysql_errno().'): '.mysql_error()); 

// Start the table 
echo '<table border="1"> 
<tr>'; 

$i=1; // And a counter 
while($row = mysql_fetch_assoc($result)) 
{ 
  echo '<td><font face="arial" size="2"><div align="center"><img src="'.$row[image].'"><br><a href="radioplayer.php?scip='.$row[server].'&scport='.$row[port].'">Click Here To Listen</a></td>'; 

  if($i%$max_width == 0) // Are we at our max width?  2 items across? 
    echo '</tr><tr></font>'; 

  // Increment the counter 
  $i++; 
} 
echo '</tr></table>'; 

?> 

Now, i was just wondering about this... It doesnt matter, but it would be a nice feature.

Say, for example, the script returns 5 results, could the script merge the bottom cell so it's the width of the table, as the missing cell looks a little un-pretty..

As i said, it's not necessary, i just think it would be useful 🙂

Thank you SO much for all of your help

Davey 🙂

[bpat1434]

Hmm... possibly.... let me try really quick here with my vert-horiz loop.

It is possible. Basically here's the block of code that would change:

// Start the table
echo '<table border="1">
<tr>';
$max = @mysql_num_rows($result); // Get max number of results

$i=$k=0; // And a counter
while($row = mysql_fetch_assoc($result))
{
	if($k>0 && $k%$max_width == 0)
	{
		if($i==$max)
		{
			$span = ($max_width-$k)+1;
			$extra = ' colspan="'.$span.'"';
		}
		else
		{
			$extra = '';
			echo '</tr><tr>';
		}
	}

  echo '<td'.$extra.'><font face="arial" size="2"><div align="center"><img src="'.$row['image'].'"><br><a href="radioplayer.php?scip='.$row['server'].'&scport='.$row['port'].'">Click Here To Listen</a></font></td>';

  // Increment the counter
  $i++;
  $k++;
}
echo '</tr></table>';

Hope that gets you what you needed.

[daveyc]

Hi bpat1434,

I tried your code, but still got no change in the table layout 🙁

As i said, it doesn't really matter too much, so thank you for all of your help 🙂

Regards

Davey