Trying Update Record with PDO

Question

I updated the question code I am still having issues no errors but does not update the record. also need to figure how to write in record # updated successfully. I am stuck on this update page.

<?php
error_reporting(E_ERROR | E_PARSE);
require_once("db_connect.php");
  $id = $_REQUEST['id'];
  $lanId = $_REQUEST['lanId'];
  $name= $_REQUEST['name'];
  $department = $_REQUEST['department'];
  $manager= $_REQUEST['manager'];
  $request = $_REQUEST['request'];
 $request_description = $_REQUEST['request_description'];
  $request_comments = $_REQUEST['request_comments'];
  $status = $_REQUEST['status'];
  $comments = $_REQUEST['comments'];
  $compUser = $_REQUEST['compUser'];
  $compDt = $_REQUEST['compDt'];
  
  
  $sql =   "UPDATE requests SET " . 
    "lanId =  '" . $lanId . "', ".
    "name =  '" . $name . "', ".
    "department = '" . $department . "', ".
    "manager = '" . $manager. "', " .
    "request = '" . $request. "', " .
    "request_description = '" . $request_description. "', " .
    "request_comments = '" . $request_comments. "', " .
    "status = '" . $status. "', " .
    "comments = '" . $comments. "', " .
    "compUser = '" . $compUser. "', " .
    "compDt = '" . $compDt. "'  WHERE id = '" . $id .  "';";
    
    #echo($sql);
    
    mysql_query($sql) or die (mysql_error);
    
   print("Record " . $id .  " has been updated. .")


?>
<html>

<head>
<meta http-equiv=REFRESH CONTENT=2;url=StatusPages/received.php>
<title>

</title>
</head>
<body background="images/background.jpg">

</body>

</html>

update.php page

<?php
    include('db_connect.php');
    $id=$_GET['id'];
    $result = $db->prepare("SELECT * FROM requests WHERE id= :id");
    $result->bindParam(':id', $id);
    $result->execute();
    for($i=0; $row = $result->fetch(); $i++){
?>

<html>
<head>
<title></title>

<style type="text/css">

}
.body{
    background-color: #F2F2F2;
    border: thin solid #666666;
}
</style>

</head>
<body class='body'>
<form action = "update_process.php" " method ="post" class="Form">


<p><input type ="hidden" name = "id" value="<?php print($id); ?>"</p>

<h2 align="center">Users request  Information</h2>
<table border='1' align="center">
<tr>    
    <td>LAN ID:</td>
<td><input type="text" value ="<?php  print($row['lanId']) ?>"name="lanId"></td>

    <td>Name:</td>
<td><input type="text" value ="<?php  print($row['name']) ?>"name="name"></td>
</tr>

<tr>    
    <td>Department Location</td>
<td><input type="text" value ="<?php  print($row['department']) ?>"name="department"></td>

    <td>Manager</td>
<td><input type="text" value ="<?php  print($row['manager']) ?>"name="manager"></td>
</tr>


<tr>    
    <td>Request</td>
<td><input type="text" value ="<?php  print($row['request']) ?>"name="request"></td>

<td>Request Description</td>
<td><input type="text" value ="<?php  print($row['request_description']) ?>"name="request_description"></td>

</tr>
</table>

<table border='1' align="center">
<br>
<h2 align='center'>Requested Comments</h2>
<tr>    

<td width='300'  height="40">
<input type="text" value ="<?php  print($row['request_comments'.'']) ?>"name="request_comments" size="50" style="height: 32px; width: 587px;" ></td>

</tr>

</table>


<h2 align="center">Complete or Update Requests Status</h2>

<table border='1' align="center" style="width: 595px">
<tr>    
    <td>Completed Date</td>
<td style="width: 303px">
<input type="text" value ="<?php echo date("Y-m-d",time())?>"name="compDt" style="width: 148px"></td>


</tr>
<tr>    
    <td>Status</td>
<td style="width: 303px"><select name ="status" style="width: 149px" >
<option value <?php if ($row['status']==1){ print('selected');}  ?> ="Received">Received</option>
<option value <?php if ($row['status']==2){ print('selected');}  ?> ="Completed">Completed</option>
<option value <?php if ($row['status']==3){ print('selected');}  ?> ="Cancelled">Cancelled</option>
<option value <?php if ($row['status']==4){ print('selected');}  ?> ="In_Progress">In_Progress</option>
<option value <?php if ($row['status']==5){ print('selected');}  ?> ="On_Hold">On_Hold</option>

</select>
</td>
</tr>
<tr>
    <td>Completed by</td>
<td style="width: 303px"><select name ="compUser" style="width: 149px" >
<option value <?php if ($row['compUser']==1){ print('selected');}  ?> ="unasigned">Please Select....</option>
<option value <?php if ($row['compUser']==1){ print('selected');}  ?> ="xgrh">xgrh</option>
<option value <?php if ($row['compUser']==2){ print('selected');}  ?> ="zeap">zeap</option>
<option value <?php if ($row['compUser']==2){ print('selected');}  ?> ="xjae">xjae</option>

</select>
</td>
</tr>


</table>




<div align='center'>    
<br>Comments:<br>   
<textarea name="comments" Value = "<?php  print($row['request_comments']) ?>"  style="width: 593px; height: 100px"></textarea><br>
    <br><br>
<input type="submit" value= "Update Information">
<br>
</div>
</form>


</body>
</html>
<?php
    }
?>

update_process.php page

