0

been almost a year since i have had to muck around with databases, and as a self taught starter, am having a bit of trouble. Below is the part of code that is not working, above this is the successful connection to server. From here it is failing to update and i am not sure why. On my system, i do have a working PDO connection string if you feel that it should be done in PDO. At this point i am poking holes in the air. Appreciate your time.

$stmt = $conn->prepare("UPDATE bfl SET gamerid="?", name="?", age="?",location"?", email="?", console="?"WHERE team="?" ");



 $stmt->bindParam(1, $gamerid);
    $stmt->bindParam(2, $name);
    $stmt->bindParam(3, $age);
    $stmt->bindParam(4, $location);
    $stmt->bindParam(5, $email);
    $stmt->bindParam(6, $cosole);
    $stmt->bindParam(7, $team);

$stmt->execute(array($gamerid,$name,$age,$location,$email,$co‌​nsole,$team);
Stu Parker
  • 41
  • 6
  • 2
    That's the `insert` syntax not the `update` syntax. `mysql_*` also won't mix with `pdo` you should use `pdo` and parameterize the query. See https://dev.mysql.com/doc/refman/5.7/en/update.html for update syntax. Additionally variables in single quotes aren't variables. So there are a lot of issues here you're going to need to work out.. – chris85 Feb 07 '17 at 02:13
  • so SET gamerid="$gamerid", name="$name", ect.. my pdo ability is virtually non existant – Stu Parker Feb 07 '17 at 02:22
  • Your `mysql_*` doesn't seem to be that far along so might as well fix the ship before it sinks. http://php.net/manual/en/pdo.prepared-statements.php Use `?`s for all the values then pass the values with the binding function or in the execute as an array. e.g. `$sth->execute(array($gamerid,$name,$age,$location,$email,$console, $team))` and the prepare like `$pdo->prepare('UPDATE bfl SET gamerid = ?,name = ?,age =?,location=?,email=?,console=? WHERE team= ?')` – chris85 Feb 07 '17 at 02:25
  • It should be done in PDO or mysqli. PHP [removed the mysql_ functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) in 7.0. You should also [switch to prepared statements](http://stackoverflow.com/documentation/php/5828/pdo/2685/preventing-sql-injection-with-parameterized-queries). I'll bet another reason your query is failing due to your concatenating data – Machavity Feb 07 '17 at 02:26
  • something along these lines $stmt = $dbh->prepare("UPDATE bfl SET (gamerid="?", name="?", age="?",location"?", email="?"console="?") WHERE team="?" ");'$stmt->bindParam(1, $gamerid); $stmt->bindParam(2, $name); $stmt->bindParam(3, $age); $stmt->bindParam(4, $location); $stmt->bindParam(5, $email); $stmt->bindParam(6, $cosole); $stmt->bindParam(7, $team); $sth->execute(array($gamerid,$name,$age,$location,$email,$co‌​nsole,$team); – Stu Parker Feb 07 '17 at 02:39
  • Have you added any error output? We still don't know why MySQL doesn't like it – Machavity Feb 07 '17 at 03:05
  • under advisement I am looking at PDO, but the above code is what I have peiced together after reading a page, and most likely full of holes... any advise would be helpful... still getting syntax errors here, not even through to sql yet – Stu Parker Feb 07 '17 at 03:13
  • there seems to be a problem with prepare statement – Stu Parker Feb 07 '17 at 03:18
  • I'll write an answer. – chris85 Feb 07 '17 at 04:02

1 Answers1

1
  1. That mysql syntax is the insert syntax, not update.
  2. Use PDO not mysql_*.
  3. Use parameterized queries, not concatenated.
  4. Placeholders don't get quoted.
  5. Bind or bind in the execute, don't do both.
  6. Don't put parenthesis around the whole inner query.

Put together you get:

$stmt = $dbh->prepare('UPDATE bfl
                       SET gamerid=?,
                       name=?,
                       age=?,
                       location?,
                       email=?,
                       console=?
                       WHERE team=?');
$stmt->execute(array($gamerid,$name,$age,$location,$email,$co‌​nsole, $team));

Also look at using error reporting, http://php.net/manual/en/pdo.error-handling.php, this will help you in the future.

chris85
  • 23,846
  • 7
  • 34
  • 51