CODEWITHSUNDEEP
phpmysqlpdo
Donny
Donny

Reputation: 699

Trying Update Record with PDO

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();
    }   
}
  


?>

Upvotes: 1

Views: 203

Answers (2)

Vanitas
Vanitas

Reputation: 903

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.";


?>

Upvotes: 1

Marc B
Marc B

Reputation: 360572

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.

Upvotes: 2

Related Questions