HTML form with CRUD operations for MySQL database when columns are >300

Question

I am working on a science project, which would store (and further do other functions) some values for CD Markers (these are around 350). My table structure is like -

_id
diagnosis
remarks
CD1
CD2
CD3a
CD3b
CDw4
...
CD348
CD349
CD350

I know, these many columns in a table are usually not recommended and some would say to use multiple tables and store values in rows. I have tried those, and those are not matching my requirement, so for the time-being I am stuck with this option only.

Now, if the columns were less I could have created a simple HTML form and used PHP, MySQLi for CRUD operations. But with these many columns it would be time consuming task to type HTML code individually. So, I am searching for a way if I can generate a HTML form using some PHP Loop to enumerate table's columns with one textbox, and further create the insert query using similar loop.

My sample insert query is like (copied from my phpmyadmin)-

INSERT INTO `fcm_markers` (`_id`, `diagnosis`, `remarks`, `CD1a`, `CD1b`, `CD1c`, `CD1d`, `CD1e`, `CD2`, `CD3`, `CD3d`, `CD3e`, `CD3g`, `CD4`, `CD5`, `CD6`, `CD7`, `CD8a`, `CD8b`, `CD9`, `CD10`, `CD11a`, `CD11b`, `CD11c`, `CD11d`, `CDw12`, `CD13`, `CD14`, `CD15`, `CD16`, `CD16b`, `CD17`, `CD18`, `CD19`, `CD20`, `CD21`, `CD22`, `CD23`, `CD24`, `CD25`, `CD26`, `CD27`, `CD28`, `CD29`, `CD30`, `CD31`, `CD32a`, `CD32b`, `CD32c`, `CD33`, `CD34`, `CD35`, `CD36`, `CD37`, `CD38`, `CD39`, `CD40`, `CD41`, `CD42a`, `CD42b`, `CD42c`, `CD42d`, `CD43`, `CD44`, `CD45RA`, `CD45RB`, `CD45RC`, `CD45RO`, `CD46`, `CD47`, `CD48`, `CD49a`, `CD49b`, `CD49c`, `CD49d`, `CD49e`, `CD49f`, `CD50`, `CD51`, `CD52`, `CD53`, `CD54`, `CD55`, `CD56`, `CD57`, `CD58`, `CD59`, `CD60a`, `CD61`, `CD62E`, `CD62L`, `CD62P`, `CD63`, `CD64a`, `CD65`, `CD65s`, `CD66a`, `CD66b`, `CD66c`, `CD66d`, `CD66e`, `CD66f`, `CD68`, `CD69`, `CD70`, `CD71`, `CD72`, `CD73`, `CD74`, `CD75`, `CD75S`, `CD77`, `CD79a`, `CD79b`, `CD80`, `CD81`, `CD82`, `CD83`, `CD84`, `CD85A`, `CD85C`, `CD85D`, `CD85E`, `CD85F`, `CD85G`, `CD85H`, `CD85I`, `CD85J`, `CD85K`, `CD86`, `CD87`, `CD88`, `CD89`, `CD90`, `CD91`, `CD92`, `CD93`, `CD94`, `CD95`, `CD96`, `CD97`, `CD98`, `CD99`, `CD99R`, `CD100`, `CD101`, `CD102`, `CD103`, `CD104`, `CD105`, `CD106`, `CD107a`, `CD107b`, `CD108`, `CD109`, `CD110`, `CD111`, `CD112`, `CD113`, `CD114`, `CD115`, `CD116`, `CD117`, `CD118`, `CD119`, `CD120a`, `CD120b`, `CD121a`, `CD121b`, `CD122`, `CD123`, `CD124`, `CD125`, `CD126`, `CD127`, `CD129`, `CD130`, `CD131`, `CD132`, `CD133`, `CD134`, `CD135`, `CD136`, `CD137`, `CD138`, `CD139`, `CD140a`, `CD140b`, `CD141`, `CD142`, `CD143`, `CD144`, `CDw145`, `CD146`, `CD147`, `CD148`, `CD150`, `CD151`, `CD152`, `CD153`, `CD154`, `CD155`, `CD156a`, `CD156b`, `CD156c`, `CD157`, `CD158a`, `CD158b1`, `CD158b2`, `CD158d`, `CD158e1/e2`, `CD158f`, `CD158g`, `CD158h`, `CD158i`, `CD158j`, `CD158k`, `CD159a`, `CD159c`, `CD160`, `CD161`, `CD162`, `CD163`, `CD164`, `CD165`, `CD166`, `CD167a`, `CD168`, `CD169`, `CD170`, `CD171`, `CD172a`, `CD172b`, `CD172g`, `CD173`, `CD174`, `CD175`, `CD175s`, `CD176`, `CD177`, `CD178`, `CD179a`, `CD179b`, `CD180`, `CD181`, `CD182`, `CD183`, `CD184`, `CD185`, `CD186`, `CD191`, `CD192`, `CD193`, `CD194`, `CD195`, `CD196`, `CD197`, `CDw198`, `CDw199`, `CD200`, `CD201`, `CD202b`, `CD203c`, `CD204`, `CD205`, `CD206`, `CD207`, `CD208`, `CD209`, `CD210a`, `CDw210b`, `CD212`, `CD213a1`, `CD213a2`, `CD215`, `CD217`, `CD218a`, `CD218b`, `CD220`, `CD221`, `CD222`, `CD223`, `CD224`, `CD225`, `CD226`, `CD227`, `CD228`, `CD229`, `CD230`, `CD231`, `CD232`, `CD233`, `CD234`, `CD235a`, `CD235b`, `CD236`, `CD236R`, `CD238`, `CD239`, `CD240`, `CD241`, `CD242`, `CD243`, `CD244`, `CD245`, `CD246`, `CD247`, `CD248`, `CD249`, `CD252`, `CD253`, `CD254`, `CD256`, `CD257`, `CD258`, `CD261`, `CD262`, `CD263`, `CD264`, `CD265`, `CD266`, `CD267`, `CD268`, `CD269`, `CD270`, `CD271`, `CD272`, `CD273`, `CD274`, `CD275`, `CD276`, `CD277`, `CD278`, `CD279`, `CD280`, `CD281`, `CD282`, `CD283`, `CD284`, `CD286`, `CD288`, `CD289`, `CD290`, `CD292`, `CDw293`, `CD294`, `CD295`, `CD296`, `CD297`, `CD298`, `CD299`, `CD300a`, `CD300c`, `CD300e`, `CD301`, `CD302`, `CD303`, `CD304`, `CD305`, `CD306`, `CD307a`, `CD307b`, `CD307c`, `CD307d`, `CD307e`, `CD309`, `CD312`, `CD314`, `CD315`, `CD316`, `CD317`, `CD318`, `CD319`, `CD320`, `CD321`, `CD322`, `CD324`, `CD325`, `CD326`, `CD327`, `CD328`, `CD329`, `CD331`, `CD332`, `CD333`, `CD334`, `CD335`, `CD336`, `CD337`, `CD338`, `CD339`, `CD340`, `CD344`, `CD349`, `CD350`, `CD351`, `CD352`, `CD353`, `CD354`, `CD355`, `CD357`, `CD358`, `CD359`, `CD360`, `CD361`, `CD362`, `CD363`) VALUES (NULL, 'neutro', 'wbc', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1');

As one can see, the CD numbers are not regular (some have suffix a,b,c; some have prefix w, some numbers do not exist), so I can't just loop CD1-CD150 or something like this.

Any time you find yourself with enumerated columns (above 2, say), you can be sure that your schema is suboptimal. If normalisation is not an option, then neither is a relational database — Strawberry, Jun 16 '18 at 07:13
@Strawberry I am a medical doctor, not a professional programmer,will you please suggest me any alternate way of doing this or any article to read about it. Thanks. — Dr. Atul Tiwari, Jun 16 '18 at 16:53

icydemon · Accepted Answer · 2018-06-16T06:21:27.570

Bottomline: Use an ORM, it will save you a ton of time and effort, but if you are going for a pure approach:

Check this on how to Get table column names in mysql?

Then, with a simple foreach you can create the form fields (sample follows);

foreach ($result as $key => $value) {
    echo "<input type=\"text\" name=\"{$value}\">";
}

The trick to saving data from this form is to name your HTML fields (<input name="something">), exactly the same as your database fields. Then, when POSTing the data to PHP, it can do the ditry work for you (sample code below).

$data = $_POST;
$keys = array(); $values=array();
foreach ($data as $key => $value) {
    array_push($keys, $key);
    array_push($values, $value);
}
$keys = implode(',', $keys);
$values = implode(',', $values);

$query = "INSERT INTO fcm_makers ({$keys}) VALUES ({$values})";

How can I learn about ORM and how to use it in my project (sorry, I am a medical doctor, not professional programmer, I learn coding only in my time) — Dr. Atul Tiwari, Jun 16 '18 at 17:02

HTML form with CRUD operations for MySQL database when columns are >300

1 Answers1