How can this work? is it possible?

[phence]

Hey guys i've been battling this problem for over a week now. and i still can't get it to work correctly.

I have a script that pulls out categores and sub categories from a mysql database. The output of this script looks similar to this:
Computers >> Software
Computers >> Software >> Games
Computers >> Software >> Programs
Computers >> Hardware
Computers >> Hardware >> Hard drives
Computers >> Hardware >> RAM
Computers >> Hardware >> Accessories

Now i want to pull this output into a <select> box drop down menu.

I can't seem to get it right. The script ends up timming out or showing one category repeatly around the page.

Here is the Original script: (displays on regular HTML doc)

<?php

function categories ($id, $depth) {
    if ($id == 'NULL')
        $query = "SELECT * FROM categories WHERE Parent IS NULL";
    else
        $query = "SELECT * FROM categories WHERE Parent = '" . $id . "'";

$result = mysql_query($query);
if (mysql_num_rows($result) < 1)
    return;
while($row = mysql_fetch_array($result)) {
    categories2 ($row, $depth);        
}
}
//note that I had to split this into 2 functions 
//because $depth will not restart on each loop
function categories2 ($row, $depth) {
    echo($depth . $row['Name'] . "<br>");
    $depth = $depth . $row['Name'] . " > ";
    categories ($row['ID'], $depth);
}

// db data
$hostName = 'localhost';
$username = 'user';
$password = 'pass';
$databaseName = 'db';

//connect to DB
if(!($connection = @ mysql_pconnect($hostName, $username, $password)))
		die("Could Not Connect to database");
if (!mysql_select_db($databaseName, $connection))
		showerror();

categories("NULL", "");

Here's the script i've been work'n on: (display in a <select> box)

<?php

function categories ($id, $depth, $connection) {
    if ($id == 'NULL')
        $query = "SELECT * FROM categories WHERE Parent IS NULL";
    else
        $query = "SELECT * FROM categories WHERE Parent = '" . $id . "'";

$result = mysql_query($query);
if (mysql_num_rows($result) < 1)
    return;
while($row = mysql_fetch_array($result)) {
    categories2 ($row['ID'], $row['Name'], $depth, $query, $connection);        
}
}
// Display the select
?><SELECT name="categories"><?
function categories2 ($cat_id, $cat_name, $depth, $query, $connection) {
	if (($result = mysql_query ($query, $connection)))
	{
    	while ($category = mysql_fetch_array($result))
		{	
			?><option name="<?php echo $category['ID']; ?>"><?
			echo($depth . $cat_name);
			?></option><?
			$depth = $depth . $cat_name . " > ";
    		categories ($category['ID'], $depth, $connection);
		}
	}else
        echo "umm.. somthing messed up";

}
?></select><?

// db data
$hostName = 'localhost';
$username = 'user';
$password = 'pass';
$databaseName = 'db';

//connect to DB
if(!($connection = @ mysql_pconnect($hostName, $username, $password)))
		die("Could Not Connect to database");
if (!mysql_select_db($databaseName, $connection))
		showerror();

// run it
categories("NULL", "", $connection);

?> 

If anyone could help me get this working right i really really would appreiciate it. Thank you!

[dustbuster]

If you run your script and view the source do you have matching select and option opening and closing tags? If you have mismatched tags you can run into problems.

Normally I don't comment on coding style as everyone has their own ways of doing things but I think you may run into problems in the future.

I reccomend using an external file to keep your mysql data connection calls.

//connect.php
$hostName = 'localhost';
$username = 'user';
$password = 'pass';
$databaseName = 'db';
if(!($connection = @ mysql_pconnect($hostName, $username, $password)))
        die("Could Not Connect to database");

//file.php
global $connection;
require ("connect.php");
//do something
mysql_close();

This way if you need to change the db, pass, or user it can be done in one place. It isn't really neccesary to provide a $connection to mysql calls because if this is missing mysql will defualt to the last connection used.

//some suggested changes
function categories2 ($cat_id, $cat_name, $depth, $query) {
echo "<SELECT name=\"categories\">\n";

if ($result = mysql_query ($query))
{
    while ($category = mysql_fetch_array($result))
    {    
    echo "<option name=\"".$category['ID']."\">";
    echo($depth . $cat_name);
    echo"</option>\n";
    $depth = $depth . $cat_name . " > ";        
    categories ($category['ID'], $depth);
    }
}else
    echo "umm.. somthing messed up";
echo "</select>\n";
}

Hope this helps

[phence]

hey thanks for the reply, but it's still not working, i changed $connection to global and used your code.. still nothing.

i normally do use and include file for my database info.. just didn't make one for it yet.

i'd really appreciate anyone who might help get this working..
Am i going about this the right way?
or is there a better approach?

[dustbuster]

Can you post your table structure? Specifically the categories table. And can you provide some sample data? That would help figure it out.

[phence]

This is the table structure...
Table: categories

ID | Name | Parent

1 | Cars | NULL
2 | Ford | 3
3 | Coupe | 1
4 | Constrution | NULL
5 | Equipment | 4
6 | Concrete | 5
7 | Boom | 6

8 | Hatchback | 1

if this was the database of categories, this is what would print out if you would run the category script...

Construction
Construction > Equipment
Construction > Equipment > Concrete
Construction > Equipment > Concrete > Boom Pumps
Cars
Cars > Coupe
Cars > Coupe > Ford
Cars > Hatchback

the above output is what i would like to print out in a select box so that a user could select an exact category.

