update specific row based on user selection using PDO via php issue

Question

I am trying to update my sql table based off user input. So the user picks from a select list the column they want to update. Then they enter the data from the row and then what they want to update that with. so say the pick column teamname, the would then enter the team name they want to change via text box and then via another textbox enter the new name. Thats what Im trying to get to happen anyways.

I tried to copy and paste my code from where the user deletes data and modify it. I have tried a couple different things, the first thing I tried gave me this error:

UPDATE teams SET rockets = :value1 WHERE teamname = rockets SQLSTATE[42S22]: Column not found: 1054 Unknown column 'rockets' in 'where clause'

My sql statement looked like this:

$sql = "UPDATE teams SET $selectData = :value1  WHERE $columnSelect = $selectData";

Then, I tried to do something like this:

entire code below, as stated above I have tried a couple different thigns with the $sql variable:

php

<?php

    if ($_SERVER["REQUEST_METHOD"] == "POST"){ 

        $servername = "localhost";
        $username = "";
        $password = "";
        $dbname = "";
        $columnSelect = $_POST['selectColumn2'];
        $selectData = $_POST['selectData'];
        $updateData = $_POST['updateData'];

        try {
            $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
            // set the PDO error mode to exception
            $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

            // sql to delete a record
            $sql = "UPDATE teams SET :value1 = :value2  WHERE $columnSelect = :value1";

            // use exec() because no results are returned
            $stmt = $conn->prepare($sql);
            $stmt->execute(array(':value1'=>$selectData, ':value2'=>$updateData));
            if ($stmt->rowCount() > 0) {
                echo 'Updated '.$stmt->rowCount().' rows';
            } else {
                echo 'No rows updated';
                }
        }
        catch(PDOException $e)
            {
            echo $sql . "<br>" . $e->getMessage();
            }

        $conn = null;
    }
?>

html

        <form method='post' action='addData.php'>
            Select a column name, then enter which data to update.
            <br>
            <br>
            <label for='option2'>
                <select name='selectColumn2'>
                    <option value='teamname' id='team2'>teamname</option>
                    <option value='city' id='city2'>city</option>
                    <option value='bestplayer' id='best2'>bestplayer</option>
                    <option value='yearformed' id='year2'>year</option>
                    <option value='website' id='website2'>website</option>
                </select>
            </label>    
            <label for='option2'>
                select data to change: <input type='text' name='selectData'>
                enter new data: <input type='text' name='updateData'>
            </label>
            <br><br>
            <input type='submit' value='Submit New Entry'>
        </form>

Basically what I what to do is take $columnSelect, find $selectData in that column and replace it with $updateData using the PDO method. What am I doing wrong?

Answer 1

This should work: UPDATE teams SET $columnSelect = :value1 WHERE $columnSelect = $selectData

Answer 2

Change your query like this,

$sql = "UPDATE teams SET `$selectData` = ':value1'  WHERE `$columnSelect` = '$selectData'";

Add single quotes for string values while inserting or updating. And addd backtics for the table name or column names.

EDIT

$sql = "UPDATE teams SET `$columnSelect` = ':value1'  WHERE `$columnSelect` = '$selectData'";