PHP Insert into MySQL Database using $_SESSION['user'] in WHERE clause

Question

I'm trying to insert variables into my database where the user data comes from $_SESSION['user'].

<?php

require("common.php");

if(empty($_SESSION['user']))
{
    header("Location: login.php");

    die("Redirecting to Login");
}

$user = $_SESSION['user'];

~calculations done~

$query = "INSERT INTO db (role,rolesub) VALUES ('$varRole','$varRoleSub') WHERE user = $user";

    $query_params = array(
        ':role' => $varRole,
        ':roleSub' => $varRoleSub
    );
    try
    {
        $stmt = $db->prepare($query);
        $result = $stmt->execute($query_params);

    }
    catch(PDOException $ex)
    {
        die("Failed to run query 3: " . $ex->getMessage());
    }

I keep getting this error:

Failed to run query 3: 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 user = Array' at line 1

I can not see where my WHERE clause is failing on me.

Any help would be greatly appreciated!!!

as the error message clearly shows $_session['user'] is an array. You need to find out which value inside that array is the one you want and use the appropriate key to extract it. — John Conde
– John Conde, Commented May 2, 2014 at 18:10
You are vulnerable to SQL injection attacks. Your placeholders are utterly useless since you don't actually HAVE any actual placeholders in the query. — Marc B
– Marc B, Commented May 2, 2014 at 18:29
Thank you! I completely spaced when I was working the placeholders in. Got it working with the UPDATE. When I check the database, the data inputted into my two fields come up :role and :ro...not the data it should have. — johnnysheps
– johnnysheps, Commented May 2, 2014 at 22:27

Sammitch · Accepted Answer · 2014-05-02 18:12:38Z

1

You cannot have a WHERE clause in an INSERT statement.

You're either looking for:

UDPATE db SET role = '$varRole', rolesub = '$varRoleSub' WHERE user = $user

Or:

INSERT INTO db (role,rolesub,user) VALUES ('$varRole','$varRoleSub',$user)

Or if you're feeling extra saucy, and user is your PK:

INSERT INTO db (role,rolesub,user) VALUES ('$varRole','$varRoleSub',$user)
  ON DUPLICATE KEY UPDATE role = '$varRole', rolesub = '$varRoleSub'

answered May 2, 2014 at 18:12

Sammitch

32.5k7 gold badges57 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Funk Forty Niner Over a year ago

"You cannot have a WHERE clause in an INSERT statement." --- True, but not entirely true. The only time you will find INSERT that has a WHERE clause is when you are using an INSERT INTO...SELECT statement.

MrCode · Accepted Answer · 2014-05-02 18:16:51Z

1

INSERT queries do not and can not have a WHERE clause. This is the cause of the MySQL syntax error. If you need to insert based on some condition, you need to do that logic before the INSERT query.

If you want to do an UPDATE query then you can use the WHERE clause, however, the MySQL error shows $_SESSION['user'] is an array, which can't be put directly into SQL, so you'll need to access one of its elements such as $_SESSION['user']['id'].

edited May 2, 2014 at 18:16

answered May 2, 2014 at 18:11

MrCode

64.6k10 gold badges95 silver badges114 bronze badges

Comments

sgrutman978 · Accepted Answer · 2014-05-02 18:13:13Z

0

First of all, IF you could have a WHERE in the same query as an INSERT, variables need to be separate from the string (outside of the quotes). BUT you CANT put a where clause into an INSERT.

So you could change this line:

$query = "INSERT INTO db (role,rolesub) VALUES ('$varRole','$varRoleSub') WHERE user = $user";

to:

$query = "INSERT INTO db (role,rolesub) VALUES (" . $varRole . ", " . $varRoleSub . ")";

answered May 2, 2014 at 18:13

sgrutman978

1128 bronze badges

Collectives™ on Stack Overflow

PHP Insert into MySQL Database using $_SESSION['user'] in WHERE clause

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related