[SOLVED] Loop results of query into an array

[oskom]

Hello all,

I'm trying to take the results of a mysql query from a single table with data that needs to be sorted very carefully afterward based on certain parameters. The way I came up with is to organize data of a specific type into arrays to be sorted through again later. However I'm not quite sure of the method for taking the data from a query and then looping through it to stick it into another array. Here's the crux of what I'd like to do:

$result = mysql_query("SELECT * FROM table");

//MAYBE ESTABLISHING THE ARRAYS LIKE THIS?
$set1 = array(); 
$set2 = array();
$set3 = array();

$counter = 0;

while($row = mysql_fetch_array($result)) {
    $data = $row['data'];

    if($data == 1) {
        $set1[$counter]; = $data;
    }
    else if($data == 2) {
        $set2[$counter]; = $data;
    }
    else {    
        $set3[$counter]; = $data;
    }

    $counter++;
}

 

Am I on the right track here?

 

If it helps, here's what I'm doing with this: I'm trying to build a tree-structure type navigation with multiple levels. Each row in the db has these params:ID, parentID(if it's a sublevel item, this is the ID of the item it's relative to), text(of the link), URL(of the link), level(in the structure), displayOrder(relative to its' level). I want to pull out all the data ordered by displayOrder, then sort each of them into an array based on it's level and its' parentID if it's not level 1.

 

THE CAVEAT: My only problem with this concept is that it sets me up with a mess of loops, the number of which is predefined. I'd rather have a recursive function that will run until the max number of levels is reached, given the idea that each set of navigation links may have a varied amount of levels.

 

So perhaps I've got 2 questions: 1. How do you do the loop thing I've described?  2. Should I do the loop thing I've described in THIS case based on the desired output? (Wow! This is complicated stuff.)

 

Enlightenment?

[GingerRobot]

I'm a little confused as to what the displayOrder is all about? Perhaps some sample data from your database might be helpful.

 

Unless i've misunderstood, i think you might have overcomplicated the problem. Ordering by parent ID then by the ID should achieve the correct order, and then it'd just be case of manipulating that into the correct arrays. But as I say, im unsure if this is exactly what you're after.

[GingerRobot]

For example, here's a modification of something i made before:

 

<?php
$sql = "SELECT * FROM `menu` ORDER BY `id`";
$result = mysql_query($sql) or die(mysql_error());
$menu = array();
while($row = mysql_fetch_assoc($result)){
$id = $row['id'];
$parentid = $row['parentid'];
$link = $row['link'];//location to link to
$text = $row['text'];//text of the link
$menu[$parentid][$id]['link'] = $link;
$menu[$parentid][$id]['text'] = $text;
}
function menu($menu,$index=0){
	echo '<ul>';
foreach($menu[$index] as $k => $v){
	echo "<li><a href='".$v['link']."'>".$v['text']."</a></li>";
		if(is_array($menu[$k])){
		menu($menu,$k);
	}
}
echo '</ul>';	
}
menu($menu);
?>

 

Which works with data like this:

 

-- phpMyAdmin SQL Dump
-- version 2.8.2.4
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Generation Time: Dec 21, 2007 at 07:57 PM
-- Server version: 5.0.24
-- PHP Version: 5.1.6
-- 
-- Database: `test`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `menu`
-- 

CREATE TABLE `menu` (
  `id` int(10) NOT NULL auto_increment,
  `parentid` int(10) NOT NULL,
  `link` varchar(255) NOT NULL,
  `text` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

-- 
-- Dumping data for table `menu`
-- 

INSERT INTO `menu` (`id`, `parentid`, `link`, `text`) VALUES (1, 0, '/index.php', 'Home'),
(2, 0, '/contactus.php', 'Contact Us'),
(3, 0, '/members/index.php', 'Members'),
(4, 1, '/about.php', 'About'),
(5, 2, '/chat/index.php', 'Live Chat'),
(6, 5, '/chat/connected.php', 'Live Help'),
(7, 2, '/emailform.php', 'Email');

[oskom]

I like the menu function you've got there. It's not EXACTLY what'll do the trick but it's darn close!

 

Just to clarify, Gingerroot, the displayOrder is the "presentation" order. This is to give the site administrator the opportunity to order the links in whatever way they'd like. Also, here's the db table structure:

To clarify this, the links "Neural & Me", "Professionals", and "About" are level 1 navigation items, hence the parentID of those being 0. You'll notice that the(obviously BS) items of "harry" and "mary" have a parentID of 3 which is the ID of "Neural & Me"...that's the relationship.

 

What might augment what you've done with the menu function is to do a query for the sub-levels inside the loop(?). In other words, maybe I should start with a query that just pulls the top level links, loop through those and invoke the menu function inside that loop to pull the sublevels...does that make sense?

 

 

[oskom]

GingerRobot...your function is actually EXACTLY what I needed! It needs a little tweaking for presentation, but it really DID work.

 

Thanks!

[GingerRobot]

No problem. I thought it'd be pretty much the right sort of thing.