Chage MySQL field columns to populate html table in rows

[Rob (SA)]

Hi Folks,I am wondering if this is at all possible in the simplest of terms.I would like to change the MySQL field columns to populate my html table, in rows and when populating the page use a prescribed method.I have a link that as can be seen is populating in the vertical plane. I would like it to populate in the horizontal plane (i.e from left to right) and by using the highest number under the H/C coulmn until the table is populated before movine to the next table.Ideas here would be most welcomeRegardsRob

[Rob (SA)]

It might also be good to veiw an example I have prepared using excelHere is my example

[dsonesuk]

identify how many group cells (name and h/cap) per row you require,($total_col=4;) and this will echo these records in group size you have chosen.<table border="1" cellpadding="0" cellspacing="0"> <tr> <td>FULL NAME</td><td>H/CAP</td><td>FULL NAME</td><td>H/CAP</td><td>FULL NAME</td><td>H/CAP</td><td>FULL NAME</td><td>H/CAP</td> </tr> <?php $col_colunt=0; // count of group cells processed $total_col=4; //total group of record cells per row while ($row_dyntable = mysql_fetch_assoc($dyntable)) { if($col_count==0) //start tr row { echo "<tr>"; } ?> <td><?php echo $row_dyntable['FullName']; ?></td><td><?php echo $row_dyntable['Hcap']; ?></td> <?php $col_count++; if($col_count==$total_col) //when cell group matches total group cells per row, end tr row, and reset cell count. { echo "</tr>"; $col_count=0; } } if($col_count<4 && $col_count!=0) /// use to fill empty cells { for($i=$col_count;$i<$total_col;$i++) { echo "<td>empty</td><td>empty</td>"; } echo "</tr>"; } ?></table>

[Rob (SA)]

Hi,Thanks for the reply.This is my code I have at the moment.I have been playing with the alternating colours to see if I cab get the principle right first "Section adds headings" so that portion is not part of the real deal.Not to sure where I am suposed to edit your portion in - please advise?I look forward to new contact.

