Dynamic Dropdown Category Subcategory List in PHP MySQL using ajax — Tutsmake.COM

How to display category and subcategory?

How to display category and subcategory ? I have one table in DB. Row in this table looks something like this:

CREATE TABLE IF NOT EXISTS `category` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) NOT NULL, `parent_id` int(11) NOT NULL, `order` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM 

I want display category and subcategory like on this site: http://www.dealsdirect.com.au/c/baby-1/ (menu on the left) My try:

3 Answers 3

The below code should get you started. It’s a «recursive function» — a function that calls itself. As @Fake51 has just mentioned, it’s not very efficient, but it should work.

You’ll need some CSS to structure the list you’ve produced.

Edit: Since there’s still been no accepted answer, here’s my code modified to do it all with a single SQL query which should be much more efficient, albeit a little more confusing potentially. See how it goes for you.

You’ll first need to connect with mysql_connect() and select a database with mysql_select_db() . You should be able to convert this to work with the PDO if you want.

This is terrible. I would much rather do a single select, get array of all categories and then work with that one array. Also if you can add another field to your categories table ‘has_children’ and store value like yes/no or boolean, that would allow you to use a more efficient algorithm

Agree that repeating the query is sub-optimal. A single query building a set of arrays would be a far better method. Some form of caching would also be a smart idea as it’s probably unlikely to change often. I went with this as it’s simple to get started from, probably should’ve suggested this change though.

With a structure like that, you’ll need to first construct a query for the chosen item, then afterwards construct a query for the chosen items parent. Essentially, you’ll need a query for each level of menus you have, that should be visible.

This is the reason why a parent_id scheme of tree relationship is not very efficient. Much better to go with nested set (http://en.wikipedia.org/wiki/Nested_set_model) or materialized path (http://en.wikipedia.org/wiki/Materialized_path). If you expect to do some updating of the items, materialized path will probably be easier to deal with in the long run — I personally much prefer it, having tried both.

No true at all. You can select all rows, ideally index them by id, then traverse and build your result.

That is a waste of resources as you’ll not need all results — there can be loads of sub-category items hidden that you need no details of. Apart from that, traversing and building the result in php will be slower than doing it in the database, if you have a proper structure.

Источник

How to create nested category list from category-strings in PHP

I have a MySQL table «products» that has a column called «category». In this field you find the categorie herarchy for every product as a string. E.g.:

female / dresses / long female / dresses / short female / shoes male / shoes / sneakers / skate . 

$result = $mysqli->query("SELECT DISTINCT category FROM products ORDER BY category"); 

Any advice would be very much appreciated. Thanks!

EDIT: I get the data from several csv files, so a solution without the need of transforming the categories would be great!

a solution without the need of a second table would be great, since I get the data as csv in this format.

@AndiPower even if you would find a solution without a second table it would perform terrible bad. So spending the time in preprocessing and using Nested Tree Sets like Sanja suggested is the best way to avoid bottlenecks and other problems in future. To make the import easier you can for sure use a path to node in tree mapping as fast lookup table for import, with that it shouldn’t be a big deal to implement this even with multiple csv files as source. Here a short description of Nested Tree Sets I wrote to another question.

2 Answers 2

Just construct your tree using the categories as the node’s key. For example :

$categoryLines = array( "female / dresses / long", "female / dresses / short", "female / shoes", "male / shoes / sneakers / skate" ); function buildCategoryTree($categoryLines, $separator) < $catTree = array(); foreach ($categoryLines as $catLine) < $path = explode($separator, $catLine); $node = & $catTree; foreach ($path as $cat) < $cat = trim($cat); if (!isset($node[$cat])) < $node[$cat] = array(); >$node = & $node[$cat]; > > return $catTree; > function displayCategoryTree($categoryTree, $indent = '') < foreach ($categoryTree as $node =>$children) < echo $indent . $node . "\n"; displayCategoryTree($children, $indent . '|- '); >> $categoryTree = buildCategoryTree($categoryLines, '/'); 

Now, var_export($categoryTree) will output :

array ( 'female' => array ( 'dresses' => array ( 'long' => array (), 'short' => array (), ), 'shoes' => array (), ), 'male' => array ( 'shoes' => array ( 'sneakers' => array ( 'skate' => array (), ), ), ), ) 

and displayCategoryTree($categoryTree) will output :

female |- dresses |- |- long |- |- short |- shoes male |- shoes |- |- sneakers |- |- |- skate 

** Edit **

To get the HTML representation of the tree :

Why does it matter? This is actually better since it does not create an exceptions; meaning that leaves or nodes. they both have the «label» as keys and an array defining any potential children as value. Doing anything other than that requires extra and unncecessary checks.

I’m not a data structure geek and I don’t know what are the consequences of having no leaves in a tree so, I was just mentioning that it’d better to have an ending point in paths. Besides, extra check is just up to look at current node’s depth and that’s all, no heavy lifting. Cheers 🙂

@YanickRochon: That’s fantastic. You’re my hero and now I feel dumb. The buildCategoryTree-function is very clever. Thanks for the title-elements in the HTML! Great!

As others mentioned in comments, you’d better to use nested tree sets for this kind of categorization, but since you asked you don’t want to change the scheme of your category system and IF and only IF you’re not concerned about your run-time overload, you can use this code that I wrote.

For populating an array of categories:

 > function place($categories, $category, $counter = 0) < if($counter < count($category)) < if(!isset($categories[$category[$counter]])) < $categories[$category[$counter]] = array(); >else if($counter == count($category) - 1) < $categories[$category[$counter]][] = $category[$counter]; >// Recurse $categories[$category[$counter]] = place($categories[$category[$counter]], $category, $counter + 1); > return $categories; > // Populate categories populate(); ?> 

You can replace my foreach on $rows with your fetch while loop.

For displaying of categories:

female dresses long short 1 2 1 shoes male shoes sneakers skate 

Источник

Category Subcategory Dropdown in PHP MySQL Ajax

Dynamic category and subcategory tree dropdown list in PHP MySQL ajax. In this tutorial, we will show you how to dynamic populate categories and subcategories in the dropdown tree using PHP MySQL Ajax or How to create categories and subcategories in PHP MySQL using ajax.

Sometimes, you need to create separate tables for displaying selected subcategories based on selected category dropdowns. But in this tutorial, we will use only a single table (category and subcategory in one table) for a PHP dynamic populate dropdown list onchange of category and subcategories.

This tutorial will help you step by step on how to implement a dynamic category and subcategory dropdown list onchange in PHP MySQL using Ajax or populate the second dropdown based on the first PHP. As well as learn, how to fetch data from the database in the dropdown list in PHP using jQuery ajax.

In this PHP ajax MySQL get categories and subcategories example, we will show subcategory in the dropdown list based on selected category value in PHP using ajax from the database.

This dynamic category and subcategory dropdown list in PHP MySQL using ajax will look like:

Dynamic Category and Sub Category Dependent Dropdown List in PHP + MySQL using Ajax

• Step 1: Create Table
• Step 2: Insert Data In Table(Category and SubCategory)
• Step 3: Create DB Connection PHP File
• Step 4: Create Form And Category, SubCategory Dropdown in Form
• Step 5: Get Sub Category in Dropdown List by Category Id

Step 1: Create Table

First of all, open your database and run the following sql query to create categories table into database:

CREATE TABLE `categories` ( `id` int(11) NOT NULL AUTO_INCREMENT, `parent_id` int(11) NOT NULL DEFAULT '0', `category` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Step 2: Insert Data In Table(Category and SubCategory)

In this step, run the following sql query to insert category and subcategory into mysql database in php:

INSERT INTO `categories` (`id`, `parent_id`, `category`) VALUES (1, 0, 'General'), (2, 0, 'PHP'), (3, 0, 'HTML'), (4, 3, 'Tables'), (5, 2, 'Functions'), (6, 2, 'Variables'), (7, 3, 'Forms');

Step 3: Create DB Connection PHP File

In this step, create a file name db.php and update the following code into db.php file:

Note that, This code is used to create a MySQL database connection in PHP project.

Step 4: Create Form to Category, SubCategory Dropdown in Form

In this step, create an index.php file and update the below PHP and HTML code into index.php file.

Note that, This HTML code shows the category and subcategory dropdown list. And the PHP and ajax code of this file will dynamic populating subcategories in dropdown list based on the selected category in dropdown.

Now, update the following php mysql ajax and html form into index.php file:

         html, body < background-color: #fff; color: #636b6f; font-family: 'Nunito', sans-serif; font-weight: 200; height: 100vh; margin: 0; >.full-height < height: 100vh; >.flex-center < align-items: center; display: flex; justify-content: center; >.position-ref < position: relative; >.top-right < position: absolute; right: 10px; top: 18px; >.content < text-align: center; >.title < font-size: 84px; >.links > a < color: #636b6f; padding: 0 25px; font-size: 13px; font-weight: 600; letter-spacing: .1rem; text-decoration: none; text-transform: uppercase; >.m-b-md  
 
 
 
Category Select Category  ?> 
 
Sub Category , cache: false, success: function(result) < $("#sub-category-dropdown").html(result); >>); >); >);    
Step 5: Get SubCategory in Dropdown List by Category Id
 
