Help with display results

[mclamais]

I'm trying to create a page like this one on OfficeDepot.com

http://www.officedepot.com/a/ink-toner-and-ribbons/#A

 

I'm struggling with displaying the 3 columns under each heading.  I have the query (select all manufacturer in ascending order) the headers and the results under each header.  I just don't know who to get the record count for each section header so that I can calculate how many records to return in each of the 3 columns.

 

Is there an way to get the number of manufacturer records under the Heading "A" from the array of the main query?

 

So let's say for A its 30, I would then use this number to return 10 rows in each of the three columns.

 

Thanks for your assistance.

 

[JasonLewis]

Okay... Well I was bored so I wrote the code. :\

 

Sorry, but oh well. I've commented it a lot so you can make heads and tails of it.

 

<?php

//Get a list of all the manufacturers. If you can, an array like mine below is good.
$manufacturer = array(
						"A" => array(
										"Apple",
										"Ant",
										"Apollo",
										"Avenged Sevenfold", #Yes I'm listening to them as I write this. 
										"Alice Cooper", #Such a good artist!
										"Alice in Chains", #Pretty good!
										"ALP",
										"Amped",
										"Atlas",
										"A New Hope",
										"Attack Of The Clones",
										"Australia",
										"America"
										),
						);

$numberOfColumns = 6; //Set the number of columns, 3 by default.

echo "<table border='1' width='500' cellpadding='1' cellspacing='0'>";

foreach($manufacturer as $letter => $v){
$results = count($v);

//Now, to get the number of results per column we divide the
//$results variable by the $numberOfColumns variable then
//round up, using ceil().
//If we rounded down, in this case we would get 4. So if you
//have 4 results per column, you would have 1 remaining!
//So we round up, in this case we get 5. So the first and second
//column will both have 5 results, whilst the last will have 3 results.
$resultsPerColumn = ceil($results / $numberOfColumns);

//Start showing the table of results.
echo "<tr>";
echo "<td colspan='" . $numberOfColumns . "'>" . $letter . "</td>";
echo "</tr>";
echo "<tr>";

for($i = 0; $i < $results; $i++){
	//Start another loop to loop through all the results.
	//But lets check if we need to insert a new column.
	if($i % $resultsPerColumn == 0){
		//If this returns 0, it means we need to start a new column.
		echo "</td>";
		echo "<td valign='top'>";
	}

	//Now we can show the result.
	echo $v[$i] . "<br />";
}

//Now close off the current table data
echo "</td>";
echo "</tr>";
}

echo "</table>";

//Done!

?>

 

Good luck! Hope it works out for you!

[mclamais]

ProjectFear,

 

Thanks a million on such a great post, that helps a lot.    I would like to ask you a couple questions if you don't mind.

 

My query returns all 500 manufacturers A to Z, and the trouble I'm having is how to work with the array that comes from the query.  $row = mysql_fetch_assoc($result);

 

From your example I can see it's fairly straight forward, to deal with an array and values that all start with A, and display a single section. 

 

But what if the query returns all records A-Z.  How do I return each section one after another?  Is there a way to look inside the array and determine how many of the results are manufacturers that begin with A, and use this as $results.

 

$resultsPerColumn = ceil($results / $numberOfColumns);

 

Also I was trying to avoid using tables and planning on using div, lists and CSS. Similar to below.

<div class="index div0">   
    <div class="column1">
     <ul class="liststyle1">
        <li>
           <a href="">1000</a>
        </li>
        <li>
           <a href="">2000</a>
        </li>
	</ul>
    </div>
   
    <div class="column2">
     <ul class="liststyle1">
        <li>
           <a href="">3000</a>
        </li>
        <li>
           <a href="">4000</a>
        </li> 
     </ul>
    </div>
  </div>

[genericnumber1]

One method (and you should use server-side caching since retrieving this many records is slow) is to create a buffer variable and as you loop through the results with the while() loop constantly check the value of the first character (see: $row['manufacturer'][0] you can use array notation to select the first character from a string). Load each entry into the buffer array and when you've finally reached the next letter, dump the buffer array in the form you want after count()ing the number of variables.

 

