PHP -> mySQli -> Cats and Subcats

Looks good. Thanks for translating the database. It looks very good. Here are some thoughts:

From your screen shot, it seems the steps for a user are these: (1) type the name of the new subcategory they want to add, (2) select the category they want to add the subcategory to in the drop down list box, (3) click the "Add new subcategory" button.

In the button's onClick event handler you will call a Javascript function that will (4) make sure the text box isn't empty, and contains only text. If the entry passes those tests the Javascript function will send the new subcategory name to a PHP file, possibly using AJAX, which will (5) check to see if the subcategory is already in the database and (6) either send back a message that the subcategory is a duplicate or insert it into the database.

To make things easier for the user, you could have the user select the category first, then show then a list of the current subcategories for that category on the screen. I.e., reverse steps (1) and (2). That way, when they enter the new one they will have a list of the current ones in from of them, so it will be more difficult for them to duplicate or partially duplicate an existing one. I don't know who will have access to the add screen, but if it's the general public one of the hardest things is to keep people from entering close equivalents.

E.g., if you were asking for their country, and they were American, they could enter US, USA, America, US of A, United States, etc., etc. That's why most sites use drop-down list boxes whenever possible, so they have to pick just one version. But here you need to allow new input, and so you might think about how you keep garbage out of your database. Again, I don't know the users, or if this is just for administrators, but what some sites do is put take the new subcategory in as a suggestion, and enter it into the database only after an admin reviews and approves it. Another reason for such screening is that if it's a public site there are those who cannot be expected to abide by the usual standards of decency.

P.S., I haven't had a chance to look at the php files you sent. But I will.

Edis, just got your database and php pages connected and working on my computer. Sorry, it didn't take long, but I had to wait to today to do it. I've added categories, and new subcategories for categories (both yours and my new ones), and everything works fine. Congrats!!

I thought that after I looked at your pages I would understand your first question better. But sorry, I still don't know what you mean by:

• How to call via php and mysqli query Categories and Subcategories in a Tree for the Navigation menu. I know how to style it and everything else just need the function. -- what is the Navigation menu? what do you mean by "in a Tree". Nothing comes to mind for either of these.

For your second question: what logic to use if I have an post article and I want to add it to a subcategory. Do I need to create two table rows category_ID, subcategory_ID in the posts table. Or is there a better solution, example: to save category_ID and subcategory_ID in one row and then explode it. Thanks.

Answer: Neither, you can do it with just subcategory_ID. Because each subcategory_ID maps to just one category_ID, you don't need both. Here's the SQL for getting back the category and subcategory for a post:

SELECT post_title, category_name, subcategory_name, s.category_ID, s.subcategory_ID FROM posts AS p
INNER JOIN subcategories AS s ON s.subcategory_ID = p.subcategory_ID
INNER JOIN categories AS c ON s.category_ID = c.category_ID  WHERE post_ID = $post_ID

Not sure what you meant, though, in the second question by "I want to add it to a subcategory". I was thinking that each time a post was inserted the person posting would be asked to pick a category and a subcategory. And then, each time your app records a post, you would use SQL like this:

INSERT INTO posts (post_title, subcategory_ID) VALUES ("....", subcategory_ID)

As I said, you don't need to keep the category_ID for the category the poster chooses, as you have the subcategory_ID, and that's enough. Assuming, of course, that you never, ever, change a subcategory's category. But if you plan on doing that you will have bigger problems.

Re an earlier idea, here's the code to display the current categories on the new_category.php page:

<! -- Display current categories -->
<p>Current Categories:</p>
<p id="cat_list">
    <ul>
<?php
    $sql = "SELECT category_name FROM categories ORDER BY category_name";
    $categories = mysqli_query($conn, $sql);                            
    while($red = mysqli_fetch_assoc($categories)) {
        echo "<li>" . $red['category_name'] . "</li>";
    }
?>  
    </ul>
</p>

And here's the code to display current subcategories by category on the new_subcategory.php page:

<! -- Display current subcategories by category -->
<p>Current Subcategories by Category:</p>
<p id="cat_list">
    <ul>
<?php
    $sql_cat = "SELECT category_name, category_ID FROM categories ORDER BY category_name";
    $categories = mysqli_query($conn, $sql_cat);                            
    while($red = mysqli_fetch_assoc($categories)) {
        echo "<li>" . $red['category_name'] . "</li>";
        echo "<ul>";
        $sql_subcat = "SELECT subcategory_name FROM subcategories WHERE category_ID = " . $red['category_ID'];
        $subcategories = mysqli_query($conn, $sql_subcat);                          
        while($red2 = mysqli_fetch_assoc($subcategories)) {
            echo "<li>" . $red2['subcategory_name'] . "</li>";  
        }
        echo "</ul>";
    }

?>  
    </ul>
</p>

I think you may have already done this, but just in case I misunderstood. Also, if you put this code at the bottom of the pages, the listings will update automatically to display the new entries.

jcorum, if you have seen the prtsc. I have categories and subcategories. I want a option select structure like on the example bellow:

Categorie1

• subcat 1
• subcat 2 Categorie2
• subcat 1
• subcat 2 Categorie3 Categorie4 Categorie5

Now I am getting something like this, what I don't want:

Categorie1

• subcat 1 Categorie1
• subcat 2 Categorie2
• subcat 1 Categorie2
• subcat 2 Categorie3 Categorie4 Categorie5

Ah, ha! It's the output that's the problem. The language was a bit of a hurdle, but I think I've got it. The issue is that each time you echo an li with a subcategory you get a li for the category above it, but you just want the category once for each set of subcategories.