<?php
 include('db_connect.php');
 
   $action = isset( $_POST['action'] ) ? $_POST['action'] : "";
            if($action == "update"){ 
            try{    
            global $conn;
 $sql = 'UPDATE requests SET lanId= :lanId, name= :name, department= :department,manager= :manager,request= :request,request_description= :request_description, request_comments= :request_comments,status= :status,comments= :comments,compUser= :compUser, compDt= :comDt WHERE id= :id';
$stmt = $pdo->prepare($sql);                                  
$stmt->bindParam(':lanId', $_POST['lanId'], PDO::PARAM_STR);       
$stmt->bindParam(':name', $_POST['$name'], PDO::PARAM_STR); 
$stmt->bindParam(':department', $_POST['department'], PDO::PARAM_STR);   
$stmt->bindParam(':manager', $_POST['manager'], PDO::PARAM_STR);
$stmt->bindParam(':request', $_POST['request'], PDO::PARAM_STR);    
$stmt->bindParam(':request_description', $_POST['request_description'], PDO::PARAM_STR);
$stmt->bindParam(':request_comments', $_POST['request_comments'], PDO::PARAM_STR);
$stmt->bindParam(':status', $_POST['status'], PDO::PARAM_STR);
$stmt->bindParam(':comments', $_POST['comments'], PDO::PARAM_STR);
$stmt->bindParam(':compUser', $_POST['compUser'], PDO::PARAM_STR);
$stmt->bindParam(':comDt', $_POST['comDt'], PDO::PARAM_STR);

$stmt->execute();
}catch(PDOException $exception){ 
            echo "Error: " . $exception->getMessage();
    }   
}
  


?>

Add error reporting to the top of your file(s) right after your opening ` — Jay Blanchard, Nov 06 '14 at 21:18
`$affected_rows->execute()` is most definitely not what you want here. You should be preparing a statement with `prepare()` and placeholders, then calling `execute()` on the statement. The [PDO tutorial for MySQL developers](http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers#Named_Placeholders) explains it well, and in context of the old `mysql_*()` functions if that is your background. — Michael Berkowski, Nov 06 '14 at 21:22
Try `$db->prepare` instead of `$db->exec` also add `$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);` right after the connection is opened. — Funk Forty Niner, Nov 07 '14 at 01:34
You don't have a named element called `action` and I don't know which form element you wish to check for. This for `$_POST['action']` — Funk Forty Niner, Nov 07 '14 at 17:47

score 2 · Answer 1 · answered Nov 06 '14 at 21:22

This code is a disaster:

$affected_rows = $db->exec("UPDATE requests SET") . 
                                               ^^---terminating your query here
                "lanId =  '" . $lanId . "', ".

So you run a malformed query (UPDATE requests SET), which will either throw an exception of return boolean FALSE. You then concatenate a whole bunch of text (which would've been part of your query) onto that FALSE.

And even if this code was properly structured, you'd be WIDE OPEN to sql injection attacks.

score 1 · Accepted Answer · edited May 23 '17 at 12:20

1

Your code is a mess.

You should use HEREDOC for big queries like this. Read more about HEREDOCs over here. Furthermore getting the affected row count is done with rowCount () More on that over here

I don't think you understand how prepared statements work either.

I highly advice you read up some of this.

Lastly please read up on what is wrong with $_REQUEST.

Now for the monstrousity you've managed to produce...

<?php


$db_host = "localhost";
$db_username = "root";
$db_pass = "";
$db_name = "test";

$db = new PDO('mysql:host='.$db_host.';dbname='.$db_name,$db_username,$db_pass);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);

  $id = $_REQUEST['id'];
  $lanId = $_REQUEST['lanId'];
  $name= $_REQUEST['name'];
  $department = $_REQUEST['department'];
  $manager= $_REQUEST['manager'];
  $request = $_REQUEST['request'];
  $request_description = $_REQUEST['request_description'];
  $request_comments = $_REQUEST['request_comments'];
  $status = $_REQUEST['status'];
  $comments = $_REQUEST['comments'];
  $compUser = $_REQUEST['compUser'];
  $compDt = $_REQUEST['compDt'];

$update =
<<<SQL

UPDATE requests
    SET lanID = ?,
        name = ?,
        department = ?,
        manager = ?,
        request = ?,
        request_description = ?,
        status = ?,
        comments = ?,
        compUser = ?,
        compDt = ?

        WHERE id = ?;

SQL;

$stmt = $db->prepare ($update);
$stmt->execute (array ($lanId, $name, $department, $manager, $request, $request_description,
                $status, $comments, $compUser, $compDt, $id));

echo $stmt->rowCount () . " rows were affected.";
echo "Record " . $id . " has been updated.";


?>

edited May 23 '17 at 12:20

Community

1
1

answered Nov 06 '14 at 22:22

Vanitas

865
1
7
19

Thank you I was going to keep trying to figure it out and repost new code. I changed $_REQUEST to $_POST also I tried the code you had above but i get syntax error on line 25 $update = UPDATE requests SET lanID = ?,name = ?,department = ?,manager = ?,request= ?,request_description = ?,status = ?,comments = ?,compUser = ?,compDt = ?, WHERE id = ?; – Donny Nov 06 '14 at 23:00
@Donny I edited it and tested it, it should work now ;) – Vanitas Nov 06 '14 at 23:06
I am still getting a parse error on line 25 unexpected 'UPDATE'(T_STRING) – Donny Nov 06 '14 at 23:21
Parse error: syntax error, unexpected 'requests' (T_STRING) in C:\wamp\www\Systems\update_process.php on line 25 now requests is getting error – Donny Nov 07 '14 at 00:02
https://www.youtube.com/watch?v=RcW8mMiIexc I found tutorials I am going to go through too – Donny Nov 07 '14 at 00:19
I updated the code still having issues wrote it in a different direction not sure if I am in the right path – Donny Nov 07 '14 at 04:37
not working seems to go to the page but not update the request no errors are shown – Donny Nov 07 '14 at 16:48

Trying Update Record with PDO

2 Answers2

Linked