MYSQL results two column + alph. grouping

Hi everyone, I have this code:

$counter = 0;
$lastletter = '';

echo "<table border=\"1\" cellpadding=\"2\" cellspacing=\"1\">\n";

$counter = 0;
$lastletter = '';

while ($entry = mysql_fetch_assoc($result))
{
$first_char = strtoupper($entry['name'][0]);

if ($first_char != $lastletter)
{
$lastletter = $first_char;

echo "<tr>\n";
echo '<td colspan="2" style="text-align: center; font-weight: bold;">'. $first_char .'</tr>'. "\n";
echo "</tr>\n";
echo "<tr>\n";
}

echo "\t<td>{$entry['name']}</td>\n";

if (++$counter % 2 == 0)
{
echo "</tr>\n<tr>\n";
}
}

echo "</tr>
</table>\n";

It display like this

http://img124.imageshack.us/img124/3239/ssax6.jpg

The "B" group is incorrectly displayed. I would like it to display:

A
apples

B
bananas birds
bamboo



How would I go about correcting this error? Your help will be greatly appreciated.

You need to rethink your <tr>'s as you will leave some open for the next letter and etc. You need to start your counter over at each new letter. I never liked using the % 2 as you will end up leaving open <tr>'s...

Try this:

if ($first_char != $lastletter)
{
$lastletter = $first_char;
$counter = 0;

echo "<tr>\n";
echo '<td colspan="2" style="text-align: center; font-weight: bold;">'. $first_char .'</tr>'. "\n";
echo "</tr>\n";
}

if($counter == 0) {
echo "<tr>\n";
}

echo "\t<td>{$entry['name']}</td>\n";

if ($counter == 1)
{
echo "</tr>\n";
$counter = 0;
}

$counter++;

That will still leave open <tr>'s. Try this way with an added check:

if ($first_char != $lastletter)
{
$lastletter = $first_char;
if($counter != 0) {
echo "</tr>\n";
}
$counter = 0;

echo "<tr>\n";
echo '<td colspan="2" style="text-align: center; font-weight: bold;">'. $first_char .'</tr>'. "\n";
echo "</tr>\n";
}

if($counter == 0) {
echo "<tr>\n";
}

echo "\t<td>{$entry['name']}</td>\n";

if ($counter == 1)
{
echo "</tr>\n";
$counter = 0;
}

$counter++;

iLLin thank you soo much for your great effort. Your code works great, everything is almost fixed except the tiny "B" group, it still displayed incorrectly.

http://img526.imageshack.us/img526/7922/tabledy8.gif

Did'nt notice this in your code. NOt sure if its what the problem is.


echo "<tr>\n";
echo '<td colspan="2" style="text-align: center; font-weight: bold;">'. $first_char .'</tr>'. "\n";
echo "</tr>\n";


You close </tr> twice instead of closing the </td>'

Also you will need another check AFTER the loop

echo "</tr>
</table>\n";
//change to
if($counter != 0) {
echo "</tr>\n";
}
echo "</table>\n";

This must be homework that is due tomorrow and time to get it done is ticking away. This is the third different Forum that I know this has been posted on and the code itself is actually what was given by someone else in a different Forum.

m comes before n, so I suspect that you actually want -

bamboo birds
bananas

If you want this alphabetical down column one and finishing down column two, you will not be able to use this code that fetches one row from the result set and outputs it on the fly. You will need to fetch all the entries for one letter and store them and form the output once you know how many entries there are for that letter (you need to know the half-way point so that you know which entry starts the second column.)

Then if that doesnt work post up your viewsource of this part of your page.

If you want this alphabetical down column one and finishing down column two, you will not be able to use this code that fetches one row from the result set and outputs it on the fly. You will need to fetch all the entries for one letter and store them and form the output once you know how many entries there are for that letter (you need to know the half-way point so that you know which entry starts the second column.)

Not its just not going column column, if you notice its doing the first one now just not the second one. But yea if he wanted to do that he will need to learn arrays.

Hmm thanks guys, sorry if I'm frustrating you. I'm still a beginner. I've tried everything you mentioned, but still the same. So here it is.

$sql = "select * from bams order by name";
$result = $db->sql_query($sql);

$letterlinks = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
echo '<a name="#top"></a>';
echo '<a href="#number">0-9</a> ';
for ($i = 0; $i < 26; $i++)
{
echo '<a href="#'.$letterlinks[$i].'">'.$letterlinks[$i].'</a> ';
}

$counter = 0;
$lastletter = '';

echo "<table border=\"1\" cellpadding=\"2\" cellspacing=\"1\">\n";

$counter = 0;
$lastletter = '';

while ($entry = $db->sql_fetchrow($result))
{
$first_char = strtoupper($entry['name'][0]);

if ($first_char != $lastletter)
{
$lastletter = $first_char;
if($counter != 0) {
echo "</tr>\n";
}
$counter = 0;

echo "<tr>\n";
echo '<td colspan="2" style="text-align: center; font-weight: bold;">'. $first_char .'</td></tr>'. "\n";
}

if($counter == 0) {
echo "<tr>\n";
}

echo "\t<td>{$entry['name']}</td>\n";

if ($counter == 1)
{
echo "</tr>\n";
$counter = 0;
}

$counter++;
}

