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.