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?

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 meeting, networking, learning, and sharing knowledge.