Insert query

I have shortened the query I had (plus it had PHP variable names)

But basically I want to do the below

INSERT INTO mytable (id,cost,tax,total) VALUES (‘1’,‘$200.00’,‘10’,SUM(SUBSTRING($200.00, 2, 11)+10) AS sumval);

I want to run a sum when I insert in to the table for an extra column called total. The PHP passes the dollar sign so I need to use substring

But I know the above doesn’t work

Does anyone know of a better way to write this

please explain what this means

i have a feeling you should be doing the summing in php

The insert should create the additional total column, I don’t know if there is an advantage to doing this in PHP

you cannot create a column with INSERT

you must use ALTER TABLE ADD COLUMN

Hello jodyschwartz, I think I understand your problem.

Your code isn’t working because firstly of the grave accents (`) you’re using around the values in your SUM() function (making MySQL think they are attribute or relation names). You’re also attempting to alias the value generated in the area where the value should be (not sure why?).

The SUBSTRING function does also not require the third argument, so you can safely emit that. There’s also no need to use the SUM() function - you can have expressions where values are expected. The following should therefore work:


INSERT INTO `mytable` (`id`,`cost`,`tax`,`total`) VALUES ('1', '$200.00', '10', SUBSTRING('$200.00', 2) +10);

Remember that integer values do not need quotes to encase them (you seem to be unnecessarily quoting the values for the id and tax columns).

I’d also suggest that you do not store the dollar sign with the amount in the cost attribute. It’s inconsistent with the tax and total attribute values, and more to the point, it is an output formatting detail (thus, it has no place in the database). (Had you has done this, then there would have been no need to have used MySQL’s SUBSTRING() method.) The 2 decimal place value in the 200.00 instance is also unnecessary. You can easily add formatting like that using PHP’s printf() function:


$cost = 200;
printf('$%.2f', $cost);