[RESOLVED] array thing with mysql

[ajdegans]

How to make this work:

$kaart="SELECT * FROM zc_kaart WHERE id='$id'";
$kaart2=mysql_query($kaart) or die("Could not query database.<br>".mysql_error());
$kaart3=mysql_fetch_array($kaart2);
//Highest level of catagories

$cat="SELECT * FROM zc_categorie WHERE parent='$id'";
$cat2=mysql_query($cat) or die("Could not query database.<br>".mysql_error());
//the parent field matches the id field in zc_kaart cuz the zc_categorie are subcatagories for zc_kaart

$prod="SELECT * FROM zc_menu WHERE kaart='$id'";
$prod2=mysql_query($prod) or die("Could not query database.<br>".mysql_error());
//inhere items are stored it identifies the catagorie its in at kaart (matching with the id in zc_kaart), categorie (matching that with id in zc_categorie)

//output
print "$kaart3[kaart]<br>";
while($cat3=mysql_fetch_array($cat2)) {
    print "- $cat3[categorie]<br>";
    while($prod3=mysql_fetch_array($prod2)) {
        print "-- $prod3[omschrijving]<br>";
    }
}

the idea is to create a sort of array which looks like this:

 
1st thing
- categorie
- categorie
----item
----item
- categorie
- categorie
----item
- categorie
-item
-item

the database queries get data for this list the zc_kaart table holds 3 the highest catagory. In there subcatagories and items can be placed. Also in the subs, items can be placed. (as shown in my example) How do i make this work the fastest, most efficient way? since the while thing i thought up is crap 🙁 and i really cant think of another way. my knowledge about arrays is really crap... i looked at php.net regarding arrays, it didnt make much sense to me on how to put it to use for me 🙁

maybe you can help out 🙂
Thanks!

[leatherback]

Hi,

You should probably write a recursive function for this. Last week a fewe threads dealt with this. Search the forum for recursive, tree, menu

[ajdegans]

hey there,

thanks for you reply. i took some time to look at topics here...

ifound this one...
http://www.phpbuilder.com/board/showthread.php?s=&postid=10633599#post10633599

i seems it kinda does what i want, but ive got 3 queries...
one for categories, onefor subcategories and one for products in both categories and subs...

im gonna try something on the code you gave in that topic but as of yet im not sure how to do this... 🙁
maybe its easier to merge both the categories tables and add a field to say if its a subcategory or not?

thanks in advance

[ajdegans]

i now have this code:

 
$menuquery = "SELECT * FROM zc_categorie WHERE parent='$id' ORDER BY volgorde ASC"; 
$result=mysql_query($menuquery) or die('An error occured retrieving data'); 
$listing = Array(); 

while($row = mysql_fetch_array($result)) {  

  	$listing[]=array("cat_id"        => $row[id],  

                     "cat_name"      => $row[categorie], 
                     "cat_parent"    => $row[parent],
					 "cat_volgorde"  => $row[volgorde]     

                    ); 
  	} 

function display_tree($parent, $level, $array, $path) { 
	foreach ($array AS $node)
		if($node[cat_parent]==$parent) { 
    		$html .= "<a class='menu".$level."' href='#'>".$node[cat_name]."</a>";
			$html .= display_tree($node[cat_id], ($level + 1), $array, $path); 
    	} 
	return $html; 
} // end display list 

// Now use it 
$menu = display_tree(0, 0, $listing, $baseurl);

but it show nothing, no error no output.

i also tried putting in - print "$menu"; and print_r "$menu"; but that dont work either

[raspberryh]

Well I think this is how I would do it....

$kaart="SELECT * FROM zc_kaart WHERE id='$id'";
$kaart2=mysql_query($kaart) or die("Could not query database.<br>".mysql_error());

