Store variable from SQL Table to use later using PHP

Question

I would like to be able to store a variable from a column to use to update a variable in another column.

Here is my code:

        $sqlPlaceNumber = "SELECT @locationPlace := `$locationPlace` FROM `$className` WHERE `Vehicle` = `$vehicleName` ";
        $results = mysqli_query($con, $sqlPlaceNumber) or die(mysqli_error($con));

        $sqlUpdate = "UPDATE `$className` SET `$location` = 31-`@locationPlace` WHERE Vehicle = `$vehicleName`";
        mysqli_query($con, $sqlUpdate) or die(mysqli_error($con));

Here is my table

I would like to store the value of column "Colby, WI Place" to use to subtract it from 31 to get the value for column "Colby, WI"

31-"Colby, WI Place" = new value for "Colby, WI"

Do you *really* have columns called `Colby, WI Place` and `Colby, WI`? — GMB, Apr 15 '20 at 20:59
@GMB yes, cause I am an amateur coder and this is the way I came up with that worked for me best with my expertise. I just don't know how to get this to work using PHP. I can run this query in MyPHPAdmin and it works, but not using PHP. — Brent Yaron, Apr 15 '20 at 21:05
then please read https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php because of sql injection. And do it all in one stemant only there is no need for two — nbk, Apr 15 '20 at 21:09

score 0 · Accepted Answer · answered Apr 15 '20 at 21:09

It looks like you want:

update mytable set `Colby, WI` = 31 - `Colby, WI Place` where vehicle = ?

There is no need to use an intermediate variable, you can do this what you want with a single update statement.

Also I would strongly recommend using column names that do not contain special characters (they should be made of alphanumeric characters and underscores only), so you don't need to worry about quoting them.

Finally: you do want to use parameterized queries to make your code safer and more efficient. You can have a look at this post for more information.

Store variable from SQL Table to use later using PHP

1 Answers1