Advanced Sorting Results from Query

[Jamez]

Ok, basically i have DB full of products, and it has cells of information for 'name', 'thumbnail', 'price', etc etc.

I know how to get all the products to display on the page in a single row:

mysql_select_db($sqldb);
$sql = "SELECT * FROM products WHERE category = '$type'";
$result=mysql_query($sql);
if (!$result) {
    print mysql_error()." ERROR - browse query failed.";
}
while($row = mysql_fetch_array($result)) {
		$product_id = $row[id];
		echo '<img src="http://www.mysite.com/thumbnails/'.$row["thumb"].'"><br></td></tr>';
		echo '<tr><td><a href="http://www.mysite.com/folder/'.$product_id.'.html">'.$row[name].'<br></a>';
		echo ''.$row[price].'<br><br>';
		echo ''.$row[description].'<br><br>';	
		$line_count++;
	}

So that works fine to list them all in a single row, but i want to list them all in 2 rows, sorted by 'name', i've used this code successfully befor but only with a single field being queried, i can't get it to work right querying all the above info:

<?
include('connect.php'); 
mysql_select_db('und_mov') or exit(mysql_error()); 

$query = 'SELECT title FROM list ORDER BY title'; 
$result = mysql_query($query) or exit(mysql_error()); 

while ($row = mysql_fetch_assoc($result)) {$array[] = $row['title'];} 
$chunks = array_chunk($array, ceil(count($array) / 2)); 

echo ' 
Total Movies: <b>' . mysql_num_rows($result) . '</b><br><br> 
<table border="0" cellpadding="5"> 
<tr valign="top"> 
'; 

foreach ($chunks as $value) 
{  

  echo '<td><table border="0" cellpadding="5">'; 
    foreach ($value as $title) 
    { 
        if (!$title) {$title = '&nbsp;';} 
        echo '<tr><td><a href="http://www.imdb.com/find?q=' . $title . ';tt=on;nm=on;mx=20">' . $title . '</td></tr>'; 
    } 
    echo '</table></td>'; 
} 
echo '</tr></table>';
?>

So i'm hoping someone is kind enough to show me (or tell) what i need to do to get all the fields from a query into the array so i can use them in the loop...

and even better, if they could ALSO show how to do this with 3 colums 🙂

[Roger_Ramjet]

God some of you people make things difficult for yourselves. The results are already in an array, the $result that everone gets from their queires. You can fetch each row as an array with mysql_fetch_array or as an associateive array with mysql_fetch_assoc, so you have an array already, no need to make the mess you are with al those array functions.

STANDARD DB DISPLAY SOLUTION :

$sql = "SELECT * FROM table";

$result = mysql_query($sql) or die($sql . '<br />' . mysql_error());

if (!$result) { }  // nothing retruned by query so deal with it 

echo '<table>';

while ($row = mysql_fetch_array($result)) {
   echo '<tr><td>' . $row['field1'] . '</td><td>' . $row['field2'] . '</td><td>' . $row['field3'] . '</td></tr>';
}

echo '</table>';

Just substitute your own query for $sql, and your column names for field1 etc.

Anything more complex than this for dealing with 99% os db queries is just bad coding by a bad programmer who should find another job. Use the KISS principle if you want to be a professional programmer. Keep It Simple Stupid - means you are not as clever as you thnk so don't try to be clever with your code. It is also THE basic principle of all engineering.

Mathematics Occam's Razor: 'The proof (formula) that requires the invention of the fewest entities is the correct one'.

Mechanical enginnering: the design with the fewest moving parts the best one.

All engineering: the simplest solution is always the right one so don't over-engineer the problem.

[Jamez]

I dont think you understood what i was asking, b/c you didnt answer it all.

This is a display problem, i want to display DIFFERENT products on 2-3 different columns of a table. Just like any decent store you shop on.

I ALREADY know how to do this:
PRODUCT1
PRODUCT2
PRODUCT3

I WANT to do this:
PRODUCT1 new td PRODUCT4 new td PRODUCT7
PRODUCT2 new td PRODUCT5 new td PRODUCT8
PRODUCT3 new td PRODUCT6

So not only does it need to go into 3 colums it needs to be sorted in that same type of order. The KISS principle doesn't always work when your dealing with people who want things to look a certain way 😉

[Roger_Ramjet]

So. Same differance. Basic html using a table in a table.

Get the count of records. Divide by 3 to get number in each column, add 1 if there is any remainder.
Start a table, open a cell and start a table. Loop through the results counting for the first column. Close the table and the data cell its in. Open next cell, start next table for middle column etc.

Still no need for anything but the original result array.

[Roger_Ramjet]

And if you don't like tables for page layout then its an ordered list <OL> and CSS for layout.

[Roger_Ramjet]

SLICE: function to calculate nuber of rows in each column

<?php
// SLICE Standard Lines Into Columns Enumerator

function slice($rows, $cols) {
	switch ($rows % $cols){
	case 0:
		return ($rows/$cols);
		break;
	default:
		return ($rows/$cols)+1;
	}
}

// so you use it like this

$rows = mysql_num_rows($result);
$cols = 3;  // assuming 3 columns, but it could be 2 or more

$rowcount = slice($rows, $cols);

for ($j=1,$j>$cols,$j++) {
echo '<ol id="list'.$j.'">';
for ($i=1, $i>$rowcount,$i++) {
    echo '<li>'. $row['name'].'</li>';
}
echo '</ol>';
}

// to create 3 ordered lists which you then use CSS to organise on the page
// or you could use a table in which case the code would be

$rowcount = slice($rows, $cols);

for ($j=1,$j>$cols,$j++) {
echo '<td><ol>';
for ($i=1, $i>$rowcount,$i++) {
    echo '<li>'. $row['name'].'</li>';
}
echo '</ol></td>';
}
?>