Jump to content

*SOLVED* PHP + mySQL sorting problem

ludwigvb

By ludwigvb
in PHP Coding Help

 Share

Recommended Posts

ludwigvb Newbie

ludwigvb

Posted
Posted
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
Link to comment
https://forums.phpfreaks.com/topic/10480-solved-php-mysql-sorting-problem/
Share on other sites
freakus_maximus Member

freakus_maximus

Posted
Posted
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 Prolific Member

Barand

Posted
Posted
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 Newbie

ludwigvb

Posted
  • Author
Posted
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">&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>
[/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 Member

freakus_maximus

Posted
Posted
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]
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.