Update database

MySQL database
#1

Hello everyone, learning CRUD with PHP and MySQL. adding new clients, viewing them works fine. however when I select a client to edit the information, it will not update. gives me no errors, just will not update. and all I changed was the the actual sql to UPDATE but im not quite sure where I am messing up. Any help would be greatly appreciated.

[code]<?php require_once('dbcon.php'); ?>

<?php if(isset($_POST['btn_submit'])) { $id = $_POST['txt_client_id']; $client_lname = $_POST['txt_client_lname']; $client_fname = $_POST['txt_client_fname']; $client_mname = $_POST['txt_client_mname']; $client_phone = $_POST['txt_client_phone']; $client_email = $_POST['txt_client_email']; $client_street_address = $_POST['txt_client_street_address']; $client_city = $_POST['txt_client_city']; $client_state = $_POST['txt_client_state']; $client_zip = $_POST['txt_client_zip']; $client_instagram = $_POST['txt_client_instagram']; $client_facebook = $_POST['txt_client_facebook']; if(!empty($client_lname)){ try{ $stmt = $con->prepare("UPDATE client_list set client_lname= :client_lname, client_fname= :client_fname, client_mname= :client_mname, client_phone= :client_phone, client_email= :client_email, client_street_address= :client_street_address, client_city= :client_city, client_state= :client_state, client_zip= :client_zip, client_instagram= :client_instagram, client_facebook= :client_facebook WHERE client_id = :id"); $stmt->execute(array(':client_lname'=>$client_lname, ':client_fname'=>$client_fname, ':client_mname'=>$client_mname, ':client_phone'=>$client_phone, ':client_email'=>$client_email, ':client_street_address'=>$client_street_address, ':client_city'=>$client_city, ':client_state'=>$client_state, ':client_zip'=>$client_zip, ':client_instagram'=>$client_instagram, ':client_facebook'=>$client_facebook, ':id'=>$id)); if($stmt){ header('Location:client_list.php'); } }catch(PDOException $ex){ echo $ex->getMessage(); } }else { echo "INPUT LAST NAME"; } } $client_id = 0; $client_lname = ''; $client_fname = ''; $client_mname = ''; $client_phone = ''; $client_email = ''; $client_street_address = ''; $client_city = ''; $client_state = ''; $client_zip = ''; $client_instagram = ''; $client_facebook = ''; if(isset($_GET['id'])){ $id = $_GET['id']; $stmt = $con->prepare('SELECT * FROM client_list WHERE client_id = :id'); $stmt->execute(array(':id'=>$id)); $row = $stmt->fetch(); $client_id = $row['client_id']; $client_lname = $row['client_lname']; $client_fname = $row['client_fname']; $client_mname = $row['client_mname']; $client_phone = $row['client_phone']; $client_email = $row['client_email']; $client_street_address = $row['client_street_address']; $client_city = $row['client_city']; $client_state = $row['client_state']; $client_zip = $row['client_zip']; $client_instagram = $row['client_instagram']; $client_facebook = $row['client_facebook']; } ?>

Edit Client Information

[/code]
Last Name
First Name
Middle Name
Phone
Email
Street Address
City
State
Zip
Instagram
Facebook
#2

Hi Christian,

Try to replace
[php]value="<?$client_id;?>"[/php]
to
[php]value="<?=$client_id;?>"[/php]

#3
learning CRUD

Actually, one of the points of CRUD is developing general purpose code that can be (re)used with different tables WITHOUT typing out line after line of code for each possible field for each Create, Read, Update, or Delete operation.

The way to do this is to define the expected and allowed fields for each operation in an array. The general purpose code would then just detect which operation is being performed and use the array definition to control what the code does.

For the C, U, and D operations, the post method form processing code needs a hidden form field (named ‘action’ or similar) that holds a value that identifies which operation is being performed. The form processing code would use a switch/case statement to test this value and control which set of code gets executed.

The form processing code should -

  1. Detect that a post method form was submitted.

  2. Detect which operation is being performed.

  3. Validate all the expected input data. The defining array for the operation can include entries with the types of validation to perform for each input. You should store validation error messages in a php array variable. You can display all the error messages at the appropriate point in the html document when you (re)display the form, simply by looping over the php array variable. Had your code been validating the id input, you would have known there was a problem with it. You should NOT copy variables to other variables for no reason. If you do have a reason to make a copy of an array of variables, such as if you are trimming all the data, you can do this using a single line of code. There’s no need for separate lines of code for each variable.

  4. If there are no validation errors (the php array variable holding the validation error messages is empty), use the submitted data. The defining array can be used to dynamically build the correct sql query statement and to dynamically produce the array of data for the execute method.

  5. You should NOT output the raw database error messages on a web page (except when learning, developing code, or debugging code).

The simplest way of doing this is to let php handle the database errors, where it will use its error_reporting, display_errors, and log_errors settings to control what happens with the actual error information. When learning, developing, or debugging, you would display all php errors. When on a live/public server, you would log all php errors.

Your code would handle only the database errors that have meaning in your application. The only case I have ever found for this is when you try to insert/update duplicate data. You would use a try/catch block only for the execution of an insert/update query. The catch code would check for a duplicate key error number (it’s 1062). If there is a duplicate key error, you would setup an error message for the user (you can parse the database error message to get the field name that produced the error). If the database error isn’t for a duplicate key, you would re-throw the exception and let php handle the error.