$quey1="select * from _Form_Nr_3 ORDER BY `i_wish_to_play` DESC, `my_handicap_is` DESC ";$result=mysql_query($quey1) or die(mysql_error());?><!------------------------------------------------------------------><!		   THIS SECTION CHANGES WHAT THE TABLE LOOKS LIKE		 ><!------------------------------------------------------------------><table border=2 width="85%" bordercolor="#D3E2FE" bordercolorlight="#FFFFFF" bordercolordark="#AFBCDB" style="font-size:11px" cellspacing=0><!------------------------------------------------------------------><!				  THIS SECTION CHANGES THE MAIN HEADING		   > <!------------------------------------------------------------------><caption><b><font style="font-size:16px" color="#00008B" face="Arial"></font><b><caption><!-------------------------------------------------------------------><!	THIS SECTION FETCHES THE INFORMATION FROM THE MYSQL TABLE	  >  <!-------------------------------------------------------------------><?php$counter = array();$last_i_wish_to_play = "";$firstNAsAString = "";while($row=mysql_fetch_array($result)){//----------------------------------------------------------------------------------------------------// THIS SECTION ADDS 3 EMPTY LINES IF THERE IS A SPLIT BUT ONLY AFTER SOME RECORDS HAVE BEEN DISPLAYED//----------------------------------------------------------------------------------------------------if($i > 0 AND $row['i_wish_to_play'] != $last_wish_to_play){echo '<tr><td colspan="8"> </td></tr>';}//------------------------------------------------------//			THIS SECTION ADDS HEADINGS				//------------------------------------------------------for($i=0; $i<=2; $i=$i+1)	{		if($i % 2 == 0)		{			echo "<tr>";			echo "<td style='background-color:#F0FFFF'>";			echo $i;			echo "</td>";			echo "</tr>";		}		else		{			echo "<tr>";			echo "<td style='background-color:#FFFFF0'>";			echo $i;			echo "</td>";			echo "</tr>";		}	}if ($row['i_wish_to_play'] != $last_wish_to_play) {echo '<tr><td colspan="8" align="center" bgcolor="#FFFFFF"><font style="font-size:11px" color="#00008B" face="Arial"><b>'.$row['i_wish_to_play'].'</b></font></td></tr>';echo '<tr><td width=100 bgcolor="#D6D6D8"><font style="font-size:11px" color="#00008B" face="Arial"><b>FULL NAME</b></font></td><td align="center" bgcolor="#D6D6D8"><font style="font-size:11px" color="#00008B" face="Arial"><b>H/CAP</b></font></td><td width=100 bgcolor="#D6D6D8"><font style="font-size:11px" color="#00008B" face="Arial"><b>FULL NAME</b></font></td><td align="center" bgcolor="#D6D6D8"><font style="font-size:11px" color="#00008B" face="Arial"><b>H/CAP</b></font></td><td width=100 bgcolor="#D6D6D8"><font style="font-size:11px" color="#00008B" face="Arial"><b>FULL NAME</b></font></td><td align="center" bgcolor="#D6D6D8"><font style="font-size:11px" color="#00008B" face="Arial"><b>H/CAP</b></font></td><td width=100 bgcolor="#D6D6D8"><font style="font-size:11px" color="#00008B" face="Arial"><b>FULL NAME</b></font></td><td align="center" bgcolor="#D6D6D8"><font style="font-size:11px" color="#00008B" face="Arial"><b>H/CAP</b></font></td></tr>';}echo '<tr><td bgcolor="#FFFFF0"><font style="font-size:10px" color="#00008B" face="Arial">';echo '<option value="'.$row['surname'].', '.$row['name'].'">'.$row['surname'].', '.$row['name'].'</option>';echo '</td><td align="center" bgcolor="#F0FFFFF"><font style="font-size:10px" color="#00008B" face="Arial">';echo $row['my_handicap_is'];if($row['i_wish_to_play'] != $last_wish_to_play){// the first n words to extract$n = 4;// extract the words$words = explode(" ", $row['i_wish_to_play']);// chop the words array down to the first n elements$firstN = array_slice($words, 1, $n-1);// glue the 3 elements back into a spaced sentence$firstNAsAString = implode(" ", $firstN);}if(!isset($counter[$firstNAsAString]))$counter[$firstNAsAString] = 0;else$counter[$firstNAsAString]++;$last_wish_to_play = $row['i_wish_to_play'];$i++;echo '</td></tr>';}echo '</table>';?>

identify how many group cells (name and h/cap) per row you require,($total_col=4;) and this will echo these records in group size you have chosen.<table border="1" cellpadding="0" cellspacing="0"> <tr> <td>FULL NAME</td><td>H/CAP</td><td>FULL NAME</td><td>H/CAP</td><td>FULL NAME</td><td>H/CAP</td><td>FULL NAME</td><td>H/CAP</td> </tr> <?php $col_colunt=0; // count of group cells processed $total_col=4; //total group of record cells per row while ($row_dyntable = mysql_fetch_assoc($dyntable)) { if($col_count==0) //start tr row { echo "<tr>"; } ?> <td><?php echo $row_dyntable['FullName']; ?></td><td><?php echo $row_dyntable['Hcap']; ?></td> <?php $col_count++; if($col_count==$total_col) //when cell group matches total group cells per row, end tr row, and reset cell count. { echo "</tr>"; $col_count=0; } } if($col_count<4 && $col_count!=0) /// use to fill empty cells { for($i=$col_count;$i<$total_col;$i++) { echo "<td>empty</td><td>empty</td>"; } echo "</tr>"; } ?></table>

[dsonesuk]

this should work, just check table reference and field references<?php$quey1="select * from _Form_Nr_3 ORDER BY `i_wish_to_play` DESC, `my_handicap_is` DESC ";$result=mysql_query($quey1) or die(mysql_error());?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /><title>Untitled Document</title><script type="text/javascript">/*<![CDATA[*//*---->*//*--*//*]]>*/</script> <style type="text/css">table {border:2px solid #D3E2FE; width:100%; font-size:11px; font-family:Arial; color:#00008B;}table tr td {border-right:1px solid #D3E2FE;border-top:1px solid #D3E2FE;}tr.header{background-color:#D6D6D8; }.rowcol1 {background-color:#F0FFFF;}.rowcol2 {background-color:#FFFFF0;}tr.header td{width:100px; }#wrapper {width:85%; margin:0 auto;}</style></head><body><div id="wrapper"><?php $col_colunt=0; $total_col=4; $currentclub=""; $trcount=0; $trclass=""; while ($row=mysql_fetch_array($result)) { if($currentclub != $row['i_wish_to_play']) { if($col_count<$total_col && $col_count!=0) /// use to fill empty cells { for($i=$col_count;$i<$total_col;$i++) { echo "<td>empty</td><td>empty</td>"; } echo "</tr></table>"; $trcount=0; } ?> <p></p><table border="0" cellpadding="0" cellspacing="0"> <tr> <th colspan="<?php echo $total_col*2; ?>"><?php echo $row['i_wish_to_play']; ?></th> </tr> <tr class="header"><?phpfor ($i=0;$i<$total_col;$i++){echo '<td>FULL NAME</td><td>H/CAP</td>';}?> </tr><?php $col_count=0; } if($col_count==0) { $trcount++; if($trcount % 2 == 0) { $trclass="rowcol1"; } else { $trclass="rowcol2"; } echo '<tr class="'.$trclass.'">'; } ?> <td><?php echo $row['name'].', '.$row['surname']; ?></td> <td><?php echo $row['my_handicap_is']; ?></td> <?php $col_count++; if($col_count==$total_col) { echo "</tr>"; $col_count=0; } $currentclub = $row['i_wish_to_play']; }//end while loop if($col_count<$total_col && $col_count!=0) /// use to fill empty cells { for($i=$col_count;$i<$total_col;$i++) { echo "<td>empty</td><td>empty</td>"; } echo "</tr>"; } ?> </table> </div></body></html>

[dsonesuk]

spotted problem when changing column group number, but sorted now with Improvements, auto cell width adjustment.

<?php$quey1="select * from _Form_Nr_3 ORDER BY `i_wish_to_play` DESC, `my_handicap_is` DESC ";$result=mysql_query($quey1) or die(mysql_error());  $col_colunt=0;  $total_col=4;  //group column number (name and handicap)  $currentclub="";  $trcount=0;  $trclass="";?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /><title>Untitled Document</title><script type="text/javascript">/*<![CDATA[*//*---->*//*--*//*]]>*/</script> <style type="text/css">table {border:2px solid #D3E2FE;  width:100%;  font-size:11px;  font-family:Arial; color:#00008B;margin-bottom:20px;}table tr td {border-right:1px solid #D3E2FE;border-top:1px solid #D3E2FE;}tr.header{background-color:#D6D6D8; font-weight:bold;}.rowcol1 {background-color:#F0FFFF;}.rowcol2 {background-color:#FFFFF0;}tr.header td.fullname{width:<?php echo 80/$total_col; ?>%; }tr.header td.hcap{width:<?php echo 20/$total_col; ?>%;}td.hcap {text-align:center;}#wrapper {width:85%; margin:0 auto;}</style></head><body><div id="wrapper"><?php while ($row=mysql_fetch_array($result)) 	{ 	  if($currentclub != $row['i_wish_to_play'])		  {   		   if($col_count<$total_col && $col_count!=0) /// use to fill empty cells			   {			   for($i=$col_count;$i<$total_col;$i++)				   {				   echo "<td> </td><td> </td>";				   }			  echo "</tr></table>";			  $trcount=0;			  } else if ($currentclub !="") {			   echo "</table>";			  $trcount=0; }  ?> <table border="0" cellpadding="0" cellspacing="0">  <tr>  <th colspan="<?php echo $total_col*2; ?>"><?php echo $row['i_wish_to_play']; ?></th>  </tr>  <tr class="header"><?phpfor ($i=0;$i<$total_col;$i++){echo '<td class="fullname">FULL NAME</td><td class="hcap">H/CAP</td>';}?>	  </tr><?php	$col_count=0;		}	if($col_count==0)		  {		$trcount++;		if($trcount % 2 == 0)			{			$trclass="rowcol1";			}		else			{			$trclass="rowcol2";			}		  echo '<tr class="'.$trclass.'">';		  }    ?>    <td><?php echo $row['name'].', '.$row['surname']; ?></td>	<td class="hcap"><?php echo $row['my_handicap_is']; ?></td>	  <?php  	  $col_count++;	   if($col_count==$total_col)		  {		  echo "</tr>";		  $col_count=0;		  } 	$currentclub = $row['i_wish_to_play'];   }//end while loop          if($col_count<$total_col && $col_count!=0) /// use to fill empty cells		   {		   for($i=$col_count;$i<$total_col;$i++)			   {			   echo "<td> </td><td> </td>";			   }		   echo "</tr>";		  }		   ?>		</table></div></body></html>

[Rob (SA)]

Hi Folks,Thanks for that help.It presents well and I need your help in making a few tweaks:1. I would like all the text vewied to only appear in Capitols.2. I need to insert a column that is headed "TIME" at the extreme left of the table3. I need to now split the tables up into quarters - this must be done by equally splitting the table by row count. Then splitting that by two again and insering a row which is filled with the name "STARTER" under each column headed by the word "FULL NAME".4. The last entry in each table always has to be greater than 1 - otherwaise the player then participates on his /her own and this would not be allowed.The ultimate would then be to be able to select the number 2,3 or 4 and this would then create the "DRAW" with 4,6 or 8 columns just like the one we have just done except it would have times allocated to them in the column on the extreme left.I new there was someone out there who could help.Kind RegardsRob

spotted problem when changing column group number, but sorted now with Improvements, auto cell width adjustment.

<?php$quey1="select * from _Form_Nr_3 ORDER BY `i_wish_to_play` DESC, `my_handicap_is` DESC ";$result=mysql_query($quey1) or die(mysql_error());  $col_colunt=0;  $total_col=4;  //group column number (name and handicap)  $currentclub="";  $trcount=0;  $trclass="";?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /><title>Untitled Document</title><script type="text/javascript">/*<![CDATA[*//*---->*//*--*//*]]>*/</script> <style type="text/css">table {border:2px solid #D3E2FE;  width:100%;  font-size:11px;  font-family:Arial; color:#00008B;margin-bottom:20px;}table tr td {border-right:1px solid #D3E2FE;border-top:1px solid #D3E2FE;}tr.header{background-color:#D6D6D8; font-weight:bold;}.rowcol1 {background-color:#F0FFFF;}.rowcol2 {background-color:#FFFFF0;}tr.header td.fullname{width:<?php echo 80/$total_col; ?>%; }tr.header td.hcap{width:<?php echo 20/$total_col; ?>%;}td.hcap {text-align:center;}#wrapper {width:85%; margin:0 auto;}</style></head><body><div id="wrapper"><?php while ($row=mysql_fetch_array($result)) 	{ 	  if($currentclub != $row['i_wish_to_play'])		  {   		   if($col_count<$total_col && $col_count!=0) /// use to fill empty cells			   {			   for($i=$col_count;$i<$total_col;$i++)				   {				   echo "<td> </td><td> </td>";				   }			  echo "</tr></table>";			  $trcount=0;			  } else if ($currentclub !="") {			   echo "</table>";			  $trcount=0; }  ?> <table border="0" cellpadding="0" cellspacing="0">  <tr>  <th colspan="<?php echo $total_col*2; ?>"><?php echo $row['i_wish_to_play']; ?></th>  </tr>  <tr class="header"><?phpfor ($i=0;$i<$total_col;$i++){echo '<td class="fullname">FULL NAME</td><td class="hcap">H/CAP</td>';}?>	  </tr><?php	$col_count=0;		}	if($col_count==0)		  {		$trcount++;		if($trcount % 2 == 0)			{			$trclass="rowcol1";			}		else			{			$trclass="rowcol2";			}		  echo '<tr class="'.$trclass.'">';		  }    ?>    <td><?php echo $row['name'].', '.$row['surname']; ?></td>	<td class="hcap"><?php echo $row['my_handicap_is']; ?></td>	  <?php  	  $col_count++;	   if($col_count==$total_col)		  {		  echo "</tr>";		  $col_count=0;		  } 	$currentclub = $row['i_wish_to_play'];   }//end while loop          if($col_count<$total_col && $col_count!=0) /// use to fill empty cells		   {		   for($i=$col_count;$i<$total_col;$i++)			   {			   echo "<td> </td><td> </td>";			   }		   echo "</tr>";		  }		   ?>		</table></div></body></html>

[dsonesuk]

1. I would like all the text vewied to only appear in Capitols.2. I need to insert a column that is headed "TIME" at the extreme left of the table3. I need to now split the tables up into quarters - this must be done by equally splitting the table by row count. Then splitting that by two again and insering a row which is filled with the name "STARTER" under each column headed by the word "FULL NAME".4. The last entry in each table always has to be greater than 1 - otherwise the player then participates on his /her own and this would not be allowed.please clarify 3, dividing 4 row table quarterly will mean EACH row being separated by the 'starter' row? or do you mean 2 rows of 2, separated by 1 single 'starter row(as in excel example). Also will the rows be greater than 4, example, if we had 6 row table, would you require this split into, 2 rows of 3, OR 3 rows of 2 all separated by starter row.query for 4; I can add an extra row, to make number of rows even. To control number of persons shown to be 'greater than 1', so you want me to just prevent this player from being shown completely?

[justsomeguy]

In PHP you can use the strtoupper function to convert a string to uppercase, or you can also use SQL functions to return the text as uppercase from the database. In Javascript you can use the string.toUpperCase method.

2. I need to insert a column that is headed "TIME" at the extreme left of the table

What about that do you need help with?

[dsonesuk]

1 and 2 strike through meaning done!

[Rob (SA)]

Hi Folks,I have made a sample of what I am trying to achieve and it can vewied at http://www.gnjgf.co.za/DRAWTEST_EX.htm This means I have to still do on my side 1 & 2.If one goes to this page and select the word "DRAW" one will see the effect of getting the various draws per group players be it a group of two, three or four.The last group must never be one otherwise he/she plays on thier own.Each tournament is on a seperate table and on this link page the headings starting with @ . . . represent a new tournament.The players enter through a seperate "Entry form" and sometimes a person might enter by accident more than once thus upsetting the draw - I would therefor like to build in the function that prevents this by a simple check of a unique quality already then present in the database. The result of entering twice would come up with the prompt "You have already been entered for this event - Please select another event"I hope this helps to make matters more clear.

1 and 2 strike through meaning done!

[dsonesuk]

The players enter through a seperate "Entry form" and sometimes a person might enter by accident more than once thus upsetting the draw - I would therefor like to build in the function that prevents this by a simple check of a unique quality already then present in the database.

for this to work a player would need a unique reference to identify them.name - no! as could misspelled it when applying, how many 'john smith's' are there now!address - no! members of same address may wish to playcell phone number - maybe, as long as they enter the number correctly, and if they even have cell phone (unlikely i know, but is a possible factor to consider).best option is for them to register, which would give them an unique user id, this would then uniquely identify them.they would login, go to the entry form and fill in the details (address, name phone etc could be already stored on registration) it could already show them the tournaments they are already applied to play in, but if they do try to enter the same tournament the database would pick up their logged in userid has already been used in a application, and give a warning.Edit:OR they register and are given userid, username and passwordthey don't need to log in, just provide username and password (hidden with asterisk), when filling out the applying for tournament form, this would check if username, password match. Then, if they pass, check if the players userid already exist in selected tournament.

[Rob (SA)]

Hi,You ar ecorrect and the options of consideration are valid as future communications are done either by cellphone and/ or email address.I will check my datbase this evening to confirm but there might already be id' options avialable and that option I think is th ebest one.A lot more complicating for me who is a leak at this level.In th eoverall picture of things this is very improtant as I would like to have a facility later that captures scores and this then needs to be for the correct player who is correctly identified rather than have all th eJones's getting more scores tahn they had played.Will provide another post later.

for this to work a player would need a unique reference to identify them.name - no! as could misspelled it when applying, how many 'john smith's' are there now!address - no! members of same address may wish to playcell phone number - maybe, as long as they enter the number correctly, and if they even have cell phone (unlikely i know, but is a possible factor to consider).best option is for them to register, which would give them an unique user id, this would then uniquely identify them.they would login, go to the entry form and fill in the details (address, name phone etc could be already stored on registration) it could already show them the tournaments they are already applied to play in, but if they do try to enter the same tournament the database would pick up their logged in userid has already been used in a application, and give a warning.Edit:OR they register and are given userid, username and passwordthey don't need to log in, just provide username and password (hidden with asterisk), when filling out the applying for tournament form, this would check if username, password match. Then, if they pass, check if the players userid already exist in selected tournament.

[Rob (SA)]

Hi,In follow up - in my database - my membership has personal Identity numbers which can be selected - as only members may participate in these events - hence a check can thus be made.Alternatively a login page can be created and then the cell number used to verify their entry - I like this optiton better because it can be used in both d/bases.I hope this does help

Hi,You ar ecorrect and the options of consideration are valid as future communications are done either by cellphone and/ or email address.I will check my datbase this evening to confirm but there might already be id' options avialable and that option I think is th ebest one.A lot more complicating for me who is a leak at this level.In th eoverall picture of things this is very improtant as I would like to have a facility later that captures scores and this then needs to be for the correct player who is correctly identified rather than have all th eJones's getting more scores tahn they had played.Will provide another post later.

[Rob (SA)]

I think this is also a very good option - and the check can be their user name is thier cellnumber - Thoughts are welcome here

they register and are given userid, username and passwordthey don't need to log in, just provide username and password (hidden with asterisk), when filling out the applying for tournament form, this would check if username, password match. Then, if they pass, check if the players userid already exist in selected tournament.

[dsonesuk]

Might be overcautious here, but this could be open to abuse, as players will know each other cellphone number, so some could enter other members into tournaments without their permission. same goes with email as well, but with password submission this is less likely to be known by other members, well.. at least it should not be.

[Rob (SA)]

Hi folks,In my current script I am tryin gto change all the lower case to upper case.Can anyone assist me as to where I can place strtoupper that all the text is recognised in capitals.

Might be overcautious here, but this could be open to abuse, as players will know each other cellphone number, so some could enter other members into tournaments without their permission. same goes with email as well, but with password submission this is less likely to be known by other members, well.. at least it should not be.

[dsonesuk]

<td class="fullname <?php echo $trclass; ?>"><?php echo strtoupper($row['name']); ?>, <?php echo strtoupper($row['surname']); ?></td>

[Rob (SA)]

PERFECT.Works like a dream.I am being a real pain here.Can I split the text sizeing options - in other words - instead of selecting the header of each table and the contents of each table to be the same i have the option to change the contents and the header to be different font sizes an dperhaps types.I really appreciat eyour help in this regard

<td class="fullname <?php echo $trclass; ?>"><?php echo strtoupper($row['name']); ?>, <?php echo strtoupper($row['surname']); ?></td>

[dsonesuk]

table {border:2px solid #D3E2FE; width:100%; font-size:12px; font-family:Arial; color:#00008B;margin-bottom:20px;}is the default setting, and will control font type, size in all table cellsbut, below will control header row cells, with class header. so by adding font-size and font-family, you can control how the text looks in this row. Example:tr.header{background-color:#D6D6D8; font-weight:bold; font-size:10px; font-family: "Times Roman";}

[Rob (SA)]

Thx

table {border:2px solid #D3E2FE; width:100%; font-size:12px; font-family:Arial; color:#00008B;margin-bottom:20px;}is the default setting, and will control font type, size in all table cellsbut, below will control header row cells, with class header. so by adding font-size and font-family, you can control how the text looks in this row. Example:tr.header{background-color:#D6D6D8; font-weight:bold; font-size:10px; font-family: "Times Roman";}