Want to display the sub category for the selected options through MySQL and PHP

sahebyuvrajs · October 7, 2020, 1:16am

I want to show the sub category for the selected option, I tried using an approach, it was successful, but due to the foreach loop, the category names used to repeat it self as I used to select the options. I am trying to figure this out since 2 weeks already. Help will be really appreciated on the said issue. Please help me out on this one.

Database Table Entries:
As you can see below, I have created two tables, one for the menu items and the other one for the sub categories. They are kind of interlinked as the ID of the Sub Category Table is same as the SubCategoryID of the Menu Table , so I can sort it out and categorize it further in the future.

Menu Table
ID  SubCategoryID   ItemName           ItemDescription
1   1               Bottled Water      Test
2   1               Aerated Beverages  Test
3   2               Virgin Mojito   

Sub Category Table
ID  CategoryID   SubCategoryName
1   1            Customary Selections
2   1            Mocktails
3   1            Fresh Fruit Juices

Index.php:
This is the front end on where the options are displayed, as per the user’s selection, the value of the item selected will be posted forward to the Sample.php. The values posted are, SubCategoryID , ItemName and the ItemDescription .

<?php
    include_once 'dbh.php';
    $query = "SELECT * FROM menu;";
    $result = mysqli_query($conn, $query);
?>

<form action="generate.php" method="post">
    <?php while($row = mysqli_fetch_array($result)) { ?>

    <label><?php echo $row['ItemName'];?></label>
    <input type="checkbox" value="<?php echo $row['SubCategoryID'] . "-" . $row['ItemName'] . "-" . $row['ItemDescription'];?>" name="data[]" />

    <?php } ?>
    <button type="submit" class="btn btn-md btn-outline-primary btn-block" style="border-radius: 0px;">Generate PDF</button>
</form>

Sample.php:
The data values posted, are further exploded, so I can style and align the individual values, and all this is enclosed inside the foreach loop.

<div class="root">
    <?php foreach ($data as $value) { list($item_sub_category, $item_name, $item_description) = explode('-', $value, 3); { ?>
  
    <div class="main">
        <span><?php echo $item_name;?><br/></span>
        <span><?php echo $item_description;?></span>
    </div>
  
    <?php }} ?>
</div>

My Method:
I was able to get the category according to the assignment, but as it was enclosed within the foreach statement, it was getting repeated every time along with the menu items. Below is the code which I used in the Sample.php.

<?php
    include_once 'dbh.php';
    $id = $item_sub_category;
    $query1 = "SELECT * FROM sub_category WHERE sub_category.id = '$id'";
    $result1 = mysqli_query($conn, $query1);
    $rows1 = mysqli_fetch_array($result1);
?>

<span><?php echo '---' . $rows1['SubCategoryName'] . '---';?><br/></span>

Output Received:

Output Expected:

Thanks a lot for your time.

droopsnoot · October 7, 2020, 8:56am

If I understand the question correctly, I think I’d do something like

select * from menu inner join sub_category on sub_category.id = menu.subcategory_id order by menu.subcategory_id, itemname

This should give every item from the menu table, include the subcategory name, and sort them in order of subcategory and then item name.

I would then loop through the results like this pseudo-code:

last-subcat = ""
while (row = fetch()) {
  if row(subcat) <> last-subcat {   
    display subcat name
    }
  display menu item details
  last-subcat = subcat id
}

That would allow you to detect when the subcategory id changes, and display the new subcategory name.

Of course, you should only retrieve the columns you need rather than using select *, and I’ve guessed on some of the column names.

sahebyuvrajs · October 7, 2020, 11:33am

Hey mate,
Thanks a lot for giving your time.

I am stuck actually, I am doing as you said but I think I am doing it wrong, I know its silly to ask, you already helped me now, quite a lot, but please have a look whenever you have time.

