0

I would like to be able to check a to see if a value in a column of my SQL table is = 0. If true, then do something.

Here is the part of code in question:

foreach($value -> entryTable as $entry)
        {
            $pointsUpdate = $entry['entry']->points;
            $vehicleName = mysqli_real_escape_string($con, $entry['entry']->vehicle);

            $sqlLocationPoints = "SELECT * FROM `$className` WHERE `$location` = 0";
            mysqli_query($con, $sqlLocationPoints) or die(mysqli_error($con));

            if($sqlLocationPoints);
            {
                $sql = "UPDATE `$className` SET `$location` = '$pointsUpdate' WHERE Vehicle = '$vehicleName'";
                mysqli_query($con, $sql) or die(mysqli_error($con));

                $sql2 = "UPDATE `$className` SET `Total Points` = `Total Points` + '$pointsUpdate' WHERE Vehicle = '$vehicleName'";
                mysqli_query($con, $sql2) or die(mysqli_error($con));
            }

        }
Brent Yaron
  • 5
  • 3
  • 1
    Combine so: `UPDATE table SET Total_Points=Total_Points+:pointsUpdate, location=:pointsUpdate WHERE Vehicle= AND location=0`. A table per $classname was a mistake, `classname` (or number) should be a column. See also [How to prevent SQL injection in PHP](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – danblack Apr 15 '20 at 04:00
  • @danblack I am getting and syntax error with location=:pointsUpdate. Specifically it's at the : Any ideas? – Brent Yaron Apr 15 '20 at 04:52
  • 2
    @BrentYaron You need to use a prepared statement. See the link above or https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php Although with `mysqli` you'll need to use `?`, not named placeholders. – user3783243 Apr 15 '20 at 04:55
  • note column and table names can't be parameterized. so you are limited to `$pointsUpdate` and `$vehicleName`. `:pointsUpdate` was my attempt at a placeholder, but as mentioned above, it was the wrong one for `mysqli` – danblack Apr 15 '20 at 05:00
  • @danblack thank you for the information. I am still rather new to sql and forgot that I couldn't parameterize a column name. I believe I know how to get this going and will give a crack at it tomorrow. – Brent Yaron Apr 15 '20 at 05:13
  • Why are the column names dynamic? That's usually a failure of [database normalization](https://en.wikipedia.org/wiki/Database_normalization). – tadman Apr 15 '20 at 06:26
  • Note: The [object-oriented interface to `mysqli`](https://www.php.net/manual/en/mysqli.quickstart.connections.php) is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface where missing a single `i` can cause trouble. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is an artifact from the PHP 4 era and should not be used in new code. – tadman Apr 15 '20 at 06:26

0 Answers0