// Highest level of catagories
while($top_level=mysql_fetch_array($kaart2)){

 // add top level to array (a.k.a. '1st thing' in your example)
 $finaloutput[] = $top_level["kaart"];

 // now add the levels that are under that top level (a.k.a. 'categorie' in your ex)
 $cat="SELECT * FROM zc_categorie WHERE parent='" . $top_level["id"] . "'";
 $cat2=mysql_query($cat) or die("Could not query database.<br>".mysql_error());

 // go through each 2nd level category
 while($categorie = mysqlfetcharray($cat2)){

  // add this second level
  $finaloutput[] = "- " . $categorie["kaart"];

  // get all subcategories under this level (a.k.a., 'item' in your example)
  $prod="SELECT * FROM zc_menu WHERE kaart='" . $categorie["id"] . "'";
  $prod2=mysql_query($prod) or die("Could not query database.<br>".mysql_error());

  // now add those 'items' to the output
  while($item = mysqlfetcharray($prod2)){
   $finaloutput[] = "-- " . $item["kaart"];
  }
 }
}

echo "<pre>";
print_r($finaloutput);
echo "</pre>";

Hope that helped...

Heather

[ajdegans]

IT WORKS!
Using this code:

 
$id=$_GET['id'];

$kaart="SELECT * FROM zc_kaart WHERE id='$id'"; 
$kaart2=mysql_query($kaart) or die("Could not query database.<br>".mysql_error()); 

// Highest level of catagories 
while($kaart3=mysql_fetch_array($kaart2)){ 

// add top level to array (a.k.a. '1st thing' in your example) 
$finaloutput[] = "<i>" . $kaart3["kaart"] . "</i>"; 

// now add the levels that are under that top level (a.k.a. 'categorie' in your ex) 
$cat="SELECT * FROM zc_categorie WHERE parent='" . $kaart3["id"] . "'"; 
$cat2=mysql_query($cat) or die("Could not query database.<br>".mysql_error()); 

// go through each 2nd level category 
while($cat3 = mysql_fetch_array($cat2)){ 

	// add this second level 
	$finaloutput[] = "- <b>" . $cat3["categorie"] . "</b>"; 

	// producten 
	$product1="SELECT * FROM zc_menu WHERE kaart='" . $kaart3["id"] . "' AND categorie='" . $cat3["id"] . "'"; 
	$product1_2=mysql_query($product1) or die("Could not query database.<br>".mysql_error()); 

	// now add those 'items' to the output 
	while($product1_3 = mysql_fetch_array($product1_2)){ 
		$finaloutput[] = "-- " . $product1_3["omschrijving"]; 
	} 
} 

// producten 
$product2="SELECT * FROM zc_menu WHERE kaart='" . $kaart3["id"] . "' AND categorie=''"; 
$product2_2=mysql_query($product2) or die("Could not query database.<br>".mysql_error()); 

// now add those 'items' to the output 
while($product2_3 = mysql_fetch_array($product2_2)){ 
	$finaloutput[] = "- " . $product2_3["omschrijving"]; 
} 
}  

echo "<pre>"; 
print_r($finaloutput); 
echo "</pre>";  

i have now this output:

 
Array
(
    [0] => diner //highest level
    [1] => - voorgerechten //sublevel
    [2] => -- Tortillas met kaas en augurk //item
    [3] => -- gebraden kipfilet met slagroom //item
    [4] => - Salade met sla //item
    [5] => - Kip met wortel //item
    [6] => - pannekoek met aubergine //item
    [7] => - shoarma met thousand eiland dressing //item
)
//the pieces with //item etc. are not shown offcoarse :)

i now have to figure out a way to output it nicely 🙂

[ajdegans]

$i=0;
while($finaloutput[$i]) {
	printf($finaloutput[$i]);
	print "<br>";
	$i++;

}

does the trick 🙂

*thanks all for their input

[ajdegans]

one question though, im playing with the code to squeeze it in a table... but when ever i use something like this:

$finaloutput[] = "<tr><td width='75%'>" . $product2_3["omschrijving"] . "</td><td><i>&euro;" . $product2_3["prijs"] . ",-</i></td></tr>\n";

the code gets stuck on the 'width' variable

i get this error:

Warning: printf(): Too few arguments in /var/www/virtual/2sisters/kaart.php on line 69

line 69 is: printf($finaloutput[$i]);

[edit]
this only occurs when i use the % just width='75' works alright...
[/edit]

ne1 has a idea?

[Weedpacket]

[man]printf[/man]expects the first argument to be a format pattern, and then the values to insert into the pattern.