Ordering Records in Columns rather than rows *solved*

[stualk]

Hello,

Can anyone help with the following code? Someone gave me this and it works perfectly, but I want to amend it slightly. What the code does it to display brand names from a database. However, it displays them alphabetically from left to right in three columns, then starts a new row. What I want ideally is to display them alphabetically DOWN column one and then start the second column going downwards again, then the third column doing the same.

Hope that makes sense! Can anyone help? Here's the code that works so far...


[code]if ($Filter) {

$NumRows = mysql_query("select * from products_table order by brand_name") or die("Sorry Couldn't Complete Query_1");
$NumRows = mysql_num_rows($NumRows);

if ($NumRows == "0")    {
  echo ("<font face=\"Verdana\" size=\"2\" color='#003366'>Sorry, we do not currently have any products listed in this section.</font>

");
}
else  {
$NumRows = mysql_query("select * from products_table where visible='Yes' order by brand_name") or die("FIX ME");
$NumRows = mysql_num_rows($NumRows);
$Result = mysql_query("select DISTINCT brand_name from products_table where visible='Yes' order by brand_name") or die("Sorry Couldn't Complete Query_2");


    $i=1;
    while($row = mysql_fetch_array($Result)){
       
if($i%3==1)
{
echo "<tr>";
}

echo("<td>");
  echo($row["brand_name"]);
echo("</td>");
   
if($i%3==0)
{

    echo("</tr>");
}
$i++;
}

echo("
<tr valign='top' align='left'>
    <td><font face='Verdana' size='2'><div class='navlinks' align='left'><img src='images/bullets.jpg'> <a href=HYPERLINK>$row[brand_name]</a></font></div></td>
</tr>
");
}}
else {
$NumRows=mysql_query("select * from products_table where visible='Yes' order by brand_name");
$NumRows=mysql_num_rows($NumRows);
$Result = mysql_query("select DISTINCT brand_name from products_table where visible='Yes' order by brand_name") or die("");
$i=1;
    while($row = mysql_fetch_array($Result)){
       
if($i%3==1)
{
echo "<tr height='30' valign='middle'>";
}

echo("<td width='150'>");
  echo("<font face='Verdana' size='2'><div class='navlinks' align='left'><img src='images/bullets.jpg'> <a href=HYPERLINK>$row[brand_name]</a></font></div>");
echo("</td>");
   
if($i%3==0)
{

    echo("</tr>");
}
$i++;
}
}

}[/code]

Thanks for your help

[HuggieBear]

The following works, obviously you'll need to change the sql and variables accordingly.

[code]<?php

include('connect.php');

$result = mysql_query('SELECT count(countryName) FROM tblCountry');
$num_records = mysql_result($result,0,0);
$max_num_rows = 2;
$max_num_columns = 3;
$per_page = $max_num_columns * $max_num_rows;
$total_pages = ceil($num_records / $per_page);
if (isset($_GET['page'])) $page = $_GET['page']; else $page = 1;
$start = ($page - 1) * $per_page;
$result = mysql_query("SELECT countryName FROM tblCountry ORDER BY countryName LIMIT $start, $per_page");
$num_columns = ceil(mysql_num_rows($result)/$max_num_rows);
$num_rows = min($max_num_rows, mysql_num_rows($result));
echo "<table border=\"2\">\n";
for ($r = 0; $r < $num_rows; $r++){
echo "<tr>\n";
for ($c = 0; $c < $num_columns; $c++){
$x = $c * $num_rows + $r;
if ($x < $num_records) $y = mysql_result($result, $x, 0); else $y = '&nbsp;';
echo "<td>$y</td>";
}
echo "</tr>\n";
}
echo "</table>\n";
for ($i=1;$i <= $total_pages;$i++) {
if ($i == $page) echo " $i "; else echo " <a href=\"?page=$i\">$i</a> ";
}
?>[/code]

[size=8pt][color=red][b]Edit:[/b][/color][/size] Just realised that this example includes pagination too.  You can remove that if not required.

Regards
Huggie

[Barand]

You need to store the name in array as you read the query results.
Here's sample code to output the array
[code]<?php
$data = array (1,2,3,4,5,6,7,8,9,10,11);
$cols = 3;
$rows = ceil(count($data)/$cols);

echo '<table border="1">' ;
for ($r = 0; $r < $rows; $r++) {
    echo '<tr>';
    for ($c = 0; $c < $cols; $c++) {
        echo '<td>' . $data[$r + $c * $rows] . '</td>';
    }
    echo '</tr>';
}
echo '</table>';
?>[/code]

[stualk]

Thanks for your help.

I've tried both of these methods and i'm struggling to implement them.

Is there no easier way of doing it than this? Surely I can just manipulate the existing code but put in a command to go down the columns instead of across the rows?

I'm confused!!

[HuggieBear]

There's no function for vertical columns as opposed to horizontal rows.

You need to calculate the cell based on the number of columns so that you can use the mysql_result() function with an offset.

What problem are you having?  Post your attempt at the code and we'll take a look for you.

Regards
Huggie

[stualk]

I'm just not sure how to put your code into the code I pasted as my original message.

Is there any way you can expand on your first reply so I know where to fit your code into mine?

I'm fairly new to this form of PHP so I don't really know what I did wrong but it didn't work.

Somehow I ended up with four columns at one point, even though I specified 3 as the maximum number of columns!

[Barand]

With mine, it's

[code]
<?php

$res = mysql_query("select brand_name from products_table order by brand_name");
$data = array();
while ($row = mysql_fetch_row($res))
    $data[] = $row[0];              // put data in array

$cols = 3;
$rows = ceil(count($data)/$cols);

echo '<table border="1">' ;
for ($r = 0; $r < $rows; $r++) {
    echo '<tr>';
    for ($c = 0; $c < $cols; $c++) {
        echo '<td>' . $data[$r + $c * $rows] . '</td>';
    }
    echo '</tr>';
}
echo '</table>';
?>[/code]

[stualk]

That works perfectly, thank you.

One other thing I need to do with this is to make each record into a hyperlink to the link below and put a tiny thumbnail image beside each record as well.

When I have tried to do this the '$brand_name' part of the link is not calling in the record, it is merely printing the record. I managed to make the thumbnail image part work but it displayed the image in empty columns as well, I'd rather it didn't do this. If it has to then it's no problem. I'm almost there so I'm not too fussed!

HYPERLINK: <a href="?view=characters&&Step=StepTwo&&brand_name=$brand_name&&sublinks=product_brands">

IMAGE: <img src='bullet.jpg'>

[Barand]

Try something like

[code]
<?php

$res = mysql_query("select brand_name from products_table order by brand_name");
$data = array();
while ($row = mysql_fetch_row($res)) {
    $brand_name = $row[0];
    $data[] = "<a href='?view=characters&Step=StepTwo&brand_name=$brand_name&sublinks=product_brands'>
        $brand_name</a>"
}

$cols = 3;
$rows = ceil(count($data)/$cols);

echo '<table border="1">' ;
for ($r = 0; $r < $rows; $r++) {
    echo '<tr>';
    for ($c = 0; $c < $cols; $c++) {
        echo '<td>' . $data[$r + $c * $rows] . '</td>';
    }
    echo '</tr>';
}
echo '</table>';
?>[/code]

[stualk]

Ok, I tried this but I got this error mesage,

"Parse error: syntax error, unexpected '}' in..."

which points at the last line of this section of code:

[code]while ($row = mysql_fetch_row($res)) {
    $brand_name = $row[0];
    $data[] = "<a href='?view=characters_and_themes3&Step=StepTwo&brand_name=$brand_name&sublinks=product_brands'>
        $brand_name</a>"
}[/code]

If it's only a syntax problem then we can't be far away. I tried removing the '}' but that didn't work either as I got this message:

"syntax error, unexpected T_VARIABLE"

[Barand]

Oops, missed ; at end of line
[code]
while ($row = mysql_fetch_row($res)) {
    $brand_name = $row[0];
    $data[] = "<a href='?view=characters_and_themes3&Step=StepTwo&brand_name=$brand_name&sublinks=product_brands'>
        $brand_name</a>" ;
}
[/code]

[stualk]

I should have seen that really shouldn't I!

That works perfectly now, thank you for your help. Much appreciated.