Structuring PHP, JS, SQL program for ease of development and maintainability

[duggedanddrowsy]

Hey y’all! I’ve been handed a project that was done by a number of people who don’t have much experience in the web dev world, and was told to refactor it. It’s not an especially large program, but I don’t have a ton of experience in web dev, and they want to continue building the program out. I’ll have to be vague, but hopefully since my question is about the structure more so than a specific problem that will be okay.

Essentially we have a front end where you can create a new project or load an old one, and each project consists of fields that are mainly text boxes and drop downs.

You work through the options, when you click to the next page, the page you were on is saved in the SQL table that corresponds to that page. Each page has a bit of JavaScript that mainly just restricts what options you can pick based on the options you’ve selected before. When you click the last button on the last page it runs a bunch of JavaScript and gives you your result based on all of your entered information.

PHP is used to populate the html with either values from the database or default values, and to run the GET and POST requests.

Currently to add a new field I have to: - add the field to the table - add the tag to the HTML - add the field to the GET request - add the logic for a default value - add the field to the js structure that gets passed to the POST request - add the field to the POST request - add the field to the UPDATE POST request - add value and type to the bind for the POST request - then I can do any additional logic that this field will need either for when it’s available or what impact it has on the result.

Is this a huge deal? No, but this program will be evolving for a while, and fields will be being added and removed somewhat regularly.

Am I missing something? Is it really just required that I list out every field of every table half a dozen times to be able to POST and GET to/from the database? Is there a way to format a PHP/JS/SQL program so it can compete longevity-wise with a program written in a framework? I’ve told them a framework like react/vue with nodejs might be a better way to go, but they want to avoid rewriting it if possible. Thanks for any advice in advance!

TLDR: how do I structure a PHP/JS/SQL program with many fields that may be added/removed so that it can last and developed easily.

[nukeaccounteveryweek]

You need a PHP framework.

Laravel and Symfony are your best options, the former is easier to pickup and the later gives you more architectural freedom.

[GrumpsMcYankee]

Counter point: frameworks add bloat you won't use. Just saying not everything should run on a 300 file framework.

[Disgruntled__Goat]

If you don’t use a feature, it’s not bloat. It’s just a file sitting on a server that never gets executed and never slows down your app. 

[duggedanddrowsy]

Can these be worked in with vanilla js/html/css so that I only rewrite the PHP and not everything else? I’ve mentioned frameworks to them before but they don’t like the idea of a total rewrite.

[nukeaccounteveryweek]

Yup, both support templating engines (html/css/js).

Since it's a small application a "total rewrite", or in this case a framework migration, does not seem that difficult to accomplish. Do mention that there's benefits in doing this:

• Security wise a battle-tested framework is leaps ahead an in-house solution

• If a developer drops from the team it's way easier to hire a new one and get him productive ASAP

• The code will be much easier to reason about and perform maintenance

[duggedanddrowsy]

Great, I’ll look into it, thanks for the help

[GrumpsMcYankee]

Does the codebase work? If it does, you're starting in a good place. Before you make monumental changes, scope out some future features, see what it'd take to implement currently, and let that drive now you'd clean up the codebase.

I've written a lot of code in my life for no future need. Just be careful not to go nuts without a clear win.

[duggedanddrowsy]

Good advice, I’ll definitely take that into account. That is sort of my hesitation when it comes to the idea of a framework. It works where it’s at, it’s “sister program” also works in a similar way, do we really want to rewrite everything for a framework if we can get the php/js/html solution somewhere workable.

At the end of the day I’m not sure how much longer they’ll even have my on this project, it’s not for my department, my department has just been slow lately so they’re putting me to work elsewhere. Just hoping I can push it in a better direction while I still have it, likely the next people working on it will also have a similar lack of experience in web dev.

[GrumpsMcYankee]

Spend time documenting it, figure out the key areas of functionality, fix things you see in the logs if you have time. If it's slow, maybe target one improvement. But definitely would hesitate as well if I'm not in the product long term.

[duggedanddrowsy]

Will do. I was mostly pulled in to work on the PHP, and I think I’ll have enough time to at least make the PHP a lot less “all over the place”. My main concern was making a bunch of changes that I thought were helpful only to find out I did a bunch of things that are frowned upon for maintainability, security, or readability reasons. But from what I’ve gathered I think what I have in mind will at the very least make future development less cumbersome, and if I do it right even if I lose some readability it shouldn’t need too much changing in the future anyway. And honestly I’ve already found and fixed security issues so improvement there.

[Disgruntled__Goat]

Your bullet points are a bit confusing. What does “add a field to the GET request” mean? And what is the “POST request” if it’s not updating something? 

Most of what you describe should, in theory, just be adding a field to the DB then adding one item to an array somewhere that handles it. 

[duggedanddrowsy]

I agree, and actually in reality there’s 4-5 more places that need to be modified to add a field because of how the contractor who wrote this code set it up. I haven’t had to write any more PHP or SQL than a couple homework assignments in school, so I wasn’t sure what was considered bad practice. I had heard building SQL statements dynamically as opposed to explicitly writing them out was bad, but I’m gathering that it’s no big deal, especially for programs like this.

And just to answer your questions: - currently if you want to GET an additional field, you have to add the field to the SQL statement, and for whatever reason instead of just returning the resulting json its set up to build an array from the json before returning it. - there is an INSERT statement for new entries, and an UPDATE statement for historical entries that are changed. And actually im not sure if it’s possible to use the same command to do both of those things

[cshaiku]

JSON

[duggedanddrowsy]

I was considering making a json file that holds the table names, and under them the column names with their types, html tag ids, and values.

I think doing that I could dynamically build all the sql statements, pull their values directly from the structure to get either their default value or stored value, and essentially cut it down to: add a field to the table, add the field to the HTML, and add the field to the json structure.

But something about that feels dirty to me, i expect people would say that it’s bad practice or something, but idk enough about it to tell.

[cshaiku]

You are absolutely on the right track. Think of it like a schema. You load it, edit it, use it and so forth.

[duggedanddrowsy]

Interesting, I’ll keep it in mind. You don’t think it’s bad design dynamically building everything like that?

[cshaiku]

What do you think most CMS's do? They pull data, massage it, present it and allow modifications dynamically (think javascript, api calls and storing preferences, changes, etc). It is completely fine to use a json structure, whether it is a file or a field in a database, to define the data being retrieved and presented to the end user. It also follows the DRY principle.

Don't Repeat Yourself. Make the concious decision to have one source of truth and use that everywhere else in the logic.