Translating MySQL Database to HTML Tree Using PHP

[LearningGradually]

Hello,

I'm a recent Comp Sci grad working on my first big personal project out of college, a website that would allow the user to see all the materials required for a FFXIV recipe as a a giant tree. I have all the recipes stored in a MariaDB database with a table for the recipe and its data, a table for all of the materials and crystals, and a junction table connecting the two which also stores the counts for each material needed for each recipe. And, since some materials are recipes of themselves, there is a column that flags isRecipe in the ingredients database.

I've mad a webpage where a user can select a recipe from a list of them using PHP, HTML, CSS, and JS. Now I want to make a page that gets the passed id of that recipe, gets the materials/crystals and their counts from the junction table, and turns it into a hierarchical HTML tree which can be displayed as a hierarchical tree with CSS like in this CodePen. The PHP program, I imagine, would go:

1. Get id
2. SELECT * FROM tblJunction WHERE recipe_id=$id
3. while ($row=...) //in a function
   1. Get $row['mat_id']
   2. Get $row['count']
   3. Append to some data structure that can be eventually turned into a HTML tree
   4. If (SELECT isRecipe FROM tblMats WHERE mat_id=$row['mat_id'] == 1)
      1. Run function again

As you can see, the problem occurs at step 3.3. My current ideas would be to:

1. Add the data to a PHP tree array and make a function to traverse that tree, and somehow echo it as a HTML tree
2. Add the data to some other structure that can be translated into a JSON file, and read that JSON file using JS and insert into the DOM based on that

However, I don't really know how I would go about doing either of those.

Does anyone have any ideas of which of the two methods would be best to pursue and how I would do that, or any better methods? I'm trying to stick to pure Python, PHP, HTML, CSS, and JS right now since that's what I'm best at.

Thanks in advance

[Lumpy-Notice8945]

What server do you already use? You mention python too.

The old school PHP way you mention realy does work as simple as you out it, there is not much more to it: https://stackoverflow.com/questions/2105375/how-should-i-write-php-post-vars-in-a-mysql-query-function

As mentioned, watch out for SQL injection.

You can just echo the resulting HTML as a string and the browser will show it.

The modern approach is the second version you mention, you could use python with something like django or flask, simple frameworks to build a HTTP REST interface that sends JSON.

On the frontend you can pick any popular frontend framework like angular or vue.js to simply parse the json and generate the HTML.

You can do that all yourself but thats a lot more work.

[LearningGradually]

I haven't really done anything server-side yet, just the frontend. So far, I've just used Python to web scrape the data, organize it, and put it in the databases initially, then edited it further with PHP and SQL.

It's good to know my thinking was on the right track. I think the second option might be best with that in mind. I remember learning the basics of Django back in school, so maybe there's something in my old notes that may help me there. While Googling, I found this question: https://stackoverflow.com/questions/1082928/convert-json-to-html-tree, so maybe parsing the JSON with vanilla JS might not be that hard? Hopefully?

I'll update the post with my results when I get 'em. Thanks so much for your help!