I know how to limit the number of rows returned in a query:

$result = mysql_query("SELECT * FROM playerdb where CFHL_A = 'XMEN' OR CFHL_B = 'XMEN' order by field(pos,'LW','C','RW','D','G'), PTS0910 DESC LIMIT 0, 11")
or die(mysql_error());

However, now I am looking to fix a table to 25 rows, even though there may only be 20 rows of data. In this exmaple, I would like the first 20 rows to have data and the bottom 5 rows to be blank.

Is this possible?

Recommended Answers

wait, can you explain further?

Jump to Post

yeah, of course. but this is out of the mysql thing. its all in the logic.

// while($row = mysql_fetch_array($result)) this is the old method but we need to replace this instead


 for($x=0;$x<=25;$x++) 
{
$row = mysql_fetch_array($result);
//assuming we're going to output just the name of the …
Jump to Post

You can use &nbsp; as an empty space in any empty table columns and rows. It's HTML's way of representing a space. In case you haven't noticed, a space along usually doesn't work, and multiple spaces (or a new line) do no appear. &nbsp; is the answer to the multiple …

Jump to Post

yeah just add " " I guess that will do :)

No problem and Anytime :)

Jump to Post

All 15 Replies

wait, can you explain further?

I am building a hockey pool site. Teams can have a maximum of 25 players. Often times teams have less than 25 so I want to have a table that has 25 rows, but it may not necessarily be full. There could be 20 rows of data and 5 blank lines.
Can this be done?

yeah, of course. but this is out of the mysql thing. its all in the logic.

// while($row = mysql_fetch_array($result)) this is the old method but we need to replace this instead


 for($x=0;$x<=25;$x++) 
{
$row = mysql_fetch_array($result);
//assuming we're going to output just the name of the player
if($row["playername"] != "") echo "Player's name: " . $row["playername"]; //if there is still player, echo its name
else
{
echo "Player: Blank"; // echo this if its NULL
}

  }

did you get the logic? I hope this helps

thanks! :)

britoniah3480 has a point. MySQL doesn't limit the number of rows in a table because that is not the purpose of a database. You can assume rows are empty if they aren't set (See britoniah3480's code above).
When inserting, you can ensure that there are still spaces by doing a quick check with PHP.

//Connect to MySQL
mysql_connect($host, $user, $pass) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());

//Check if there are spaces left
$result = mysql_query("SELECT * FROM players WHERE team = '".$team."'");
if(mysql_num_rows($result) < 25) {
    //Still spaces
} else {
    //Team full
}

Thanks again. Still not getting it to work. Here is an example of the page I want to update:

http://www.cornwallfantasyhockey.com/cfhl/mission/

This team has less that 25 players, but i want the table to be fixed at 25 lines...with blank ones. Here is the code that drives this table

<? php
$result = mysql_query("SELECT * FROM playerdb where CFHL_A = 'MISS' OR CFHL_B = 'MISS' order by field(pos,'LW','C','RW','D','G'), PTS0910 DESC")
or die(mysql_error());  

