[SOLVED] Display SQL results in columns with in a cell

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jkmcgrath
Forum Commoner
Posts: 33
Joined: Mon May 05, 2003 10:07 am

Display SQL results in columns with in a cell

Post by jkmcgrath »

Hi all, I have been fighting this for a few days now and I need help :lol:

In the section where you see //Get the Players it can return 0 to 150 names which creates a long list down the page. What I need to accomplish is breaking it up in 25 names per column and create however many columns it needs to display all the names.

Any help would be appreciated.

Thanks
John McGrath

Code: Select all

//Start the Table
echo "<table align='center' cellpadding='0' cellspacing='0' border='0'><tr><td valign='top'>";
echo "<table align='center' cellpadding='0' cellspacing='0' border='0'>";
echo "<tr><td>Server :</td><td>",$JOname,"</td></tr>";
echo "<tr><td>Type :</td><td>",$JOtype,"</td></tr>";
echo "<tr><td>Players :</td><td>",$JOnum,"/",$JOmax,"</td></tr>";
echo "<tr><td>Map :</td><td>",$JOmap,"</td></tr>";
echo "<tr><td>Region :</td><td>",$JOregion,"</td></tr>";
echo "<tr><td>Time of Day :</td><td>",$JOtimeday,"</td></tr>";
echo "<tr><td>Country :</td><td>",$JOco,"</td></tr>";
echo "<tr><td>Skins :</td><td>",$JOskins,"</td></tr>";
echo "<tr><td>Tracers :</td><td>",$JOtracers,"</td></tr>";
echo "<tr><td>PB :</td><td>",$JOpb,"</td></tr>";
echo "<tr><td>pwd :</td><td>",$JOpwd,"</td></tr>";
echo "<tr><td>Age :</td><td>",$JOage,"</td></tr>";
echo "<tr><td>MSG :</td><td>",$JOmsg,"</td></tr>";
echo "</table>";
//Get the players
$JOplayer = mysql_query("SELECT * FROM e_players WHERE rid = '$rid' ORDER BY name");
//Start the Table
echo "</td><td valign='top'>";
echo "<table align='center' cellpadding='0' cellspacing='0' border='0'>";
echo "<tr><Td align='left'><b>Players</b></td></tr>";
$i = 0;
while($JOquery = mysql_fetch_array($JOplayer)) &#123;
$i = $i++
$JOname = $JOquery&#1111;"name"];
//Fill the table with data

echo "<tr><td>",$JOname,"&nbsp;</td></tr>";
&#125;
echo "</table>";
echo "</td></tr></table><br><br>";
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post by Joe »

You could use:

Code: Select all

SELECT * FROM e_players WHERE rid = '$rid' ORDER BY name LIMIT 25
Or perhaps you could even use a bit of pagination. :D
jkmcgrath
Forum Commoner
Posts: 33
Joined: Mon May 05, 2003 10:07 am

Post by jkmcgrath »

Thanks that does limit it but I also want to display the rest in a column beside this one.

By the way, pagination would be nice as well, how would I go about that?

Thanks
John McGrath
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post by Joe »

Well pagination has been talked about numerous times in these forums, just do a search. :D

Also, he is a good pagination tutorial: http://www.phpfreaks.com/tutorials/43/0.php
litebearer
Forum Contributor
Posts: 194
Joined: Sat Mar 27, 2004 5:54 am

Post by litebearer »

I didn't test it because I was too lazy to setup a test db, but, this is a modification of a script I use that does work.

I modified(or at least tried) to fit your circumstances. It should be easy enough to test; all you need to change is the database connection information. Then run it as a separate test file.

Code: Select all

<?php
<?PHP
# set the number of rows per page
$limit =25;

# connect to the database
include ('db.php');

# calculate the number of names returned

$query_count = ""SELECT * FROM e_players WHERE rid = '$rid' ORDER BY name""; 
$result_count = mysql_query($query_count); 
$totalrows = mysql_num_rows($result_count); 

# set the number of cells per row
$per_row = ceil($totalrows/$limit);


# begin the actual query
$query = "SELECT * FROM e_players WHERE rid = '$rid' ORDER BY name"; 
$result = mysql_query($query) or die("Error: " . mysql_error()); 
if(mysql_num_rows($result) == 0){ 
 echo("Nothing to Display!"); 
} 

?>
<center>
  <table>
    <tr>
<?PHP
$ixi = 0;
while($row = mysql_fetch_array($result)){
  if ($ixi < $limit) {
    if (($ixi % $per_row) == 0) {
      echo "</tr><tr>";
    }
    echo "<td>" . $row['name'] . "</td>";
    $ixi ++;
  }
}
echo "</tr></table></center>";

mysql_free_result($result); 

?> 
?>
Lite...

(BTW here is the same script as I use it http://nstoia.com/effie/gallery/ )
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

litebearer wrote:

Code: Select all

# calculate the number of names returned
$query_count = ""SELECT * FROM e_players WHERE rid = '$rid' ORDER BY name""; 
$result_count = mysql_query($query_count); 
$totalrows = mysql_num_rows($result_count);
For the sake of efficiency i prefer "SELECT COUNT(*) as count FROM e_players WHERE ...."
litebearer wrote:

Code: Select all

# begin the actual query
$query = "SELECT * FROM e_players WHERE rid = '$rid' ORDER BY name";
And as you are using MySQL you could use the LIMIT operator here. Most other RDBMS have a similar propriatary operator. And most database abstraction layers have similar functionality too.
Post Reply