Thanks for helping..

[dustbuster]

Would something like this work?
I think it will but haven't tested it.

//---------------------------------------------------------------
function categories($parent,$parentstring){
 $query="SELECT * FROM categories where parent = '$parent'";
 $result=mysql_query($result);
 if (mysql_num_rows($result)>0){
 while ($row=mysql_fetch_assoc($result)){
     $tempparentstring=$parentstring;
     $tempparentstring=$tempparentstring." >> ".$row['Name'];
     echo "<option value=\"".$row['ID']."\">";
     echo $tempparentstring;
     echo "</option>\n";
     categories($row['ID'],$tempparentstring);
 }
 }
}
//---------------------------------------------------------------

//some code
require ("connect.php");
echo "<select name=\"Categories\">\n";
 $query="SELECT * FROM categories where parent IS NULL";
 $result=mysql_query($result);
  while ($row=mysql_fetch_assoc($result)){
   echo "<option value=\"".$row['ID']."\">";
     echo $row['Name'];
     echo "</option>\n";
    categories($row['ID'],$row['Name']);
  }

  echo "</select>\n";

[phence]

hey, thanks... i made those changes, but i am getting a:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/www/user/test/creat_lst.php on line 50

heres line 50:
if (mysql_num_rows($result)>0){

thanks for helping..

[dustbuster]

Sorry my goof

$result=mysql_query($result);
should read
$result=mysql_query($query);

[phence]

well the select is populated, but is not showing the full structure of the categories...
Construction > Equipment > Concrete > Boom Pumps

it's only showing the main category...
so if there are 4 construction categories.

Construction
Construction > Equipment
Construction > Equipment > Concrete
Construction > Equipment > Concrete > Boom Pumps

the output inside the <select> is (according to the database example i gave you earlier in the post) ...
Cars
Cars
Cars
Cars
Construction
Construction
Construction
Construction

I did look at the source code and compared the select name to the category ID in the category database and it looks like they match up..
Only problem now is that the user does't no exactly which one to pick...

i think that the depth var is either ressetting or not resetting... that is why before i hade two functions...

any idea how to minipulate this to get it working right??

[dustbuster]

The following code works with the sample data you provided earlier:

<?
//---------------------------------------------------------------
function categories($parent,$parentstring){
 $query="SELECT * FROM categories where parent = '$parent'";
 $result=mysql_query($query);
 if (mysql_num_rows($result)>0){
 while ($row=mysql_fetch_assoc($result)){
     $tempparentstring=$parentstring;
     $tempparentstring=$tempparentstring." >> ".$row['Name'];
     echo "<option value=\"".$row['ID']."\">";
     echo $tempparentstring;
     echo "</option>\n";
     categories($row['ID'],$tempparentstring);
 }
 }
}
//---------------------------------------------------------------

//some code
//---------------------
//connect.php goes here but this is easier right now
global $link;
	$link=mysql_connect( "localhost", "", "" );
	if (! $link )
		die( "Couldn't connect to MySQL" );
	mysql_select_db( "new",$link )
		or die ("Couldn't open db: ".mysql_error() );
//---------------------


echo "<select name=\"Categories\">\n";
 $query="SELECT * FROM categories where parent IS NULL";
 $result=mysql_query($query);
  while ($row=mysql_fetch_assoc($result)){
   echo "<option value=\"".$row['ID']."\">";
     echo $row['Name'];
     echo "</option>\n";
    categories($row['ID'],$row['Name']);
  }

  echo "</select>\n";


?>

Here is the structure of the table I used.

# phpMyAdmin MySQL-Dump
# version 2.4.0
# [url]http://www.phpmyadmin.net/[/url] (download page)
#
# Host: localhost
# Generation Time: Mar 30, 2003 at 10:05 PM
# Server version: 3.23.53
# PHP Version: 4.2.3
# Database : `new`
# --------------------------------------------------------

#
# Table structure for table `categories`
#

CREATE TABLE categories (
  ID int(11) NOT NULL auto_increment,
  Name varchar(20) default NULL,
  Parent int(11) default NULL,
  PRIMARY KEY  (ID)
) TYPE=MyISAM;

#
# Dumping data for table `categories`
#

INSERT INTO categories VALUES (1, 'Cars', NULL);
INSERT INTO categories VALUES (2, 'Ford', 3);
INSERT INTO categories VALUES (3, 'Coupe', 1);
INSERT INTO categories VALUES (4, 'Construction', NULL);
INSERT INTO categories VALUES (5, 'Equipment', 4);
INSERT INTO categories VALUES (6, 'Concrete', 5);
INSERT INTO categories VALUES (7, 'Boom', 6);
INSERT INTO categories VALUES (8, 'Hatchback', 1);

I know it works though.

[phence]

aww... dude! you are awsome! thank-you so much!!
i've been working on this for 2 weeks!! finally it's working!
thank-you again!

peace.

[dustbuster]

no problem
good luck

[komlos]

dustbuster:

Your code is awesome! I also use it to list my categories in a select box. However just for curiosity is it possible to make it more simple? Without a function, only with loops? (I'm a newbie at PHP)
Thanks

[phence]

the function method is the simplest. it would be way too hard and slower with just loops.

[dustbuster]

It would be very hard to do this kind of code with loops. The method I showed is called recurrsion. It basically has a function that calls itself and stops calling itself when a condition is met. You can use recurrsion for traversing directories or for complex problems like we discussed before.