I recently saw a comment on StackOverflow that basically said it is a bad idea to alter tables on the fly.
In my case I have a json
file that is storing some information about some "products". One of those pieces of information tells other programs which column in a data table is related to this product. This id
is not user defined.
I have some php
functions that can add/delete products from the json
file but during these functions, a column in a table either has to be added or removed.
$sql = 'ALTER TABLE products ADD p'.$id.' VARCHAR(11)';
$stmt = $mysql->prepare($sql);
$execute = $stmt->execute();
if($execute!==false){
$build = ["name"=>$product_name, "release"=>$release_date, "details"=>$details, "id"=>$id];
array_push($products["products"],$build);
$products = json_encode($products,JSON_PRETTY_PRINT);
$file = fopen("./crm.json","w+");
fwrite($file,$products);
fclose($file);
}else{
//error message
}
Could someone explain why this may be a bad idea?
More Information:
To explain a bit more about my specific case the json file stores all of my product info. The id
of each product entry is used to indicate which column I need to be looking at in a table to see if a user is either using this product or not. Other than that, the id
is used to know which column should be dropped if a product is removed. Below is a json
sample
{
"location": "Test Location",
"products": [
{
"name": "SAMPLE PRODUCT",
"release": "2019-12-04",
"details": "RANDOM PRODUCT DETAILS",
"id": 1
},
{
"name": "SAMPLE PRODUCT",
"release": "2019-12-04",
"details": "RANDOM PRODUCT DETAILS",
"id": 2
},
{
"name": "SAMPLE PRODUCT",
"release": "2019-12-04",
"details": "RANDOM PRODUCT DETAILS",
"id": 3
}
]
}
products
table. Moreover, if you have different product records, you cannot have "one column for one product record" and a different column for another product record at the same time. The code example looks like you are trying to add columns named 1, 2 and 3 to the product tables which will actually not work since columns names cannot be just a number. Please clarify.products
table carries different information from the product information in thejson
file. The products table actually stores an identifier for a customer. When im adding a product I add a new column to myproducts
table which simply shows if that customer is using that product or not. Theid
used for the column name allows me to relate a product in thejson
file to a customers status with that product. I am still new to all of this but I appreciate you attempting to understandp
+$id
not just$id