As I've tried to describe in the title I have an issue in selecting rows from a MYSQL database depending on the id of another drop down list on the same page. I have only been using mysql and php for 2 months or so now and need help.
I have a table of categories with the below headers.
|id | name | parent_id|
There are parent categories, with a parent_id of 0. And Sub categories with the id of the parent as their parent_id, to a maximum depth of 1 child category. For example:
Software Development is a parent category with id = 18 and parent_id = 0. PHP Developer is a subcategory which has id = 30 and parent_id = 18.
I have a drop down list where I can select the category I work in as follows:
$p_query = "SELECT * FROM categories WHERE parent_id = 0 ORDER by id ASC";
$p_result = mysqli_query($con, $p_query) or die(mysqli_error($con));
$categories ='';
while($p_row = mysqli_fetch_assoc($p_result))
{
$categories .='<option class="option" value="p::'.$p_row['id'].'">' .$p_row['category_name'].'</option>';
}
<select name="categories[]" class="categories form-control" id="categories" style="width:100%" multiple>
<?php echo $categories;?>
</select>
This is working, no problem. However, when I try to get a second drop down list to show the possible categories whom have their parent_id as the id of any selected parent category I retrieve a drop down list with 'No Search Results found'. The code below is what I am using :
$subcategories ='';
while($p_row = mysqli_fetch_assoc($p_result))
{
$c_query = "SELECT * FROM categories WHERE parent_id = ".$categories['id']." ORDER by id ASC";
$c_result = mysqli_query($con, $c_query) or die(mysqli_error($con));
while($c_row = mysqli_fetch_assoc($c_result))
{
$subcategories .='<option class="option" value="c::'.$c_row['id'].'">' .$c_row['category_name'].'</option>';
}
}
<select name="subcategories[]" class="categories form-control" id="subcategories" style="width:100%" multiple>
<?php echo $subcategories ?>
</select>
Is there something that I am missing? As a relative beginner to both PHP and MYSQL, I would be very appreciative of any help or advice.
There is nothing wrong with your second query to retrieve sub-categories based on their parent_id so you're good there. You can test easily like so:
SELECT * FROM categories WHERE parent_id = 1 ORDER by id ASC
How are you providing the value to $categories['id'] as there is nothing in your code creating this array with an 'id' index? Further you already are using a variable called $categories as a string so you shouldn't re-use that variable name without good reason.
Since it appears you want to populate the second multiple select box with subcategories based on the selection of the first you will need to use some javascript+AJAX to submit the second query and write the results in the second selector element.
Using some jquery should help a bit. Try these examples and you'll get the idea.
myselect.php which contains the interface for selecting the category:
<?php
$con = mysqli_connect("host", "username", "password", "database");
$p_query = "SELECT * FROM categories WHERE parent_id = 0 ORDER by id ASC";
$p_result = mysqli_query($con, $p_query) or die(mysqli_error($con));
$categories ='';
while($p_row = mysqli_fetch_assoc($p_result))
{
$categories .='<option class="option" value="p::'.$p_row['id'].'">' .$p_row['category_name'].'</option>';
}
?>
<html>
<head>
<script
src="https://code.jquery.com/jquery-3.1.1.min.js"
integrity="sha256-hVVnYaiADRTO2PzUGmuLJr8BLUSjGIZsDYGmIJLv2b8="
crossorigin="anonymous">
</script>
</head>
<body>
Shift or Ctrl + Click to pick more than one<br />
<form id="categoryform" method="POST">
<select name="categories[]" class="categories form-control" id="categories" style="width:100%" multiple>
<?php echo $categories;?>
</select>
</form>
Here's what it contains<br />
<form method="POST">
<select name="subcategories[]" class="categories form-control" id="subcategories" style="width:100%" multiple DISABLED>
</select>
</form>
<script>
$(document).ready(function() {
$('#categories').click(function(){
$('#subcategories').children().remove().end();
var data = $('#categoryform').serialize();
$.post("mysubselect.php", data).done(function(data){
var response = JSON.parse(data);
for (var k in response){
$('#subcategories').append('<option class="option" value="c::' + response[k]['id'] + '">' + response[k]['category_name'] + '</option>');
}
});
});
})
</script>
</body>
</html>
And here's an example of the script returning data from your AJAX request.
mysubselect.php:
<?php
$con = mysqli_connect("host", "username", "password", "database") or die(mysqli_error());
$result = array();
foreach ($_POST["categories"] as $k => $v) {
$category_token = explode('::', $v);
$category_id = mysqli_real_escape_string($con, $category_token[1]);
$query = mysqli_query($con, "SELECT * FROM categories WHERE parent_id = " . $category_id . " ORDER BY id ASC") or die(mysqli_error());
while($r = mysqli_fetch_assoc($query)){
$result[] = $r;
}
}
print json_encode($result);