How to include a PHP variable inside MySQL query

Question

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))

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. — ficuscr
– ficuscr, Commented May 23, 2019 at 20:49
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. — GrumpyCrouton
– GrumpyCrouton, Commented May 23, 2019 at 20:52
@Jerooney You shouldn't need a column for every date; that's the problem. — GrumpyCrouton
– GrumpyCrouton, Commented May 23, 2019 at 20:55
@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. — GrumpyCrouton
– GrumpyCrouton, Commented May 23, 2019 at 20:59

Barmar · Accepted Answer · 2019-05-23 21:00:33Z

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);

edited May 23, 2019 at 21:00

answered May 23, 2019 at 20:58

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ficuscr Over a year ago

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

Kaiser · Accepted Answer · 2019-05-23 21:03:08Z

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)";

edited May 23, 2019 at 21:03

answered May 23, 2019 at 20:54

Kaiser

1,9951 gold badge23 silver badges29 bronze badges

4 Comments

GrumpyCrouton Over a year ago

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.

Jerooney Over a year ago

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?

GrumpyCrouton Over a year ago

@Jerooney Check my comment on the question

Kaiser Over a year ago

@Jerooney Create a table with 4 columns: id, product, price and date

Collectives™ on Stack Overflow

How to include a PHP variable inside MySQL query

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related