MySQL UPDATE, need help...

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
WithHisStripes
Forum Contributor
Posts: 131
Joined: Tue Sep 13, 2005 7:48 pm

MySQL UPDATE, need help...

Post by WithHisStripes »

Heya - how do you tell MySQL to update a field unless the submitted field is blank?
AlanG
Forum Contributor
Posts: 136
Joined: Wed Jun 10, 2009 1:03 am

Re: MySQL UPDATE, need help...

Post by AlanG »

I assume your referring to a HTML form?

Such as:

Code: Select all

<form method="post" action="update_mysql.php">
<input type="text" name="important" size="20"/>
</form>
update_mysql.php

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.
WithHisStripes
Forum Contributor
Posts: 131
Joined: Tue Sep 13, 2005 7:48 pm

Re: MySQL UPDATE, need help...

Post by WithHisStripes »

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:

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&#058;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>
 
Processor:

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.
AlanG
Forum Contributor
Posts: 136
Joined: Wed Jun 10, 2009 1:03 am

Re: MySQL UPDATE, need help...

Post by AlanG »

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).

Code: Select all

(isset($_POST['client-first-name']) && !empty($_POST['client-first-name'])) ? $client_first_name = strtolower($_POST['client-first-name']) : $client_first_name = '';
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...

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'";
}
 
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).
Post Reply