Updating Table w/PHP

Question

I am trying to write a script that receives the updated data from script called update.php and updates the database entry. The code for updated.php:

<?php
ini_set("display_errors","on");
$dsn='mysql:host=localhost;dbname=inventory_form';
$username="****";
$password="*****";
$database="inventory_form";

$FName_val=$_POST['ud_first'];
$LName_val=$_POST['ud_last'];
$Eqpmnt_Brwd_val=$_POST['ud_Equipment_Borrowed'];
$Service_Tag_val=$_POST['ud_Service_Tag'];
$Date_Taken_val=$_POST['ud_Date_Taken'];
$Comments_val=$_POST['ud_Comments'];
$id_val=$_POST['ud_id'];
try
    {
    $link=new PDO($dsn, $username,$password);
    echo 'Connected To MySQL OK';
    }
catch (PDOException $e)
    {
    $error_message=$e->getMessage();
    echo "<h1>An error occurred: $error_message</h1>";
    }

$query = "UPDATE Inventory SET FName='$FName_val', LName='$LName_val', Eqmnt_Brwd='$Eqpmnt_Brwd_val', Service_Tag='$Service_Tag_val', Date_Taken='$Date_Taken_val', Comments='$Comments_val' WHERE id_val='$id_val'";
$result=$link->query($query);
echo "Record Updated";
echo $LName_val;
?>

I receive no errors and see the LName_val print out but the database is not being updated. Any help appreciated.

Use a prepared query in case any of the parameters contain quotes. — Barmar, Jun 28 '14 at 00:20
I'm sorry new to all this. Could you be more specific. I dont have to list the db id field in the Update/Set command,correct? — , Jun 28 '14 at 00:20
Add `$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);` right after the connection is opened, since you're not checking for errors. — Funk Forty Niner, Jun 28 '14 at 00:57
Plus, in your [**other question**](http://stackoverflow.com/q/24411496/) you're using `WHERE id='$id'` and in this one it's `WHERE id_val='$id_val'`. I also noticed your use of short open tags syntax `value=" echo "$id"; ?>"`. If those are not "ON", you'll need to use `value=""` — Funk Forty Niner, Jun 28 '14 at 01:03
Yeah I was advised to change to id_val=id_val.I changed the value= tp ?php but still get Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in /var/www/inventory/updated.php on line 37 — , Jun 28 '14 at 01:38
Check your column names and make sure you don't have one that contains a typo. Column names are case-sensitive, meaning that `Username` is not the same as `username`, or that your column(s) have a hyphen or a space instead of what you're using now, being underscores to act as word seperators. Even if "one" if off, your entire query will fail. Make absolutely certain. Plus, if you can provide DB schema, we can tell right away, or at least know what we're dealing with. — Funk Forty Niner, Jun 28 '14 at 01:41
Plus, did you add what I posted above about `$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);`? — Funk Forty Niner, Jun 28 '14 at 01:44
I also noticed another thing, you're not "executing" your query (why didn't I spot that earlier?). You need to do something like `$result=$link->execute($query);` - Here's an example page for you to see http://www.phpeveryday.com/articles/PDO-Insert-and-Update-Statement-Use-Prepared-Statement-P552.html and see this Q&A on SO http://stackoverflow.com/q/18323065/ — Funk Forty Niner, Jun 28 '14 at 01:52

Barmar · Answer 1 · 2014-06-28T01:32:17.403

1

Use a prepared query:

$stmt = $link->prepare("UPDATE Inventory 
                        SET FName = :FName, LName = :LName, Eqmnt_Brwd = :Eqmnt_Brwd, 
                        Service_Tag = :Service_Tag, Date_Taken = :Date_Taken, Comments = :Comments
                        WHERE id = :id");
$stmt->execute(array(
    ':FName' => $FName_val,
    ':LName' => $LName_val,
    ':Eqmnt_Brwd' => $Eqmnt_Brwd_val,
    ':Service_Tag' => $Service_Tag_val,
    ':Date_Taken' => $Date_Taken_val,
    ':Comments' => $Comments_val,
    ':id' => $id_val));

This will avoid problems if any of the values contain quotes.

Also, you need to change

WHERE id_val =

to:

WHERE id =

edited Jun 28 '14 at 01:32

answered Jun 28 '14 at 00:39

Barmar

741,623
53
500
612

Thanks b ut still getting error:Connected To MySQL OK Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in /var/www/inventory/updated.php on line 37 – Jun 28 '14 at 01:06
Make sure all the keys in the array match the placeholders in the query. It's case sensitive, so make sure that matches. – Barmar Jun 28 '14 at 01:11
OP's got quite a few similarly related questions. Column names seem to have changed over a course of time; hard to pinpoint exactly where OP's errors are being made. – Funk Forty Niner Jun 28 '14 at 01:22
Yes they all match. Here is link to first php script that calls this one: http://pastebin.com/2kaAJSb9. Here id does not cause a problem? – Jun 28 '14 at 01:24
I don't care about that script. It's complaining about an error in THIS script. I think you didn't write the prepared statement correctly. – Barmar Jun 28 '14 at 01:26
Your pastebin file's using `WHERE id=` you say this works, yet your posted code's using `WHERE id_val=` (which one is it?). Did you not read the comments I placed under your original question? @Alan – Funk Forty Niner Jun 28 '14 at 01:26
@Fred-ii- They're two different scripts, why should variable names be the same? All that matters is that the variable you use is the same one that you assign in that same script. – Barmar Jun 28 '14 at 01:28
@Fred-ii- This script does `$id_val = $_POST['ud_id']`, that script does `$id = htmlspecialchars($_GET['id'])`. – Barmar Jun 28 '14 at 01:29
I wasn't talking about variable names, I was talking about column names. – Funk Forty Niner Jun 28 '14 at 01:31
But that wouldn't cause the `Invalid parameter number` error that he said he got. – Barmar Jun 28 '14 at 01:32
Which just leaves a lot of room for error and confusion. Without seeing OP's DB schema, we can't be 100% certain as to where the error lies. – Funk Forty Niner Jun 28 '14 at 01:32
*Hm*... I do remember seeing a question not so long ago where a bound `id` was the fault. Maybe OP's got an AI on the `id` column? – Funk Forty Niner Jun 28 '14 at 01:33
Here's schema: This table does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available. Field Type Null Key Default Extra FName varchar(12) NO NULL LName varchar(15) NO NULL Eqpmnt_Brwd varchar(25) NO NULL Service_Tag text NO NULL Date_Taken date NO NULL Comments varchar(150) NO NULL id int(11) NO PRI NULL auto_increment – Jun 28 '14 at 01:53
Please don't waste our time trying to put formatted data in comments, they're totally unreadable. And why do I care if those features are available? – Barmar Jun 28 '14 at 03:17
I'm sorry but I thought Fred ii was looking for the DB schema that's all. I will go back and try to sort this out on my own. – Jun 28 '14 at 19:28
He did ask to see that. Add it to the question, not a comment. – Barmar Jun 29 '14 at 05:43

Updating Table w/PHP

1 Answers1