MySQL - Select Column 'Name' according to user's choice Where rows in Column 'id' and 'parent' equal each other

Question

When parent = 0 means category

When parent = 1 means subcategory 1 connected to category 1 (id=1)

When parent = 2 means subcategory 2 connected to category 2 (id=2)

When parent... etc until 19 categories (id=19 with parent=0)

What I need is to bring the names of subcategories in the sub-category form field according to user's choice in the category field. The category field works fine.

id      parent      name                            active
1       0           Arts & Entertainment            0
2       0           Automotive                      0
3       0           Business & Professional Serv.   1
4       0           Clothing & Accessories          0
5       0           Community & Government          0
6       0           Computers & Electronics         1
7       0           Construction & Contractors      0
8       0           Education                       0
9       0           Food & Dining                   0
10      0           Health & Medicine               0
11      0           Home & Garden                   0
12      0           Industry & Agriculture          0
13      0           Legal & Financial               1
14      0           Media & Communications          0
15      0           Personal Care & Services        0
16      0           Real Estate                     0
17      0           Shopping                        0
18      0           Sports & Recreation             0
19      0           Travel & Transportation         0
34      1           Acting Schools                  1
35      1           Aerial Photographers            1
36      1           Arcades & Amusements            1
37      1           Art Classes                     1
38      1           Art Galleries & Dealers         1
39      1           Art Schools                     1

1.This is the Query for the category field which works fine and gives us the user's choice ($judgePick)

$db->setQuery('SELECT name FROM #__professional_categ WHERE parent=0 AND active=1 ORDER BY name ASC');

2.This is the Query for the subcategory field trying to solve

$judgePick = JRequest::getVar('category');
$db = JFactory::getDBO();

$db->setQuery('SELECT `name` FROM `#__professional_categ` WHERE active = 1 AND (something here...) ORDER BY parent ASC, name ASC);

$result = $db->loadColumn();
 if(!$result){
echo "error";
} else {
    echo json_encode($result);
}

Assumption 1 - id to include ='.$db->quote($judgePick)

Assumption 2 - For parent > 0 has to be equal to id of user's choice in Assumption 1

Expected result

Subcategory field to have the names ONLY according to user's choice in category field ($judgePick) where user's choice id equals parent. In other words, e.g. Arts & Entertainment is the category (parent=0) and has (id =1) and when the user chooses it in category form field, the subcategory form field should show all names with (parent=1)

Zollie · Accepted Answer · 2019-04-29T05:57:21.700

1

What you are looking for is probably a self join:

SELECT x.name 
FROM #__professional_categ x
JOIN #__professional_categ y
  ON x.parent = y.id
WHERE y.name = ‘. $judgePick .‘
  AND x.parent = y.id
  AND x.active = 1

You can check the query on abstract sample here: http://www.sqlfiddle.com/#!9/ecc4bb/1/0

Since with the input in your code you only get the name of the chosen category, thus we have to select its id too in the table, then we can find and select the subcategory's parent id and based on that, return the subcategories' names.

In Joomla syntax, your code and query should look like this:

$jinput = JFactory::getApplication()->input;
$judgePick = $jinput->get(‘category’);

$db = JFactory::getDbo();

// Create a new query object.
$query = $db->getQuery(true);

$query
  ->select('x.name')
  ->from($db->quoteName('#__professional_categ', 'x'))
  ->join('LEFT', $db->quoteName('#__professional_categ', 'y') . ' ON ' . $db->quoteName('x.parent') .' = '. $db->quoteName('y.id'))
  ->where($db->quoteName('y.name') .' = '. $db->quote($judgePick))
  ->andWhere(array($db->quoteName('x.parent').' = '. $db->quoteName('y.id'), $db->quoteName('x.active').' = 1'), $glue = 'AND')
  ->order($db->quoteName('x.name') . ' ASC');

// Reset the query using our newly populated query object.
$db->setQuery($query);

$result = $db->loadColumn();

edited Apr 29 '19 at 05:57

answered Apr 28 '19 at 05:59

Zollie

1,171
7
14

Thank you for the solution and for minding to convert it in Joomla syntax! You are very helpful. I would never manage this on my own! One thing to mention though, is that I had to take out `$jinput = JFactory::getApplication()->input; $judgePick = $jinput->getString(‘category’);` as it was not working. By replacing with `$judgePick = JRequest::getVar('category');` it worked fine. Maybe it is a Breezingforms issue as your syntax is not the deprecated one . – GalanopD Apr 28 '19 at 15:11
@GalanopD - Thanks for your feedback! It’s good that it’s working now. At the input maybe just using `get()` instead of `getString()` would solve the issue also with getting the input value correctly with this method too: `$judgePick = $jinput->get(‘category’);` I was not testing that part, only the query. Maybe `getString()` method is filtering out something from the input value as the category names has whitespaces. I’ll test that later... – Zollie Apr 28 '19 at 15:44
I tried changing getString to get but no luck. Must be something else... – GalanopD Apr 28 '19 at 17:51
@GalanopD - I also do not have a clue, I can only guess on this, since this is how it works standardly in core Joomla too at the moment. Maybe the name `category` itself is causing an ambiguous conflict in input values at this point in this particular code. Well, only you who can figure this out there, but if it works with JRequest, then it is OK at the moment. – Zollie Apr 28 '19 at 18:52
absolutely correct. I will try to work this on my own and send feedback in case I solve it. Thanks anyway – GalanopD Apr 28 '19 at 19:04
The `order by` e.g. name is not shown above, would you mind adding it? I must be doing something wrong. Thanks! – GalanopD Apr 28 '19 at 20:16
@GalanopD - of course, I've forgotten that, no problem, i updated that. – Zollie Apr 29 '19 at 06:00

score 0 · Answer 2 · answered Apr 27 '19 at 13:39

0

What about this

$db->setQuery("SELECT name FROM #__professional_categ WHERE parent=$judgePick AND active=1 ORDER BY name ASC");

answered Apr 27 '19 at 13:39

Rahmouni Rabii

934
1
7
16

Unfortunately what you typed doesn't produce any result to me. I had tried something similar which is `$db->setQuery('SELECT name FROM #__professional_categ WHERE active = 1 AND parent ='.$db->quote($judgePick));` but it returns only the 3 active categories (parent = 0 and active =1). What I need is to produce only all the subcategories of the selected category. – GalanopD Apr 27 '19 at 15:44

MySQL - Select Column 'Name' according to user's choice Where rows in Column 'id' and 'parent' equal each other

2 Answers2