echo "<table width='350' border='1' cellspacing='0' cellpadding='1' bgcolor='ffffff'>";
echo "<tr> 
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>POS</b></td>
<td width='15' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>#</b></td>
<td width='170' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>PLAYER</b></td>
<td width='10' bgcolor='000000' align='center'><font face='arial' size='1' color='000000'><b>.</b></td>
<td width='30' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>TEAM</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>AGE</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>GP</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>PTS</b></td>
<td width='30' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>PPG</b></td>
</tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
	// Print out the contents of each row into a table
	echo "<tr><td width='20' bgcolor='ffffff' align='center'><font face='arial' size='2' color='000000'>"; 
	echo $row['Pos'];
	echo "</td><td width='15' bgcolor='ffffff' align='center'><font face='arial' size='2' color='000000'>";
	echo $row['No'];
	echo "</td><td width='170' bgcolor='ffffff' align='left'><font face='arial' size='2' color='000000'>"; 
	echo $row['Last'];
	echo ", ";
	echo $row['First'];
	echo "</td><td width='10' bgcolor='ffffff' align='center'><font face='arial' size='1' color='000000'>";  
	if ($row['CFHL_A']=="MISS") echo "A";
	if ($row['CFHL_B']=="MISS") echo "B";
	echo "</td><td width='30' bgcolor='ffffff' align='center'><font face='arial' size='2' color='000000'>";  
	echo $row['Team'];
	echo "</td><td width='20' bgcolor='ffffff' align='center'><font face='arial' size='2' color='000000'>";   
	echo calculateAge($row['BDay']);
	echo "</td><td width='20' bgcolor='800000' align='center'><font face='arial' size='2' color='FFFF00'><b>";  
	echo $row['GP0910'];
	echo "</td><td width='20' bgcolor='800000' align='center'><font face='arial' size='2' color='FFFF00'><b>"; 
	echo $row['PTS0910'];
	echo "</td><td width='30' bgcolor='800000' align='center'><font face='arial' size='2' color='FFFF00'><b>"; 
	echo number_format($row['PTS0910']/$row['GP0910'],2); 
	error_reporting (E_ERROR);
	echo "</td></tr>";
} 

echo "</table>";
?>

your help is appreciated

Gah, I should be doing my thesis now ~_~

<? php
$result = mysql_query("SELECT * FROM playerdb where CFHL_A = 'MISS' OR CFHL_B = 'MISS' order by field(pos,'LW','C','RW','D','G'), PTS0910 DESC")
or die(mysql_error());  

echo "<table width='350' border='1' cellspacing='0' cellpadding='1' bgcolor='ffffff'>";
echo "<tr> 
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>POS</b></td>
<td width='15' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>#</b></td>
<td width='170' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>PLAYER</b></td>
<td width='10' bgcolor='000000' align='center'><font face='arial' size='1' color='000000'><b>.</b></td>
<td width='30' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>TEAM</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>AGE</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>GP</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>PTS</b></td>
<td width='30' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>PPG</b></td>
</tr>";
// keeps getting the next row until there are no more to get

$x = 0;
while($x <= 25)// this will loop until its on the 25th row even its empty.
 {
$row = mysql_fetch_array( $result );
	// Print out the contents of each row into a table
	echo "<tr><td width='20' bgcolor='ffffff' align='center'><font face='arial' size='2' color='000000'>"; 
	echo $row['Pos'];
	echo "</td><td width='15' bgcolor='ffffff' align='center'><font face='arial' size='2' color='000000'>";
	echo $row['No'];
	echo "</td><td width='170' bgcolor='ffffff' align='left'><font face='arial' size='2' color='000000'>"; 
	echo $row['Last'];
	echo ", ";
	echo $row['First'];
	echo "</td><td width='10' bgcolor='ffffff' align='center'><font face='arial' size='1' color='000000'>";  
	if ($row['CFHL_A']=="MISS") echo "A";
	if ($row['CFHL_B']=="MISS") echo "B";
	echo "</td><td width='30' bgcolor='ffffff' align='center'><font face='arial' size='2' color='000000'>";  
	echo $row['Team'];
	echo "</td><td width='20' bgcolor='ffffff' align='center'><font face='arial' size='2' color='000000'>";   
	echo calculateAge($row['BDay']);
	echo "</td><td width='20' bgcolor='800000' align='center'><font face='arial' size='2' color='FFFF00'><b>";  
	echo $row['GP0910'];
	echo "</td><td width='20' bgcolor='800000' align='center'><font face='arial' size='2' color='FFFF00'><b>"; 
	echo $row['PTS0910'];
	echo "</td><td width='30' bgcolor='800000' align='center'><font face='arial' size='2' color='FFFF00'><b>"; 
	echo number_format($row['PTS0910']/$row['GP0910'],2); 
	error_reporting (E_ERROR);
	echo "</td></tr>";
$x++;
} 

echo "</table>";
?>

Cheers! Please don't forget to mark this thread "Solved" :D