Now, create a new php file named fetch-subcategory-by-category.php. This file code will fetch and show a subcategory (second dropdown) based on selected category (previous dropdown selection) in PHP.
 
So update the following php and html code into fetch-subcategory-by-category.php file:
 
Conclusion
 
In this tutorial, you have learned how to dynamic populating subcategories in dropdown list based on the selected category in PHP MySQL using ajax.
 
Author Admin
 
My name is Devendra Dode. I am a full-stack developer, entrepreneur, and owner of Tutsmake.com. I like writing tutorials and tips that can help other developers. I share tutorials of PHP, Python, Javascript, JQuery, Laravel, Livewire, Codeigniter, Node JS, Express JS, Vue JS, Angular JS, React Js, MySQL, MongoDB, REST APIs, Windows, Xampp, Linux, Ubuntu, Amazon AWS, Composer, SEO, WordPress, SSL and Bootstrap from a starting stage. As well as demo example.
 
3 replies to Category Subcategory Dropdown in PHP MySQL Ajax
 
Thank you for this code. Its working! But…how can I edit and update it using ajax. Any help would be highly appreciated. Thank you
 
Thanks for the code, i need this!
 
Источник
 
   
 
Оцените статью
 
 
 
 
 
     
    
Вам также может понравиться
 
 
Ящики с усами python
pandas.plotting.boxplot# Make a box-and-whisker plot
 
 
 
Пример использования свойства CSS table-layout.
table-layout¶ Свойство table-layout определяет, как
 
 
 
Пример использования свойства CSS table-layout.
HTML Размеры таблицы HTML таблицы могут иметь разные
 
 
 
Пример использования свойства CSS table-layout.
table-layout¶ Свойство table-layout определяет, как
 
 
   
 
 
 
Правообладателям
 
Политика конфиденциальности
 
Контакты