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
Awesome britoniah3480. Thank you. This is very close. We got the 25 rows, but the format of the empty rows - the bottom 6 in this case is off. Any way to make them look just empty?
<? 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
Thanks again.
Here is the result.
http://www.cornwallfantasyhockey.com/cfhl/mission/
We have lost the 25 rows now. Perhaps the answer is to echo a small white character that you cant see on the white background and it will fill in the boxes?
You can use 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. is the answer to the multiple space (or single space alone) problem.
yeah just add " " I guess that will do :)
No problem and Anytime :)
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 ;)
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.