0

I wanna add an extra column to my database and give it the name price_23-05-2019.

How do I put this into a working query?

I have this right now, which is clearly not working:

  $date = date("d-m-Y");

  $query = 
  "ALTER TABLE `products_05_2019`
  ADD price_'.$date.' DECIMAL(7,2)";

($result = mysqli_query($link, $query))
Improve this question
9
  • 5
    Question is valid but really makes me question your database design. In 20 years I've never had a schema where I added columns from code. Does the MySQL user have permission to alter the table? Ideally your applications connection would not have those rights. Commented May 23, 2019 at 20:49
  • 3
    What @ficuscr says, don't do it. That should be data. Commented May 23, 2019 at 20:52
  • 3
    I agree with @ficuscr, there are probably much better ways to do what you are trying to do. This screams "bad database design" to me. Commented May 23, 2019 at 20:52
  • 5
    @Jerooney You shouldn't need a column for every date; that's the problem. Commented May 23, 2019 at 20:55
  • 2
    @Jerooney It's better to have more rows than more columns. For example, say you have a table products which contains all the info about topics, except for the prices (That's what it looks like you are trying to do). This products table has a primary key, make another table prices with the columns product_key, price, date. Now for each date, add a new row to the prices table that is linked to the primary key from products, this way you can query the database and pull all the prices for a specific product. Commented May 23, 2019 at 20:59

2 Answers 2

Reset to default
2

You really shouldn't have separate columns for each date. There should just be a date column, with the date as the value, and a separate row for each date.

But if you have to do it this way, here's how to solve it.

If you use - in a column name, you have to enclose the name in backticks.

  $date = date("d-m-Y");

  $query = 
  "ALTER TABLE `products_05_2019`
  ADD `price_$date` DECIMAL(7,2)";

  $result = mysqli_query($link, $query);

But it would probably be better to use _ instead of -.

  $date = date("d_m_Y");

  $query = 
  "ALTER TABLE `products_05_2019`
  ADD price_'.$date.' DECIMAL(7,2)";

  $result = mysqli_query($link, $query);
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Now at least I'm clear on why it probably didn't work for OP.
0

As @ficuscr says above, you may want to have a look at the design of your database so you don't have to create columns from code.

Anyway, what I use to do when I have a column name depending on code is create a new variable and then include it into the query:

  $date = date("d-m-Y");

  $column_name = 'price_'.$date;
  $query = "ALTER TABLE `products_05_2019` ADD `$column_name` DECIMAL(7,2)";
Improve this answer

4 Comments

While I applaud you for finding an answer to the question, I believe this whole question should be frame challenged so OP can learn why this is not a good idea and to explain the correct way to handle the situation.
I have a large database with products. I want to keep track of the prices of every day. So I wanna run this code every day, download the updated database with new prices, add a column with the price of that day and put the data in there. How else would you suggest to do that?
@Jerooney Check my comment on the question
@Jerooney Create a table with 4 columns: id, product, price and date

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.