<? php
$result = mysql_query("SELECT * FROM playerdb where CFHL_A = 'MISS' OR CFHL_B = 'MISS' order by field(pos,'LW','C','RW','D','G'), PTS0910 DESC")
or die(mysql_error());  

echo "<table width='350' border='1' cellspacing='0' cellpadding='1' bgcolor='ffffff'>";
echo "<tr> 
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>POS</b></td>
<td width='15' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>#</b></td>
<td width='170' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>PLAYER</b></td>
<td width='10' bgcolor='000000' align='center'><font face='arial' size='1' color='000000'><b>.</b></td>
<td width='30' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>TEAM</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>AGE</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>GP</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>PTS</b></td>
<td width='30' bgcolor='000000' align='center'><font face='arial' size='1' color='ffffff'><b>PPG</b></td>
</tr>";
// keeps getting the next row until there are no more to get

$x = 0;
while($x <= 25)// this will loop until its on the 25th row even its empty.
 {
$row = mysql_fetch_array( $result );
	// Print out the contents of each row into a table
	echo "<tr><td width='20' bgcolor='ffffff' align='center'><font face='arial' size='2' color='000000'>"; 
	echo $row['Pos'];
	echo "</td><td width='15' bgcolor='ffffff' align='center'><font face='arial' size='2' color='000000'>";
	echo $row['No'];
	echo "</td><td width='170' bgcolor='ffffff' align='left'><font face='arial' size='2' color='000000'>"; 
if($row['Last'] != "")
{	
echo $row['Last'];
	echo ", ";
	echo $row['First'];
	
}
echo "</td><td width='10' bgcolor='ffffff' align='center'><font face='arial' size='1' color='000000'>";  
	if ($row['CFHL_A']=="MISS") echo "A";
	if ($row['CFHL_B']=="MISS") echo "B";
	echo "</td><td width='30' bgcolor='ffffff' align='center'><font face='arial' size='2' color='000000'>";  
	echo $row['Team'];
	echo "</td><td width='20' bgcolor='ffffff' align='center'><font face='arial' size='2' color='000000'>";   
	if ($row['BDay'] != "") echo calculateAge($row['BDay']);
	echo "</td><td width='20' bgcolor='800000' align='center'><font face='arial' size='2' color='FFFF00'><b>";  
	echo $row['GP0910'];
	echo "</td><td width='20' bgcolor='800000' align='center'><font face='arial' size='2' color='FFFF00'><b>"; 
	echo $row['PTS0910'];
	echo "</td><td width='30' bgcolor='800000' align='center'><font face='arial' size='2' color='FFFF00'><b>"; 
	if($row['PTS0910'] != "") echo number_format($row['PTS0910']/$row['GP0910'],2); 
	error_reporting (E_ERROR);
	echo "</td></tr>";
$x++;
} 

echo "</table>";
?>

There you go. please mark this thread as "Solved" :D

You can use &nbsp; as an empty space in any empty table columns and rows. It's HTML's way of representing a space. In case you haven't noticed, a space along usually doesn't work, and multiple spaces (or a new line) do no appear. &nbsp; is the answer to the multiple space (or single space alone) problem.

yeah just add " " I guess that will do :)

No problem and Anytime :)

commented: Great Help! Thansk! +1

I would have done the same, but apparently, a space alone doesn't retain the same font size as the rest of the text in the table. If all else fails, insert some dummy text and style it with visibility: hidden; .

Indeed FlashCreations. I concur. it might ruin your design. there are lots of solution for that. and FlashCreations have a good one. but may I suggest instead of that you can use CSS to automatically adjust the table instead. here's a tutorial
http://www.w3schools.com/css/css_table.asp

please do mark this thread solve after. thanks! :)

Cheers! :D

Ahh yes, my suggestion only satisfied the aesthetic requirements, but doesn't seem that logical. I would instead use CSS like Darz mentioned and style each row to be a certain height.

table tr {
    height: 18px;
    line-height: 18px;
}

Best,
PhpMyCoder

You're good FlashCreations, Keep it up ;)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.