If that's right, one way you can solve the problem is to split it up. First query your database just for the distinct (unique) categories. And then, loop though that recordset and for each category create a list item showing the category and then fire off a query to get the subcategories for that category and, in an inner loop, create a list item for each of them. This is a fairly common master-detail design pattern, and I have done it in other contexts, e.g., listing the students in each class of a set of classes, but, interestingly, not yet for a select list. I was echoing table table rows and table details, but it should work just as well for list items.

Hope this helps.

Yes that's the right logic but I don't know how to code it, it's a lill to complex for my level of knowledge :)

Thank you jcorum. I have done it.That's the final code and I got what i wanted.

http://s16.postimg.org/s3faw03jp/podkat.png

            <div class="form-group">
                <label for="izaberi_kategoriju">Kategorija</label><br>
                <select name="izaberi_kategoriju" id="kat">

<?php $upit = "SELECT kat_ID, kat_naziv FROM kategorija"; //SQL to get categories $izaberi_kat = mysqli_query($konekcija, $upit);
mysqliGreska($izaberi_kat); while($red = mysqli_fetch_assoc($izaberi_kat)){ //loop through categories $kat_id = $red['kat_ID']; //for each category $kat_naziv = $red['kat_naziv']; echo "<option value='$kat_id'>{$kat_naziv}</option>"; //create option //SQl for inner loop, using $kat_id from outer loop $upit2 = "SELECT podkategorijaID, kategorijaID, podkat_naziv FROM podkategorija WHERE kategorijaID = $kat_id"; $izaberi_kat2 = mysqli_query($konekcija, $upit2);
mysqliGreska($izaberi_kat2); while($red2 = mysqli_fetch_assoc($izaberi_kat2)) { //inner loop $podkategorijaID = $red2['podkategorijaID']; $kategorijaID = $red2['kategorijaID']; $podkat_naziv = $red2['podkat_naziv']; echo "<option value='$podkategorijaID'>- {$podkat_naziv}</option>"; //create option } } ?> </select> </div>

Now, I need some logic thinking. For Categories and Subcategories

I have two tables Categories and Subcategories. Table structure for Categories looks like this: http://s23.postimg.org/ffmtflhbf/phpmyadmin_cats.png

For Subcategories looks like this: http://s24.postimg.org/pvb3a9csl/phpmyadmin_subcats.png

In my admin panel i have the options to add categories and subcategories.

For adding Category names structure looks like this: http://s8.postimg.org/qwkiwvxcj/new_category.png

For adding Subcategory names structure loooks like this: http://s27.postimg.org/r9dw16ixu/new_subategory.jpg

Am I on the right way?

Not sure why you have a subcategoryID field in the Category table. Currently it is filled with zeros. But if any category can have multiple subcategories what would you put here? If it really is the subcategoryID, you should delete this field.

As a side note, you should be consistent in how you name your ID fields. If it ends in ...ID in one table it should end in ...ID in every table. Or if you want it to be ..._id, then it should be that way in every table. If you are consistent in the way you name fields and tables you will save yourself a lot of heartache later! Also, make sure that your the ID field in each table is a PrimaryKey. Also, make sure that the foreign key in the many table is spelled the same as the primary key in the one table when you are building one-many relationships. If the primary key in the Category table is kat_id, then it should be kat_it in the Subcategory table, not kategorijaID.

You have Sport as a category, with an ID of 40. But then in the Subcategory table you have Sport, along with Culture and Drustmo, as being in category 37, not 40?? Further, why have Sport in both tables if Sport is a Category?

Let's take another example. You have two categories: Book and Movie. The categoryID for Book is 10 and the categoryID for Movie is 20. In the Subcategory table you have two Book subcategories: fiction and non-fiction. Each of those have their own subcategoryID: say 100 and 101, and each has a categoryID of 10. In the same table you have two Movie subcategories: comedy and action/adventure. The subcategoryID of comedy is, say, 200, and the subcategoryID of action/adventure is 201. And each has a categoryID of 20. That way, when you join the tables, you get the right subcategories with each category.

Try running this SQL in myPhpAdmin SQL window:

SELECT k.kat_ID, k.kat_naziv, p.kategorijaID, p.podkat_naziv FROM kategorija AS k INNER JOIN podkategorija AS p ON k.kat_ID = p.kategorijaID ORDER BY k.kat_id, p.kategorijaID

and see if you get the subcategories lined up with the right categories.

For the query UP I am getting this result which is I think Ok: http://s14.postimg.org/equ9ll1gh/image.png

jcorum, I have now recompiled my code for simple use just for easier understanding.

Three tables categories - > subcategories - > posts

Example image: http://s28.postimg.org/4a7hk2p3h/image.png

Category table structure:

Example image: http://s8.postimg.org/7b6h4646t/image.png

Subcategory table structure and relating it to Categorie table with category_ID:

Example image: http://s12.postimg.org/xt7xtuzy5/image.png

Interface for category:

Example image: http://s29.postimg.org/x5f1icujr/image.png

Interface for subcategory and relating it to categories:

Example image: http://s17.postimg.org/631j1likv/image.png

Here is a ZIP file with code, for testing I have used bootstrap with CDN link cms.sql is also included:

ZIP file: http://hf-mont.com/cms/cms.zip

I think the structure looks now much better.

I am now stack here:

How to call via php and mysqli query Categories and Subcategories in a Tree for the Navigation menu. I know how to style it and everything else just need the function.

Second question what logic to use if I have an post article and I want to add it to a subcategory. Do I need to create two table rows category_ID, subcategory_ID in the posts table. Or is there a better solution, example: to save category_ID and subcategory_ID in one row and then explode it. Thanks.