$last_subcat = '$item_sub_category';
while ($rows1 = mysqli_fetch_array($result1)); {
if ($rows1['SubCategoryName'] == $last_subcat) {
echo $rows1['SubCategoryName'];
}
echo $item_name;
//last-subcat = subcat id
}

droopsnoot · October 7, 2020, 11:39am

This won’t help:

while ($rows1 = mysqli_fetch_array($result1)); {
                                             ^

The semi-colon closes the while loop, so all the stuff inside your curly-braces only executes after the loop is done.

Also, this line

$last_subcat = '$item_sub_category';

isn’t doing what I expect you want it to. It is assigning the text string $item_sub_category to the variable called $last_subcat. I expect you want to assign the value of $item_sub_category, in which case either drop the single-quotes, or use double-quotes - the former is more appropriate here. It actually doesn’t alter things, unless your first subcategory happened to have that name. I’d normally assign it as an empty string, because you want to force the first subcategory to display.

And

//last-subcat = subcat id

don’t comment this out - you need to be able to compare the “new” subcategory to the one on the previous row in the loop.

If that’s not it, can you describe what the problem is, and show your full code?

sahebyuvrajs · October 7, 2020, 11:59am

Yes surely, I’ll do that, as I tried as you said, I am getting a bit different output, I am not much good with php so I am very much sure I’m missing something. Please have a look.

            <?php
            $last_subcat = "";
            while ($rows1 = mysqli_fetch_array($result1)) {
              if ($rows1['SubCategoryName'] > $last_subcat) {   
                echo $rows1['SubCategoryName'];
                }
              echo $item_name;
              $last_subcat = $item_sub_category;
            }
            ?>

My full code

<div class="root">
    <?php foreach ($data as $value) { list($item_sub_category, $item_name, $item_description) = explode('-', $value, 3); { ?>
    <div class="main">

            <?php
                include_once 'dbh.php';
                $id = $item_sub_category;
                $query1 = "SELECT * from menu INNER JOIN sub_category ON sub_category.id = menu.subcategoryid ORDER BY menu.subcategoryid, itemname";
                $result1 = mysqli_query($conn, $query1);
                $rows1 = mysqli_fetch_array($result1);
            ?>

            <?php
            $last_subcat = "";
            while ($rows1 = mysqli_fetch_array($result1)) {
              if ($rows1['SubCategoryName'] > $last_subcat) {   
                echo $rows1['SubCategoryName'];
                }
              echo $item_name;
              $last_subcat = $item_sub_category;
            }
            ?> 

    </div>
    <?php }} ?>
</div>

Output which I am receiving when I am selecting the “Bottled Watter” and “Aerated Beverages”.

Please have a look, and thanks again

droopsnoot · October 7, 2020, 12:13pm

First of all, what does this line do?

 <?php foreach ($data as $value) { list($item_sub_category, $item_name, $item_description) = explode('-', $value, 3); { ?>

What is in $data, and how does it relate to the output on the browser?

Next,

 $result1 = mysqli_query($conn, $query1);
                $rows1 = mysqli_fetch_array($result1);

I don’t think the second line, the fetch, should be there. You do that as part of your loop - all you achieve here is to lose the first result.

When you say “selecting”, where does this happen? I don’t see anything in your code that applies any choices to anything, it’s just retrieving everything. I suspect I am missing something from the original question here. Your “Output Expected” screen suggests that you want a list of all items in the menu table, ordered by subcategory, with the subcategory name displayed at the top of each one.

Oh, I just noticed:

$last_subcat = $item_sub_category;

this needs to be the subcategory that you’re retrieving from the database, so I imagine it’s in the $rows1 array. Nothing in your loop changes the value of that variable, or of $item_name for that matter, so they aren’t the correct variables to use.

sahebyuvrajs · October 7, 2020, 12:27pm

Actually it takes the input from the user in index.php

<?php
    include_once 'dbh.php';
    $query = "SELECT * FROM menu;";
    $result = mysqli_query($conn, $query);
?>

<form action="generate.php" method="post">

    <?php while($row = mysqli_fetch_array($result)) { ?>

    <label><?php echo $row['ItemName'];?></label>
    <input type="checkbox" value="<?php echo $row['SubCategoryID'] . "-" . $row['ItemName'] . "-" . $row['ItemDescription'];?>" name="data[]" />

    <?php } ?>

    <button type="submit" class="btn btn-md btn-outline-primary btn-block" style="border-radius: 0px;">Generate PDF</button>
</form>

Then it posts the data through generate.php, as it generates a pdf with html2pdf php script, and sample.php is being used as a template where I explode the data as I wanted to submit multiple values through one “name” property.

By multiple values I meant ItemName then the ItemDescription and SubCategoryID of the menu item, so I can sort it out and group them by category. As I have two tables, one for menu items and one for sub categories so I need to compare by the input for the menu item selected and the subcategoryid of the item, then match it to the id of the sub_category table and display the sub category name of the items selected as a whole.

Here is the screenshot of the index.php page, where the input is being selected.

droopsnoot · October 7, 2020, 12:25pm

Yes, the more I refer back the more I think I’ve missed part of this, so I apologise for the short trip down the garden path.

sahebyuvrajs · October 7, 2020, 12:24pm

Please mate let me know if I confused you in any way.

droopsnoot · October 7, 2020, 12:32pm

Maybe the easiest way to deal with the problem is, in your index.php, include the subcategory name in the value of each checkbox, as you currently do with the subcategory ID. That way, when you receive the $data array into generate.php, you already have the name. In fact, unless you need it somewhere, you could put the name instead of the ID.

That would be where the query with the INNER JOIN comes into play, to retrieve the subcat name for each menu item. You then just need to alter your list() if you’ve added the name.

<?php
    include_once 'dbh.php';
    $query = "SELECT * FROM menu INNER JOIN subcategory on subcategory.id = menu.subcat_id;"; // check column names
    $result = mysqli_query($conn, $query);
?>

<form action="generate.php" method="post">

    <?php while($row = mysqli_fetch_array($result)) { ?>

    <label><?php echo $row['ItemName'];?></label>
    <input type="checkbox" value="<?php echo $row['SubCategory.subcategoryname'] . "-" . $row['ItemName'] . "-" . $row['ItemDescription'];?>" name="data[]" />

    <?php } ?>

    <button type="submit" class="btn btn-md btn-outline-primary btn-block" style="border-radius: 0px;">Generate PDF</button>
</form>

sahebyuvrajs · October 7, 2020, 12:36pm

I was able to get the category according to what I assigned, but as it was enclosed within the foreach statement, the subcategory was getting repeated every time along with the menu items. Below is the code which I used in the Sample.php.

<?php
    include_once 'dbh.php';
    $id = $item_sub_category;
    $query1 = "SELECT * FROM sub_category WHERE sub_category.id = '$id'";
    $result1 = mysqli_query($conn, $query1);
    $rows1 = mysqli_fetch_array($result1);
?>

<span><?php echo '---' . $rows1['SubCategoryName'] . '---';?><br/></span>

So instead of the output above, I want the following output.

For example, as a user has selected, Bottled Watter and Aerated Beverages, as they both fall in the same sub category, instead of showing the sub category name everytime along with the items selected, I want to group them under one sub category heading.

Please have a look at the above images, I need to categorize the options selected, thats it.!

droopsnoot · October 7, 2020, 12:40pm

To make it so that you can only put the subcategory name once, you will need to make sure that your $data array is sorted in subcategory order. You can either change the query in index.php to sort it, or sort the array when it gets into generate.php.

Once you have that, you can go back to my pseudo-code that checks for a change in subcategory id or name, to decide whether to display it. So maybe it would be:

// $data is the incoming array
// sort it by subcategory id
// foreach through the sorted array
// if the subcategory changed, display the new subcat name
// display the item
// end of loop

As you still need to retrieve the subcat name, I still think it might be easier to pass that through in your form values, and sort the array then too. I find adding ORDER BY on a query much easier than sorting an array.

sahebyuvrajs · October 7, 2020, 12:43pm

Thanks alot, this seems like it will surely work out but I just don’t know where to start from, how to sort the data, as I am exploding the values, and getting a bit confused, please if you can help with the piece of code, if its possible, as you have already helped alot.

droopsnoot · October 7, 2020, 12:47pm

I think that if you just add ORDER BY subcategoryid on the end of the query in your index.php, that will do it. You can of course also sort by the item name, but the subcategory must come first. You can easily check that by just looking at the order they appear in.

sahebyuvrajs · October 7, 2020, 1:24pm

So this is the query I am using in index.php along with the following statement.

$query = "SELECT * FROM menu INNER JOIN sub_category ON sub_category.ID = menu.SubCategoryID ORDER BY menu.SubCategoryID;";

<input type="checkbox" value="<?php echo $row['SubCategoryName'] . "-" . $row['ItemName'] . "-" . $row['ItemDescription'];?>" name="data[]"/>

And the following code, which I am using in the sample.php.

<div class="root">
    <?php foreach ($data as $value) { list($item_sub_category, $item_name, $item_description) = explode('-', $value, 3); { ?>

    <div class="main">

        <span><?php echo $item_sub_category;?><br /></span>
        <span><?php echo $item_name;?><br /></span>
        <span><?php echo $item_description;?></span>

    </div>

    <?php }} ?>
</div>

I am getting the same, repeated categories, if I am selecting bottled water and aerated beverages, the category is being displayed twice, even after both the items fall under the same category.

But after using your query, it has made the operation whole lot easier there is now use of now to declared the id and to find the subcategory base on the id selected, all that is beig done in the index.php. Very efficient method of doing things.

Just help me with the repeating categories as it falls under the for each statement. So any efficient solution for that? I really appreciate your time.

sahebyuvrajs · October 7, 2020, 1:55pm

I am just not able to understand the pseudo code, as I have already initialized the variable for category name as $item_sub_category when I am exploding it in the sample.php. How should I proceed with the pseudo code. Just need you help with that. Thanks again mate.

droopsnoot · October 7, 2020, 4:59pm

You will, because you display the subcategory name for every line in the array, regardless of whether it changed or not.

What I meant is this:

<div class="root">
<?php 
$last_cat = "";  // ** NEW LINE

foreach ($data as $value) {
 list($item_sub_category, $item_name, $item_description) = explode('-', $value, 3); { 
?>

    <div class="main">

<?php
  if ($last_cat <> $item_sub_category) { ?>
        <span><?php echo $item_sub_category;?><br /></span>
    <?php
    } // close the if() clause for change of sub-cat
    ?>

    <span><?php echo $item_name;?><br /></span>
    <span><?php echo $item_description;?></span>

    </div>

    <?php
 $last_cat = $item_sub_category;  // NEW LINE
 }
} ?>
</div>

We create a variable $last_cat before the loop opens, to store the value of the previous sub-category, so we can react when it changes.
Inside the loop, we only display the sub-category if it’s not the same as the previous one. On the first iteration, the previous sub-cat was blank, so it displays the first name. On the second and rest of the iterations, it shouldn’t display it if it isn’t different from the previous.
Just before the end of the loop, we set the $last_cat variable to be the subcategory from the current row of data, so we can check again next time round.

This should result in the sub-cat name only being displayed if it is different to the previous one. Of course, it will only work if the data is sorted by sub-cat.

(You’ll have to check that I’ve opened and closed the PHP tags properly, I’m not a big fan of jumping in and out of PHP and I’d probably echo all that html code if I were doing it. So I might have made a mess of things, but I’m sure you get the idea. I should probably use “previous” instead of “last”, too.)

sahebyuvrajs · October 7, 2020, 5:37pm

Bro thanks a lot, you are the best. Its working great. Its working as I wanted. Thank you for your time.