Jump to content


Photo

*SOLVED* PHP + mySQL sorting problem


  • Please log in to reply
4 replies to this topic

#1 ludwigvb

ludwigvb
  • Members
  • PipPip
  • Member
  • 14 posts

Posted 26 May 2006 - 04:05 AM

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:
$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();

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

#2 freakus_maximus

freakus_maximus
  • Members
  • PipPipPip
  • Advanced Member
  • 177 posts

Posted 26 May 2006 - 04:50 AM

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.

#3 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 26 May 2006 - 10:10 AM

You need a recursive function
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";

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#4 ludwigvb

ludwigvb
  • Members
  • PipPip
  • Member
  • 14 posts

Posted 26 May 2006 - 01:30 PM

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:

How to create a php - mysql script to create categories with unlimited sub categories

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

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;

I have set UTF-8 for multilanguage support

3. Now create a file and name it categories.php

Put the 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">&nbsp;';
echo catOptions(0);
echo "</SELECT>\n";
?>

</td>
  </tr>
  <tr>
    <td>&nbsp;</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>

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 config.php and put in it:
<?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";

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

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

Put this code in it:

<?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";

?>

Save in UTF- format

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

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

GOOD LUCK

Spyros

#5 freakus_maximus

freakus_maximus
  • Members
  • PipPipPip
  • Advanced Member
  • 177 posts

Posted 26 May 2006 - 01:38 PM

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]




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users