[RESOLVED] Insert/Remove Portion of Query Depending on Form Field

partypete

Hello again,

I have a form that updates an employee's information including a password for that employee. The form has a password field and what I would like is if the admin leaves that field empty, the users password remains unchanged in the database... but if the admin enters text into the password field then that text should update the database as the employees new password.

The problem is to do this I need to either insert or remove a portion of the sql query. (There's probably an easier way to do this that I'm not seeing at 5:30AM.) But I don't know how to store the query portion outside of the actual query and then insert it when needed.

So let's say this is the basic query. This is what I want if the password FORM field is EMPTY:

UPDATE employees SET
employee_fullname = '".mysql_real_escape_string($_POST['employee_fullname'])."'
WHERE employee_username = '".mysql_real_escape_string($_POST['un'])."'";

But if the admin fills in the password FORM field, I want to insert an additional portion of sql to update the password field in the database like so:

UPDATE employees SET
employee_password = PASSWORD('".mysql_real_escape_string($_POST['employee_password'])."'),
employee_fullname = '".mysql_real_escape_string($_POST['employee_fullname'])."'
WHERE employee_username = '".mysql_real_escape_string($_POST['un'])."'";

So essentially if the admin enters a password into the update record form - include the following sql in the query - otherwise don't add the sql.

employee_password = PASSWORD('".mysql_real_escape_string($_POST['employee_password'])."'),

How would I store that sql string in a variable or something and then insert it into the query at the appropriate time??

Thanks again!
Peter

partypete

Nevermind. Got it working!

Thanks.

Piranha

This should do it:

$sql =  "UPDATE employees SET ";
if (!isempty($_POST['employee_password']
{
    $sql = $sql . "employee_password = PASSWORD('".mysql_real_escape_string($_POST['employee_password'])."'),"
}
$sql = "employee_fullname = '".mysql_real_escape_string($_POST['employee_fullname'])."'
WHERE employee_username = '".mysql_real_escape_string($_POST['un'])."'";

Or you could do something like this:

if (!empty($_POST['employee_password']
{
    $pass = "employee_password = PASSWORD('".mysql_real_escape_string($_POST['employee_password'])."'),";
}
else
{
    $pass = "";
}

UPDATE employees SET
" . $pass . "
employee_fullname = '".mysql_real_escape_string($_POST['employee_fullname'])."'
WHERE employee_username = '".mysql_real_escape_string($_POST['un'])."'";

Edit: Note that it's a bad idea to store passwords as plain text in the database. It's much better to encrypt it in some way.

Weedpacket

partypete wrote:
Nevermind. Got it working!

Care to give back to the community by saying how? Kind of as a thanks for taking the time to read your post in the first place?

partypete

Sorry Weedpacket, I usually do but this one was sort of a "duh" and didn't think anyone would be interested.

I used something similar to Piranha's second suggestion.

// Only change password if new one provided
if($_POST['employee_password'] != ""){
 $changepassword = "employee_password = PASSWORD('".mysql_real_escape_string($_POST['employee_password'])."'),";
 } else {
 $changepassword = "";
}
$updateSQL = "
UPDATE
 employees
SET
 ".$changepassword."
 employee_fullname = '".mysql_real_escape_string($_POST['employee_fullname'])."',

This is the first time I used mysql_real_escape_string and I think I was just confused. I thought at first that because mysql_real_escape_string isn't inside the query immediately that I would get mysql errors. So I was trying to turn it into a string with plans to echo it inside the query and it didn't work right. Real brain fart. 😛

Thank you both!

Weedpacket

Not a problem; you'll be surprised at the sort of brain farts I can have - or maybe not. But even mistakes like this can be instructive to newbies where the solution might otherwise elude them for hours.