PHP SQL Syntax error adding to table 1 with table 2 value

Question

I get an error when running this php code which going to add value to table 1 based on table 2 value where table 3 value equal to 0

MY TABLE

------------------------------
id| table 1| table 2 | table 3|
-------------------------------
01| 100    | 10     | 0      |

PHP CODE

<?php
    $servername = "localhost";
    $username = "myuser";
    $password = "mypass";
    $dbname = "lol";

    // Create connection
    $conn = mysqli_connect($servername, $username, $password, $dbname);
    // Check connection
    if (!$conn) {
        die("Connection failed: " . mysqli_connect_error());
    }

    $sql = "UPDATE table table1 = table1 + table2 WHERE table 3 = 0";



    if (mysqli_query($conn, $sql)) {
        echo "Record updated successfully";
    } else {
        echo "Error updating record: " . mysqli_error($conn);
    }

    mysqli_close($conn);
    ?>

It said that I have error in my sql syntax

Error updating record: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= table1 + table2 WHERE table3 = 0' at line 1

How to achieve this with the correct sql syntax?

Oh my. You really have a table called table with columns table1 to table3? WHY???!! — juergen d, Jul 06 '15 at 19:10
possible duplicate of [When to use single quotes, double quotes, and backticks?](http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks) — Jay Blanchard, Jul 06 '15 at 19:18
@JayBlanchard nope. maybe one with "dont forget the set keyword on update" - Im hoping the table name and columns are aliases strictly for posting here. — OIS, Jul 06 '15 at 19:20
I'm hoping that too @OIS, but you're right - the whole thing is a mess. — Jay Blanchard, Jul 06 '15 at 19:26
I think we're a little lost in the syntax because it's hard to tell if you're confusing columns and tables or something entirely different. See if you can take a step back from the sample names and use realistic column / table names so people can understand what you're doing a bit better. Then explain it with more of a straight-forward story: "I need to add the sale_discount and promotion_discount columns on table sales and then..." — Palu Macil, Jul 06 '15 at 19:34

LDMJoe · Answer 1 · 2015-07-06T19:18:43.230

0

I'm assuming the table and field names are aliases. Try this...

$sql = "UPDATE `table` SET `table 1` = `table 1` + `table 2` WHERE `table 3` = 0";

You need SET for an UPDATE statement, and fields or tables with spaces in the names need to be surrounded by the backtick character.

edited Jul 06 '15 at 19:18

answered Jul 06 '15 at 19:12

LDMJoe

1,591
13
17

Jay Blanchard · Answer 2 · 2015-07-06T19:15:15.853

0

You forgot the SET directive and your column names do not match those in your query, for which you'd have to use back ticks -

 $sql = "UPDATE `table` SET `table 1` = `table 1` + `table 2` WHERE `table 3` = 0";

You really should learn about prepared statements

edited Jul 06 '15 at 19:15

answered Jul 06 '15 at 19:12

Jay Blanchard

34,243
16
77
119

PHP SQL Syntax error adding to table 1 with table 2 value

2 Answers2