PDO insert into query based on another field

Question

I have a table named 'users' that contains 'username' and 'team_name' columns. I have created form that populates two select dropdowns based on a 'username' from the users table and a 'team_name' from the 'teams' table.

I am now trying to create an insert statement that takes the 'team_name' and 'username' selected and inputs the 'team_name' into the 'users' table WHERE the 'username' is equal to one provided by the dropdown list.

$result = $conn->prepare("INSERT INTO users (`team_name`) VALUES ('{$teamname}') WHERE username = $username");

I am getting a syntax error SQLState[420000] - I think this is due to me not declaring username anywhere else within the statement i.e. columns - However, I do not want to give it a value - I just want it to post the team_name to the particuar username provided.

What is the best way to solve this?

UPDATE:

Even when using

UPDATE users SET team_name=$teamname WHERE username=$username

I still receive the error

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'grillzeE' in 'where clause

When the username does exist within the username column.

Maybe [`insert into select from`](https://dev.mysql.com/doc/refman/5.0/en/insert-select.html) is useful to you? — Rudie, Oct 18 '15 at 14:02
It sounds like you are actually trying to modify _existing_ rows in the `users` table, not insert new ones. For that, you need an `UPDATE` statement, rather than in `INSERT`. Please clarify if that is actually your intent, — Michael Berkowski, Oct 18 '15 at 14:04
And review [How can I prevent SQL injection in PHP](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) to begin learning to use `prepare()/execute()` with bound parameters. By simply inserting variables into the PDO statement, you are getting none of the important security benefits from `prepare()`. It is _essential_ that values like `$username` are bound as parameters `:username`. — Michael Berkowski, Oct 18 '15 at 14:07
Thanks for the quick response. I do have bindParams set - I will use them instead. I have updated the question as even when using an update method I receive the error 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'grillzeE' in 'where clause' even though the username exists in the database. — , Oct 18 '15 at 14:19
The error you're getting now is because the string variables are not single quoted. This again is avoidable by using parameters correctly. There should be _no variables_ in your SQL string. `UPDATE users set team_name = :teamname WHERE username = :username` then pass the values to `execute()` as in `$result->execute(array(':teamname' => $teamname, ':username' => $username));`. This method (passing an array to execute) is easier than using `bindParam()` explicitly, but is crucial that you not place variables directly into the SQL string. — Michael Berkowski, Oct 18 '15 at 14:35
Perfect. Worked straight away, thanks alot. I'll ensure in the future I bind values to parameters aswell. Will probably save me alot of time in the future. If you add this as an answer I will upvote, thanks. — , Oct 18 '15 at 14:39

score 0 · Answer 1 · answered Oct 18 '15 at 14:11

0

I think this is the query you are looking for:

INSERT INTO users (team_name)
SELECT team_name
FROM   teams
WHERE  username = ?

answered Oct 18 '15 at 14:11

meda

45,103
14
92
122

score 0 · Answer 2 · answered Oct 18 '15 at 14:13

If this is PDO then you are losing the benefit of prepared statements to a degree by actually specifying the variable value in the query rather than as a bound variable. Typically you should be using placeholders for the variables and substituting then in at runtime.

$stmt= $conn->prepare("INSERT INTO users (`team_name`) VALUES (:teamname) WHERE username = :username");

if( isset( $username ) && isset( $teamname ) ){
    $params=array(':teamname' => $teamname, ':username' => $username );
    foreach( $params as $key => &$val ) {
        $stmt->bindParam( $key, $val );
    }
    $stmt->execute();
}

Gives me an error `code Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 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 'WHERE username = 'grillzeE'' ` Even though grillzeE exists in the username column — , Oct 18 '15 at 14:26

PDO insert into query based on another field

2 Answers2