if($counter != 0) {
echo "</tr>\n";
}
echo "</table>\n";


and the HTML output is:

<a name="#top"></a><a href="#number">0-9</a> <a href="#A">A</a> <a href="#B">B</a> <a href="#C">C</a> <a href="#D">D</a> <a href="#E">E</a> <a href="#F">F</a> <a href="#G">G</a> <a href="#H">H</a> <a href="#I">I</a> <a href="#J">J</a> <a href="#K">K</a> <a href="#L">L</a> <a href="#M">M</a> <a href="#N">N</a> <a href="#O">O</a> <a href="#P">P</a> <a href="#Q">Q</a> <a href="#R">R</a> <a href="#S">S</a> <a href="#T">T</a> <a href="#U">U</a> <a href="#V">V</a> <a href="#W">W</a> <a href="#X">X</a> <a href="#Y">Y</a> <a href="#Z">Z</a>
<table border="1" cellpadding="2" cellspacing="1">
<tr>
<td colspan="2" style="text-align: center; font-weight: bold;">A</td></tr>
<tr>
<td>apple</td>
</tr>
<tr>
<td colspan="2" style="text-align: center; font-weight: bold;">B</td></tr>
<tr>
<td>bannana</td>
<td>beach</td>
</tr>
<td>bleach</td>
</tr>
<td>brother</td>
</tr>
</tr>
<tr>
<td colspan="2" style="text-align: center; font-weight: bold;">C</td></tr>
<tr>
<td>coke</td>
</tr>
<tr>
<td colspan="2" style="text-align: center; font-weight: bold;">E</td></tr>
<tr>
<td>eric</td>
</tr>
<tr>
<td colspan="2" style="text-align: center; font-weight: bold;">F</td></tr>
<tr>
<td>fried</td>
</tr>
<tr>
<td colspan="2" style="text-align: center; font-weight: bold;">H</td></tr>
<tr>
<td>happy</td>
</tr>
<tr>
<td colspan="2" style="text-align: center; font-weight: bold;">J</td></tr>
<tr>
<td>jared</td>
<td>jikson26</td>
</tr>
</tr>
<tr>
<td colspan="2" style="text-align: center; font-weight: bold;">L</td></tr>
<tr>
<td>lanson</td>
<td>lmao</td>
</tr>
<td>lol</td>
</tr>
</tr>
<tr>
<td colspan="2" style="text-align: center; font-weight: bold;">M</td></tr>
<tr>
<td>michael</td>
<td>mom</td>
</tr>
</tr>
<tr>
<td colspan="2" style="text-align: center; font-weight: bold;">N</td></tr>
<tr>
<td>naruto</td>
</tr>
<tr>
<td colspan="2" style="text-align: center; font-weight: bold;">O</td></tr>
<tr>
<td>octagon</td>
<td>orange</td>
</tr>
</tr>
<tr>
<td colspan="2" style="text-align: center; font-weight: bold;">P</td></tr>
<tr>
<td>pear</td>
<td>pepsi</td>
</tr>
</tr>
<tr>
<td colspan="2" style="text-align: center; font-weight: bold;">Q</td></tr>
<tr>
<td>question</td>
</tr>
<tr>
<td colspan="2" style="text-align: center; font-weight: bold;">S</td></tr>
<tr>
<td>saturated</td>
<td>sister</td>
</tr>
</tr>
<tr>
<td colspan="2" style="text-align: center; font-weight: bold;">T</td></tr>
<tr>
<td>tish tosh</td>
</tr>
<tr>
<td colspan="2" style="text-align: center; font-weight: bold;">U</td></tr>
<tr>
<td>unset</td>
</tr>
<tr>
<td colspan="2" style="text-align: center; font-weight: bold;">X</td></tr>
<tr>
<td>xero</td>
</tr>
<tr>
<td colspan="2" style="text-align: center; font-weight: bold;">Y</td></tr>
<tr>
<td>yuhmmy</td>
</tr>
<tr>
<td colspan="2" style="text-align: center; font-weight: bold;">Z</td></tr>
<tr>
<td>zackro</td>
<td>zero</td>
</tr>
<td>zuno</td>
</tr>
</tr>
</table>

Stupid me: One sec

Change this:



if ($counter == 1)
{
echo "</tr>\n";
$counter = 0;
}

$counter++;


to this


if ($counter == 1)
{
echo "</tr>\n";
}
$counter++
if($counter == 2) $counter = 0;


I wonder if there is a more elegant way of doing this...

The second if statement gives me a syntax error.

oops got it, forgot to ; after the increment

Yes, it works! Thank you so much for your help. +rep

I'm glad that there are people like you on the internet.

Glad to help :) Now think of a more elegant way to do it :p

You can switch this around -
if ($counter == 1)
{
echo "</tr>\n";
}
$counter++;
if($counter == 2) $counter = 0; to this -
$counter++; // always increment the counter and test it after
if ($counter == 2)
{ // do both, end the row and reset the counter together
echo "</tr>\n";
$counter = 0;
}