Something like...

// Get the first result to initialize the prevFirstLetter
$firstRow = mysql_fetch_assoc($result);
$prevFirstLetter = $firstRow['manufacturer'][0];
// Move the pointer back
mysql_data_seek($result, 0);

$buffer = array();

// Loop through the query results (query not shown)
while($row = mysql_fetch_assoc($result)) {
$firstLetter = $row['manufacturer'][0];
// Is this a new letter? (eg. is this the start of the "B"s?)
if($firstLetter != $prevFirstLetter) {
	outputBuffer($buffer);
	outputSeperator($firstLetter);
	$buffer = array();
}

// Add this one to the buffer
$buffer[] = $row['manufacturer'];
$prevFirstLetter = $startLetter;
}

// Output the last letter buffer
outputBuffer($buffer);

 

Of course outputBuffer() and outputSeperator() above are just dummy functions (you define them, or whatever) to output the buffers and put in a separator. Inside of the outputBuffer function you can count() the array to see how many results there are before you break them up into columns.

 

This isn't meant to be working code I didn't debug it, but hopefully it gets you thinking... it also is case sensitive, but you can throw in some strcasecmp() or whatever if you'd like

[mclamais]

genericnumber1,

 

thanks for the post, very cool.

 

It's a bit over my head, and I kind stuck.  And I'm sure I messed something up

 

Since the outputBuffer is within if($firstLetter != $prevFirstLetter) that means it only going to display on the second letter (switching from A to B). Where do I put the output for the A Header?

 

 

 

<?php
function outputBuffer($buffer)
{
$numberOfColumns = 3; 
$rcount = count($buffer);
echo "The count is ".$rcount."<br />";
$resultsPerColumn = ceil($rcount / $numberOfColumns);
echo "resultsPerColumn is :".$resultsPerColumn."<br />";	
}

function outputSeperator($firstLetter)
{
// Display Section
$letter = $firstLetter;
if(is_numeric($letter)){
	$section_label = "0-9";
}else{
	$section_label = $letter;
}

echo "<div class=''>---------------------".$section_label."---------------------</div>\n";
}

require_once('conn_localhost.php');

$query  = "select * from ink_mfr order by manufacturer asc";
$result = mysql_query($query) or die(mysql_error());					

// Get the first result to initialize the prevFirstLetter
$firstRow = mysql_fetch_assoc($result);
$prevFirstLetter = $firstRow['manufacturer'][0];

// Move the pointer back
mysql_data_seek($result, 0);

$columnclass = "column1";

echo "<div class=''><div class='".$columnclass."'><ul>\n";

// Loop through the query results
while($row = mysql_fetch_assoc($result)) {
$firstLetter = $row['manufacturer'][0];

// Is this a new letter? (eg. is this the start of the "B"s?)
if($firstLetter != $prevFirstLetter) {
	outputBuffer($buffer);
	outputSeperator($firstLetter);
	$buffer = array();
}
echo "<li>".$row['manufacturer']."</li>\n";

// Add this one to the buffer
$buffer[] = $row['manufacturer'];
$prevFirstLetter = $firstLetter;
}

echo "</ul></div></div>";

// Output the last letter buffer
outputBuffer($buffer);
?>

 

My output is as follows

 

                    <---Missing Header
3M/IMATION
The count is 1
resultsPerColumn is :1
---------------------A---------------------
AB DICK
ABC
ABM
ABS
ACCESS MATRIX CORP
ACCESS MICROCOMPUTER
ACCRODYNE.......

The count is 100
resultsPerColumn is :34
---------------------B---------------------
B&E
BANCTEC
BARR-MORSE
BARRATT
BASF
BASIC FOUR/MAI
BAXTER......

[genericnumber1]

You would also need a priming header, which I left out in the code because I didn't test it and I made the mistake of forgetting it

 

you could add

outputSeperator($prevFirstLetter);

right above the while loop and it would do it.

 

Also, it will be much faster if you pass the buffer as a reference variable so it doesn't have to copy all of the values.

 

function outputBuffer(&$buffer)

 

and remember: server-side cache to save memory and execution time