SOLVED PHP + mySQL sorting problem

ludwigvb · May 26, 2006

Hi,

I have made this table to store categories and subcategories for a script I'm making:
[img src=\"http://www.hostin.gr/mysqltable.jpg\" border=\"0\" alt=\"IPB Image\" /]

How can I sort it in a drop down (select) form menu like this?

cat 01
--cat_01_01
----cat_01_01_01
------cat_01_01_01_01
--------cat_01_01_01_01_01
----cat_01_01_02
cat 02
--cat_02_01
--cat_02_02
cat 03
cat 04
--cat 04_01
cat 05
--cat_05_01

I have used the code:
[code]$query="SELECT * FROM categories WHERE parent_cat_id = 0 ORDER BY cat_name";
$result=mysql_query($query);
$num=mysql_num_rows($result);

// mysql_close();

if ($num==0) {
echo '';
} else {

$i=0;
while ($i < $num) {

$cat_id=mysql_result($result,$i,"cat_id");
$cat_name=mysql_result($result,$i,"cat_name");
$root_cat_id=mysql_result($result,$i,"root_cat_id");
$parent_cat_id=mysql_result($result,$i,"parent_cat_id");

$categories_list .= '<option value="'.$cat_id.'">'.$cat_name.'';

$query02="SELECT * FROM categories WHERE parent_cat_id=$cat_id ORDER BY cat_name";
$result02=mysql_query($query02);
$num02=mysql_num_rows($result02);

if ($num02 == 0) {
    echo '';
}
else {
$j=0;
while ($j < $num02) {

$cat_id02=mysql_result($result02,$j,"cat_id");
$cat_name02=mysql_result($result02,$j,"cat_name");
$root_cat_id02=mysql_result($result02,$j,"root_cat_id");
$parent_cat_id02=mysql_result($result02,$j,"parent_cat_id");

$categories_list .= '<option value="'.$cat_id02.'">  '.$cat_name02.'';
$j++;
        }
}
$i++;
    }
}
mysql_close();[/code]

But in this way only 2 levels of categories and subcategories are shown, so I will have to repeat the code so many times as the depth of subcategories.

Any help will be really appriciated.

Thanks in advance.

Spyros

freakus_maximus · May 26, 2006

Check out my reply in this thread here:

[a href=\"http://www.phpfreaks.com/forums/index.php?showtopic=94066&st=0&p=376462&#entry376462\" target=\"_blank\"]http://www.phpfreaks.com/forums/index.php?...62&#entry376462[/a]

It's not an exact solution to what you want but should help you to modify your code to do what you want.

Barand · May 26, 2006

You need a recursive function
[code]
function catOptions ($parent, $level=0) {

    $sql = "SELECT cat_id, cat_name
            FROM categories
            WHERE parent_cat_id = '$parent'
            ORDER BY cat_name";

    $res = mysql_query($sql) or die(mysql_error());
    $str = '';
    while (list($id, $name) = mysql_fetch_row($res)) {
        $indent = str_repeat('---', $level);
        $str .= "<OPTION value='$id'>$indent $name</OPTION>\n";

           // now call itself to get subcats of this cat
          $str .= catOptions($id, $level+1);
    }
    return $str;
}

echo "<SELECT name='cat'> \n";
echo catOptions(0);
echo "</SELECT>\n";
[/code]

ludwigvb · May 26, 2006

First of all I would like to thank reakus_maximus and Barand for their replies.

Barands code is the right one. The solution has been found!!!!

Here I 'll post a some kind of tutorial of:

[b]How to create a php - mysql script to create categories with unlimited sub categories[/b]

1. Create a mySQL database
2. Create a table in it named categories (you can execute the following code in phpadmin)

[code]
CREATE TABLE `categories` (
  `cat_id` int(4) NOT NULL auto_increment,
  `cat_name` varchar(255) collate utf8_unicode_ci default NULL,
  `parent_cat_id` int(4) NOT NULL default '0',
  `root_cat_id` int(4) NOT NULL default '0',
  `sort_order` bigint(8) NOT NULL default '0',
  `cat_img` varchar(255) collate utf8_unicode_ci default NULL,
  `date_added` datetime NOT NULL default '0000-00-00 00:00:00',
  `date_edit` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`cat_id`),
  KEY `parent_cat_id` (`parent_cat_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=0;
[/code]

I have set UTF-8 for multilanguage support

3. Now create a file and name it [b]categories.php[/b]

Put the code:

[code]
<?php

include 'config.php';
?>

<HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=<?php echo $encoding; ?>" />
<title><?php echo $lang_titletag; ?></title>
<link href="css.css" rel="stylesheet" type="text/css" />
</head>
<body>

<?php

//**************************//
//    ADD NEW CATEGORY START    //
//**************************//

if ($add) { //if add button is released

// Connect to db and set encoding
    $db = mysql_connect($server_name,$user,$password);
    @mysql_select_db($database) or die( "Unable to select database");
    mysql_query("SET NAMES 'utf8'");

if ($parent_cat_id != 0){ // if a category is selected a sub category should be added

// select the parent's_id parent_id until parent_cat_id and root_cat_id is ZERO (0)
    $query=mysql_query("SELECT * FROM categories WHERE cat_id=$parent_cat_id",$db);
    $row=mysql_fetch_array($query);

$parent_cat_name = $row["cat_name"];
$parent_cat_loop = $row["parent_cat_id"];
$root_cat_loop = $row["root_cat_id"];

    while ($parent_cat_loop !=0 && $root_cat_loop !=0) { // start searching the root cat
    $query=mysql_query("SELECT * FROM categories WHERE cat_id=$parent_cat_loop",$db);
    $row=mysql_fetch_array($query);
    $parent_cat_loop = $row["parent_cat_id"];
    $root_cat_loop = $row["root_cat_id"];
    } // root cat has been found
    $root_cat_id = $row["cat_id"];

// insert subcategory to db
    $sql = "INSERT INTO categories (cat_id, cat_name, parent_cat_id, root_cat_id, sort_order, cat_img, date_added, date_edit) VALUES ('', '$cat_name', '$parent_cat_id', '$root_cat_id', '$sort_order', '$cat_img', '$date_added', '$date_added')";
    $result = mysql_query($sql);

    echo '<div align="center">'.$lang_cat_added .' '. $parent_cat_name.'</div>';

    } // subcategory has been added

else if ($parent_cat_id == 0) { // no category has been selected

// insert root category to db
    $sql = "INSERT INTO categories (cat_id, cat_name, parent_cat_id, root_cat_id, sort_order, cat_img, date_added, date_edit) VALUES ('', '$cat_name', '$parent_cat_id', '$root_cat_id', '$sort_order', '$cat_img', '$date_added', '$date_added')";
    $result = mysql_query($sql);

    echo '<div align="center">'.$lang_root_cat_added.'</div>';
    } // root category has been added
} // end if ($add)
?>

<?php

unset($root_cat_id, $parent_cat_id);

$db = mysql_connect($server_name,$user,$password);
@mysql_select_db($database) or die( "Unable to select database");
mysql_query("SET NAMES 'utf8'");

$query="SELECT * FROM categories WHERE parent_cat_id = 0 ORDER BY cat_name";
$result=mysql_query($query);
$num=mysql_num_rows($result);

if ($num==0) {
echo '';
} else {

function catOptions ($parent, $level=0) {

include 'config.php';
$db = mysql_connect($server_name,$user,$password);
@mysql_select_db($database) or die( "Unable to select database");
mysql_query("SET NAMES 'utf8'");

    $sql = "SELECT cat_id, cat_name FROM categories WHERE parent_cat_id = '$parent' ORDER BY cat_name";

    $res = mysql_query($sql) or die(mysql_error());
    $str = '';
    while (list($id, $name) = mysql_fetch_row($res)) {
        $indent = str_repeat('---', $level);
        $str .= "<OPTION value='$id'>$indent $name</OPTION>\n";

           // now call itself to get subcats of this cat
          $str .= catOptions($id, $level+1);
    }
    return $str;
}

}

?>

<form method="post" enctype="multipart/form-data" action="<?php echo $PHP_SELF?>">

<input type="hidden" name="date_added" value="<?php echo date('Y-m-d G:i:s'); ?>">

<table width="500" border="0" align="center" cellspacing="2">
  <tr>
    <td width="200" bgcolor="#CCCCCC"><?php echo $lang_cat_name; ?> </td>
    <td width="300" bgcolor="#CCCCCC"><input type="Text" name="cat_name"></td>
  </tr>
  <tr>
    <td bgcolor="#ADD8E6"><?php echo $lang_sort_order; ?></td>
    <td bgcolor="#ADD8E6"><input type="Text" name="sort_order"></td>
  </tr>
  <tr>
    <td bgcolor="#CCCCCC"><?php echo $lang_cat_img; ?></td>
    <td bgcolor="#CCCCCC"><textarea name="cat_img" wrap rows="12" cols="25" ></textarea></td>
  </tr>
  <tr>
    <td bgcolor="#ADD8E6"><?php echo $lang_cat_sub; ?></td>
    <td bgcolor="#ADD8E6">
    <?php echo "<SELECT name='parent_cat_id'> \n";
echo '<option value="0"> ';
echo catOptions(0);
echo "</SELECT>\n";
?>

</td>
  </tr>
  <tr>
    <td> </td>
    <td><br>
      <input type="Submit" name="add" value="<?php echo $lang_cat_add; ?>"></td>
  </tr>
</table>
</form>
<?php
//**************************//
//    ADD NEW CATEGORY END    //
//**************************//
?>
    </body>
</html>
[/code]

I have put some comments on it to figure out the steps

4. Save the file in UTF-8 format

5. Now create another file called [b]config.php[/b] and put in it:
[code]
<?php
@setlocale(LC_TIME, 'el_GR.UTF8');

// change the following variables according to your settings
$user="your_db_username"; // username for the database
$password="your_db_password"; // password for the database
$server_name="localhost"; // server name.. most of the times - localhost
$database="your_db_name"; // the database

$encoding="utf-8";

include "english.php";

?>
[/code]
Change what needs to be changed :)
Save in UTF-8 format

6. Create another file [b]english.php[/b] (or another language but remember to change it in [b]config.php[/b])

Put this code in it:

[code]
<?php
$lang_titletag = "Categories Administration";

// Categories

$lang_cat_added = "Category ".$cat_name." has been added under ";
$lang_cat_name = "Category name";
$lang_sort_order = "Category sorting<br>(enter number)";
$lang_cat_img = "Category image";
$lang_cat_sub = "Choose parent category";
$lang_cat_add = "Add Category";

?>
[/code]

Save in UTF- format

7. Upload, Check if it is working, let me know :)

[b]I'm using a wysiwyg editor for the image textarea[/b]
You can use a file upload input if you prefer.

GOOD LUCK

Spyros

freakus_maximus · May 26, 2006

Yikes, now I see why you need the recursive. I missed the part of your request about sorting in a "drop down menu". Glad Barand popped in with the right solution.

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]How can I sort it in a drop down (select) form menu like this?[/quote]

Sign In

SOLVED PHP + mySQL sorting problem

Recommended Posts

ludwigvb

Link to comment

Share on other sites

freakus_maximus

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

ludwigvb

Link to comment

Share on other sites

freakus_maximus

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information

Sign In

*SOLVED* PHP + mySQL sorting problem

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Archived

Important Information

SOLVED PHP + mySQL sorting problem