While this sounds like an advanced subject and a lot of code, it only requires ‘factoring out’ the repeated and hard-coded logic you have now and replacing it with code that uses defining array(s) and some loops. This will reduce the amount of statements in the code and after you have created the general purpose ‘framework’ code, you can re-use it for any table, simply by creating/changing the defining array(s).

#4

You guys are awesome. Thank you for your time and help. and thank you for passing on this knowledge :slight_smile:

#5

Hello.

I see you have quite similar to my issue. I am not using PDO, yet (still learning SQLi for the moment). Can I ask you a favor? Can you please take a look at my code. I saw your code is not much different (other than PDO part) than mine… And I also use the value attribute to show data and name attribute to get the inserted new data, just like yours…

Well, my code did not update the data but returned the updated message. When I check the table, the new inserted data is not updated inside the table…

Thank you…

[php]<?php
$sqlshowid = “SELECT conid FROM registerlogin”;
$insertsql = mysqli_query( $sqltable, $sqlshowid );

echo "<form method=post>";
echo "<table>";
	
echo "<select name=updatedata onchange=this.form.submit()>";
echo "<option style=display:none>Pick an ID</option>";
	
while( $showid = mysqli_fetch_array( $insertsql ) )
{
	echo "<option>".$showid['conid']."</option>";
} echo "</select>";
	
if( isset( $_POST['updatedata'] ) )
{
	$id = $_POST['updatedata'];
	$sqlshowall = "SELECT * FROM registerlogin WHERE conid='$id'";
	$displaydata = mysqli_query( $sqltable, $sqlshowall );
		
	$rowdata = mysqli_fetch_array( $displaydata );
		
	echo "<tr><td>ID</td><td>".$id."</td></tr>";
	echo "<tr><td>Fullname</td><td><input type=text name=usernameupdate value='".$rowdata['name']."'></td></tr>";
	echo "<tr><td>Password</td><td><input type=text name=passwordupdate value='".$rowdata['passwd']."'></td></tr>";
	echo "<tr><td>Email</td><td><input type=email name=emailupdate value='".$rowdata['email']."'></td></tr>";
			
	echo "<tr><td><td><button type=submit name=buttonupdate>Update</td></td></tr>";
	echo "</table>";
	echo "</form>";
}
	
if( isset( $_POST['buttonupdate'] ) )
{
	$updatename = $_POST['usernameupdate'];
	$updatepassword = $_POST['passwordupdate'];
	$updateemail = $_POST['emailupdate'];
		
	$sqlchange = "UPDATE registerlogin SET name='$updatename',passwd='$updatepassword',email='$updateemail' WHERE conid='$id'";
		
	$checkquery = mysqli_query( $sqltable, $sqlchange );
		
	if( $checkquery )
		echo "UPDATED DATA";
	else
		echo "Failed to update data";
}

?>[/php]

#6

hi,

just add var_dump:
[php]$sqlchange = “UPDATE registerlogin SET name=’$updatename’,passwd=’$updatepassword’,email=’$updateemail’ WHERE conid=’$id’”;
var_dump($sqlchange);[/php]
and you will see the reason:

UPDATE registerlogin SET name='name',passwd='123',email='foo@bar' WHERE conid='Pick an ID'

fix:
[php]$id = isset($_POST[‘updatedata’]) ? $_POST[‘updatedata’] : null;
while( $showid = mysqli_fetch_array( $insertsql ) )
{
$selected = $showid[‘conid’] == $id ? ‘selected’ : ‘’;
echo “<option $selected>”.$showid[‘conid’]."";
} echo “”;[/php]

#7

Hi, Antonio.

Thank you for the response.

Can you explain your method? It might be useful for me to share in the future.

[EDIT]

Antonio! You’re the bomb!

Thank you very much, sir. So, that’s why I was unable to update the code because the ID keep storing the ‘Pick an ID’ as the value for the whole entire time!
Dude, I am so grateful that I met you today. Karma given.

Again, sir. Thank you very much. My two weeks battle has now come to an end. Thank you, again. ;D

#8

My method is very simple: always check sql and user’s input. Always! :slight_smile: Is something going wrong? Ok, just do var_dump($_REQUEST); and var_dump($sql); And then try to find some errors. And then fix them.

Good luck!

#9

Sir, I got one question regarding your solution.

when you use the ‘?’ between the two condition, does that mean it has to choose which condition it will go to?

Thank you, again and sorry for asking. I would just like to understand more.

#10

zaimazhar97,

yes, it works like ‘if’:
[php]// long
if (isset($_POST[‘foo’])) {
$foo = $_POST[‘foo’];
} else {
$foo = null;
}

// short
$foo = isset($_POST[‘foo’]) ? $_POST[‘foo’] : null;[/php]

If you are using php 7 you can use ?? instead of isset:
[php]// only for php 7
$foo = $_POST[‘foo’] ?? null;[/php]
This expressions are equal.

PS Thanks for the karma :slight_smile:

#11

[member=88228]zaimazhar97[/member], Do not hijack someone else’s thread. Start your own.

Sponsor our Newsletter | Privacy Policy | Terms of Service