Category: Code Snippet

Recursion is a data structure by which a function is called several times till the condition which calls the function becomes true. I have seldom used it while coding but I would like to show you one particular instance where I used it to display a category level structure in a drop down.

First you need to create the table in mySQL. I have named the table as categories for the purpose of this example. You can name it anything you like.

CREATE TABLE `categories` (
`categories_id` int(11) NOT NULL auto_increment,
`categories_name` varchar(64) default NULL,
`parent_id` int(11) NOT NULL default '0',
PRIMARY KEY (`categories_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

After that run the following INSERT queries which will create some sample data for this table

insert into `categories` (`categories_id`,`categories_name`,`parent_id`) values (65,'Top Level 1',0);
insert into `categories` (`categories_id`,`categories_name`,`parent_id`) values (66,'Top Level 2',0);
insert into `categories` (`categories_id`,`categories_name`,`parent_id`) values (67,'Sub Level 1-1',65);
insert into `categories` (`categories_id`,`categories_name`,`parent_id`) values (68,'Sub Level 2-1',66);
insert into `categories` (`categories_id`,`categories_name`,`parent_id`) values (69,'Sub Level 2-2',66);
insert into `categories` (`categories_id`,`categories_name`,`parent_id`) values (70,'Top Level 3',0);
insert into `categories` (`categories_id`,`categories_name`,`parent_id`) values (71,'Sub Level 3-1',70);
insert into `categories` (`categories_id`,`categories_name`,`parent_id`) values (72,'Top Level 4',0);
insert into `categories` (`categories_id`,`categories_name`,`parent_id`) values (73,'Top Level 5',0);
insert into `categories` (`categories_id`,`categories_name`,`parent_id`) values (74,'Sub Level 2-3',66);
insert into `categories` (`categories_id`,`categories_name`,`parent_id`) values (75,'Sub Level 3-1-1',71);
insert into `categories` (`categories_id`,`categories_name`,`parent_id`) values (76,'Sub Level 2-2-1',69);
insert into `categories` (`categories_id`,`categories_name`,`parent_id`) values (77,'Sub Level 2-2-1-1',76);

Finally you need to create a PHP script and enter the following bit of code to it.

<?php
// open the database connection

function get_rec($parent, $strCategory) {
$sql = "select * from categories where parent_id = ". $parent;
$rs1 = mysql_query($sql) or die(mysql_error());
$num1 = mysql_num_rows($rs1);

if ($num1 != 0) {
while ($data1 = mysql_fetch_object($rs1)) {
$strTempCategory = $strCategory ." -> " . $data1->categories_name;
echo "<option value=". $data1->categories_id .">". $strTempCategory ."</option>";
if (get_rec($data1->categories_id, $strTempCategory) == "") {
}
}
}
else {
return "";
}
}
?>
<select name="cboCategory" size="1">
<option value="0">Select</option>
<?php
$sql_cat="select * from categories where parent_id = 0 order by categories_name asc";
$result_cat = mysql_query($sql_cat) or die (mysql_error());
while ($data_cat = mysql_fetch_object($result_cat)) {
echo "<option value='".$data_cat->categories_id."'>".$data_cat->categories_name ."</option>";
echo get_rec($data_cat->categories_id, $data_cat->categories_name);
}
?>
</select>

Save the PHP file and run it. You should be able to view a drop down on the web page which will show the data you have just inserted in a tree view.

For ease I have provided the complete PHP code as well as the SQL file which you can then run and test on your computer.

Download the PHP Recursion Example by Adeel Sarfraz