...

View Full Version : MYSQL results two column + alph. grouping



Jikson26
03-25-2007, 09:26 PM
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.

iLLin
03-25-2007, 09:45 PM
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++;

iLLin
03-25-2007, 09:48 PM
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++;

Jikson26
03-25-2007, 09:55 PM
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

iLLin
03-25-2007, 10:01 PM
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";

CFMaBiSmAd
03-25-2007, 10:02 PM
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.)

iLLin
03-25-2007, 10:03 PM
Then if that doesnt work post up your viewsource of this part of your page.

iLLin
03-25-2007, 10:05 PM
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.

Jikson26
03-25-2007, 10:38 PM
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>

iLLin
03-25-2007, 10:40 PM
Stupid me: One sec

iLLin
03-25-2007, 10:42 PM
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...

Jikson26
03-25-2007, 10:46 PM
The second if statement gives me a syntax error.

Jikson26
03-25-2007, 10:47 PM
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.

iLLin
03-25-2007, 10:51 PM
Glad to help :) Now think of a more elegant way to do it :p

CFMaBiSmAd
03-25-2007, 10:59 PM
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;
}



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum