MySQL UPDATE, need help...
Moderator: General Moderators
-
WithHisStripes
- Forum Contributor
- Posts: 131
- Joined: Tue Sep 13, 2005 7:48 pm
MySQL UPDATE, need help...
Heya - how do you tell MySQL to update a field unless the submitted field is blank?
Re: MySQL UPDATE, need help...
I assume your referring to a HTML form?
Such as:
update_mysql.php
Such as:
Code: Select all
<form method="post" action="update_mysql.php">
<input type="text" name="important" size="20"/>
</form>Code: Select all
<?php
// Check that the variable was actually set and then make sure it's not empty.
if(isset($_POST['important']) && !empty($_POST['important']))
{
// Create a new database connection
$mydb = new MySQLi(DB_HOST,DB_USER,DB_PASS,DB_NAME);
// Clean the data to make sure it's safe for use in a query
$important = $mydb->real_escape_string($_POST['important']);
// Create the query
$query = "Update mytable Set myimportantfield = '$important' Where mycondition = 'mystatement'";
// Execute the query
if($mydb->query($query))
echo "Field successfully updated";
else
echo "Encountered an error. Review your script.";
}
?>
Last edited by Benjamin on Wed Jun 10, 2009 1:40 pm, edited 1 time in total.
Reason: Changed code type from text to php.
Reason: Changed code type from text to php.
-
WithHisStripes
- Forum Contributor
- Posts: 131
- Joined: Tue Sep 13, 2005 7:48 pm
Re: MySQL UPDATE, need help...
I think I'm looking for a different way. Let me share my code with you so you can see what I'm talking about specifically.
I have an HTML form - as you noted - and the user can update their password if they want to, which is MD5'd, but if it's left blank when they submit their form (which it is by default since you can't reverse MD5 to the best of my knowledge) then it resets their password to blank.
Form:
Processor:
I have an HTML form - as you noted - and the user can update their password if they want to, which is MD5'd, but if it's left blank when they submit their form (which it is by default since you can't reverse MD5 to the best of my knowledge) then it resets their password to blank.
Form:
Code: Select all
<form id='your-profile' name='frmPhone' action='" . $root . "nodes/save-client-data.php' method='post'>
<table valign='top'>
<tr>
<td><p>First Name: </p></td>
<td><input type='text' name='client-first-name' maxlength='30' value='" . ucwords($insert_client_data['client-first-name']) . "' /></td>
</tr>
<tr>
<td><p>Last Name: </p></td>
<td><input type='text' name='client-last-name' maxlength='30' value='" . ucwords($insert_client_data['client-last-name']) . "' /></td>
</tr>
<tr>
<td><p>Password: </p></td>
<td><input type='text' name='client-password' maxlength='35' value='' /></td>
</tr>
<tr>
<td><p>Password Confirmed: </p></td>
<td><input name='client-password-confirmed' maxlength='35' type='text' value='' /></td>
</tr>
<tr>
<td><p>Live Site URL: </p></td>
<td><a href='" . $insert_client_data['client-live-site'] . "'>" . $insert_client_data['client-live-site'] . "</a></td>
</tr>
<tr>
<td><p>Development Site URL: </p></td>
<td><a href='" . $insert_client_data['client-development-site'] . "'>" . $insert_client_data['client-development-site'] . "</a></td>
</tr>
<tr>
<td><p>Email: </p></td>
<td><input type='text' name='client-email' maxlength='70' value='" . $insert_client_data['client-email'] . "' /></td>
</tr>
<tr>
<td><p>Telephone: </p></td>
<td><input type='text' name='telephone' maxlength='13' value='" . $insert_client_data['telephone'] . "' onfocus='javascript:getIt(this)' /></td>
</tr>
<tr>
<td><p>Address: </p></td>
<td><input type='text' name='client-address' value='" . $insert_client_data['client-address'] . "' /></td>
</tr>
<tr>
<td><p>AIM (Instant Messenger): </p></td>
<td><input type='text' name='client-aim' value='" . $insert_client_data['client-aim'] . "' /></td>
</tr>
<tr>
<td><p>Jabber: (Secondary Instant Messenger like GoogleTalk, Yahoo, etc...)</p></td>
<td><input type='text' name='client-jabber' value='" . $insert_client_data['client-jabber'] . "' /></td>
</tr>
<tr>
<td><p>Subscribed to our enewsletter?: </p></td>
<td><select name='subscribed-to-enewsletter'>
";
if ($insert_client_data['subscribed-to-enewsletter'] == 'Yes') {
echo "<option>Yes</option><option>No</option>";
} else {
echo "<option>No</option><option>Yes</option>";
}
echo "
</select></td>
</tr>
<tr>
<td colspan='2'>
<input type='submit' name='save' value='Save' />
</td>
</tr>
</table>
</form>
Code: Select all
$client_business_name = strtolower($_SESSION['client-business-name']);
$client_first_name = strtolower($_POST['client-first-name']);
$client_last_name = strtolower($_POST['client-last-name']);
$client_password = $_POST['client-password'];
$client_password_confirmed = $_POST['client-password-confirmed'];
$client_email = strtolower($_POST['client-email']);
$telephone = preg_replace("/[^0-9a-z_]/i", "", $_POST['telephone']);
$client_address = strtolower($_POST['client-address']);
$client_aim = strtolower($_POST['client-aim']);
$client_jabber = strtolower($_POST['client-aim']);
$subscribed_to_enewsletter = $_POST['subscribed-to-enewsletter'];
if (isset($_POST["save"]) && trim($_POST["save"])!=='') {
$update = "UPDATE `client-data` SET `client-first-name` = '$client_first_name', `client-last-name` = '$client_last_name', `client-password` = '$client_password', `client-password-confirmed` = '$client_password_confirmed', `client-email` = '$client_email', `telephone` = '$telephone', `client-address` = '$client_address', `client-aim` = '$client_aim', `client-jabber` = '$client_jabber', `subscribed-to-enewsletter` = '$subscribed_to_enewsletter' WHERE `client-business-name` = '$client_business_name'";
Last edited by Benjamin on Wed Jun 10, 2009 1:41 pm, edited 1 time in total.
Reason: Changed code type from text to php.
Reason: Changed code type from text to php.
Re: MySQL UPDATE, need help...
Firstly, you have no checks in place to make sure the values entered in the form are infact entered. If values are left out, your script will produce lots and lots of errors. 
You should have the basic checks in place (as I showed above).
That's just an example. So anyway, what do you want to do? Do you want the form to not update unless they enter a password, or are you looking to overwrite their current password with an MD5 hash of nothing (which does actually return a hash)? The best thing to do, is if they don't change their password, leave the current password as is. Code below...
I also noticed you are storing the password and the password confirmation in the database? The "confirm password" field is a client side check to ensure the user did not incorrectly enter their password. Storing it serves no purpose and increases your database overhead. Especially if you have alot of users. You should remove the column from the database (I have removed it from the code above).
You should have the basic checks in place (as I showed above).
Code: Select all
(isset($_POST['client-first-name']) && !empty($_POST['client-first-name'])) ? $client_first_name = strtolower($_POST['client-first-name']) : $client_first_name = '';Code: Select all
if (isset($_POST["save"]) && trim($_POST["save"])!=='')
{
// Check the two password fields to make sure they match and that they aren't empty
if(!empty($client_password) && $client_password == $client_password_confirmed)
{
$client_password = md5($client_password); // Hash the new password
$client_password_safe = true;
}
else
$client_password_safe = false;
$update = "UPDATE `client-data`
SET `client-first-name` = '$client_first_name',
`client-last-name` = '$client_last_name',";
if($client_password_safe)
$update .= "`client-password` = '$client_password',";
$update .= " `client-email` = '$client_email',
`telephone` = '$telephone',
`client-address` = '$client_address',
`client-aim` = '$client_aim',
`client-jabber` = '$client_jabber',
`subscribed-to-enewsletter` = '$subscribed_to_enewsletter'
WHERE `client-business-name` = '$client_business_name'";
}