sql INSERT with a php form failing

Question

I have an update form which I am trying to enable updating fields but struggling to update the fields when submitting - perhaps I am missing something very obvious here.

Here is my form:

<form action="actions/updateDoc.php" method="POST">

    <input type="text" value="<?php echo $doc['doc_title'] ?>" name="doc_title" />
    <br />

    <input type="submit" value="Update" name="submit" />    
</form>

Here is the script to action that form:

    <?php

    if(isset($_POST["submit"])){
    $hostname='localhost';
    $username='******';
    $password='******';

    try {

    $dbh = new PDO("mysql:host=$hostname;dbname=******",$username,$password);

    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // <== add this line

$doc_title = $_POST['doc_title'];

$sql = "UPDATE doc_list (doc_title) SET ('".$_POST["doc_title"]."')";

if ($dbh->query($sql)) {
    header ('Location: ../docEdit.php');
}
else{
}

$dbh = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}

}
?>

The script runs but getting a blank screen and no update occurs. I have now taken some code out to show just updating 1 row, I get the following error:

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 '(doc_title) SET ('Document content sdfsd')' at line 1

You are using `INSERT` sytnax - [`INSERT INTO table (columns) VALUES (values)`](https://dev.mysql.com/doc/refman/5.0/en/insert.html). `UPDATE` syntax is [`UPDATE table SET column=value`](https://dev.mysql.com/doc/refman/5.0/en/update.html). Also, if you don't provide a `WHERE` clause, all your rows will be updated to that value. see https://dev.mysql.com/doc/refman/5.0/en/update.html — Sean, May 03 '15 at 15:27
Your html inputs needs to have a name, so you can grab their value. Also, you have an emtpy `else`, maybe the is entering there. — Choma, May 03 '15 at 15:30
also, if you are using `PDO`, please take the time/effort to use prepared statements / parameter placeholders - http://php.net/manual/en/pdo.prepared-statements.php — Sean, May 03 '15 at 15:30
as @Choma pointed out, without input name attributes `if(isset($_POST["submit"]))` is false, as your submit button does not have a `name="submit"`, and neither of your form elements will be posted as they are also nameless. — Sean, May 03 '15 at 15:34
Sorry my apologies - they do have names but I still get the blank page — PhpDude, May 03 '15 at 15:47
Aside from the above - which i have corrected does the code itself look like it should be working? — PhpDude, May 03 '15 at 15:52
if you have corrected your code, but it still does not work, then edit your question with the updated code. We would just be guessing at this point as to what still needs to be fixed. — Sean, May 03 '15 at 15:54
Please reread my 1st comment. Your error message is telling you that you have a syntax error, as `"UPDATE doc_list (doc_title) SET ('".$_POST["doc_title"]."')"` is incorrect `UPDATE` syntax. It should be `"UPDATE doc_list SET doc_title='".$_POST["doc_title"]."'"`, although without a `WHERE` condition, every `doc_title` in every row will now be updated to `$_POST["doc_title"]` value — Sean, May 03 '15 at 16:05
It still isn't completely clear - is your intent to modify an existing row or to add a new one? The syntax to modify an existing one is `UPDATE doc_list SET doc_title = :doc_title WHERE ` The use of `:doc_title` is [an important protection against SQL injection](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) using `prepare()/execute()` instead of `query()` — Michael Berkowski, May 03 '15 at 16:05
@Sean I am wanting to update and existing record, so for example I want to update the doc_title even if it already has one, I am struggling to understand how to do that. I know my code isn't right but struggling to figure out what bits need to go where. — PhpDude, May 03 '15 at 16:14
But which `doc_title`? For every row, or just 1 row? Does your row have an `id` or other primary key that identifies it? — Sean, May 03 '15 at 16:18
Just for 1 row and yes the row has an Id of doc_id which is the primary key for that table — PhpDude, May 03 '15 at 16:35
I think the bit I am struggling to understand is the WHERE clause — PhpDude, May 03 '15 at 16:39
You need to add the `doc_id` to your form, either in the action url - `` which you would then get using `$_POST['doc_id']`. You would then use that in your query -> `UPDATE doc_list SET doc_title = '".$_POST["doc_title"]."' WHERE doc_id=$_POST["doc_id"]"` (using `$_GET`/`$_POST` depending on your form). also, please use prepared statements in PDO for this. — Sean, May 03 '15 at 16:49

score 1 · Accepted Answer · answered May 03 '15 at 16:54

First, add the doc_id to your form (as a hidden input) -

<form action="actions/updateDoc.php" method="POST">

    <input type="text" value="<?php echo $doc['doc_title'] ?>" name="doc_title" />
    <input type="hidden" value="<?php echo $doc['doc_id'] ?>" name="doc_id" />
    <br />

    <input type="submit" value="Update" name="submit" />    
</form>

Then change your php code to get the doc_id (and use prepared statement/placeholders) -

<?php

    if(isset($_POST["submit"])){
    $hostname='localhost';
    $username='******';
    $password='******';

    try {

    $dbh = new PDO("mysql:host=$hostname;dbname=******",$username,$password);

    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // <== add this line



$sql = "UPDATE doc_list SET doc_title = :doc_title WHERE doc_id = :doc_id";
$query = $dbh->prepare($sql);
$query->execute(array(":doc_title"=>$_POST["doc_title"], ":doc_id"=> $_POST["doc_id"]));

if ($query) {
    header ('Location: ../docEdit.php');
}
else{
}

$dbh = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}

}
?>

Ok so that is working and is actually updating the record - however I get this error: 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 ':doc_title WHERE doc_id = :doc_id' at line 1 — PhpDude, May 03 '15 at 17:35

sql INSERT with a php form failing

1 Answers1