Jump to content


Photo

Ordering Records in Columns rather than rows *solved*


  • Please log in to reply
11 replies to this topic

#1 stualk

stualk
  • Members
  • PipPipPip
  • Advanced Member
  • 109 posts

Posted 19 September 2006 - 01:37 PM

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...


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++;
 }
}

}

Thanks for your help

#2 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 19 September 2006 - 01:56 PM

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

<?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> ";
}
?>

Edit: Just realised that this example includes pagination too.  You can remove that if not required.

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#3 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 19 September 2006 - 02:03 PM

You need to store the name in array as you read the query results.
Here's sample code to output the array
<?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>';
?>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#4 stualk

stualk
  • Members
  • PipPipPip
  • Advanced Member
  • 109 posts

Posted 20 September 2006 - 12:52 PM

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!!

#5 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 20 September 2006 - 01:04 PM

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
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#6 stualk

stualk
  • Members
  • PipPipPip
  • Advanced Member
  • 109 posts

Posted 20 September 2006 - 02:12 PM

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!

#7 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 20 September 2006 - 02:17 PM

With mine, it's

<?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>';
?>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#8 stualk

stualk
  • Members
  • PipPipPip
  • Advanced Member
  • 109 posts

Posted 20 September 2006 - 09:28 PM

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'>

#9 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 20 September 2006 - 11:31 PM

Try something like

<?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>';
?>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#10 stualk

stualk
  • Members
  • PipPipPip
  • Advanced Member
  • 109 posts

Posted 21 September 2006 - 09:03 AM

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:

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>"
}

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"

#11 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 21 September 2006 - 09:06 AM

Oops, missed ; at end of line
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>" ;
}

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#12 stualk

stualk
  • Members
  • PipPipPip
  • Advanced Member
  • 109 posts

Posted 21 September 2006 - 09:50 AM

I should have seen that really shouldn't I!

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users