Setting a MySQL field to blank whenthe field is left or changed to blank??

[jeffkee]

if($HTTP_POST_VARS["age"] != ""){ $age = htmlspecialchars($HTTP_POST_VARS["age"]);mysql_query("UPDATE users SET age='$age' WHERE ID='$SESSION[ID]'");}
else{mysql_query("UPDATE users SET age=NULL WHERE ID='$SESSION[ID]'");}

if($HTTP_POST_VARS["location"] != ""){ $location = htmlspecialchars($HTTP_POST_VARS["location"]);mysql_query("UPDATE users SET location='$location' WHERE ID='$SESSION[ID]'");}
else{mysql_query("UPDATE users SET location=NULL WHERE ID='$SESSION[ID]'");}

if($HTTP_POST_VARS["occupation"] != ""){ $occupation = htmlspecialchars($HTTP_POST_VARS["occupation"]);mysql_query("UPDATE users SET occupation='$occupation' WHERE ID='$SESSION[ID]'");}
else{mysql_query("UPDATE users SET occupation=NULL WHERE ID='$SESSION[ID]'");}

This is the code i used in attempt to make a field go blank when the input form is blank.

I tried it without all the ifs - just this

$age = htmlspecialchars($HTTP_POST_VARS["age"]);mysql_query("UPDATE users SET age='$age' WHERE ID='$_SESSION[ID]'");

I thought the blank would be automatically saved intothe MySQL field, but I guess not.

I tried so many different things, and it does not work properly for some reason..

The one above for example, sets all the values in that row to NULL, even if I put stuff into the form. The names on the inputs of the forms are correct - they were working before with teh simpler script, except they did not update the values to blank when I put in blank.

Please help me. Thank you.

[Norman_Graham]

Hi Jeff

Please use the PHP coding tags (see the buttons above) for adding PHP to your post. The code you have posted is unreadable. You add code like this:

if($HTTP_POST_VARS["age"] != "")
{
$age = htmlspecialchars($HTTP_POST_VARS["age"]);
mysql_query("UPDATE users SET age='$age' WHERE ID='$_SESSION[ID]'");
} 

else
{
mysql_query("UPDATE users SET age=NULL WHERE ID='$_SESSION[ID]'");
} 

Now the first thing is, why do you want to 'add' a null value to an empty field? Just add the values you get from your form. If the values is empty, then nothing will be added.
Next thing: if you are asking the users to fill in a form, I would make at least some of the fields required fields. During form validation, the user will be forced to fill in certain fields before the form can be accepted. As far as I can make out, you're not doing any form validation at all, but just accepting whatever the user enters, which may be nothing.
Third thing: collect all your values and then enter them into the table in one single query, not multiple separate queries for each value. If you get a lot of traffic, your current code will kill the database.
Fourth thing: I suspect your variables are not passing from the form to your insertion script, hence the insertion of null values everywhere. Could it be that you have forgotten to use session_start() at the top of your script, thus ensuring that the session ID is always empty?
Fifth thing: I recommend using all uppercase for SQL commands and lowercase only for database, table and field names. You've got a mixture of both in your field names, which is a surefire method of producing bugs caused by spelling mistakes.
Last thing: which version of PHP are you using? If it's a recent version of PHP 4, then I recommend waving goodbye to the long-notation superglobals and using $_POST[] instead. As you are already using $_SESSION[], then you must be using a recent version of PHP4. Whatever - try and standardize your superglobal calls. You'll thank yourself in a few months.

HTH

Norm

[jeffkee]

Hi Norman, thanks for taking the time to help..

THis form is a secondary form that comes after they've become a member, so there is no validation required - they can leave teh fields empty.

And as for putting all the data in at once - you're absolutely right, I overlooked that completely.

I'm a real rookie, learned this PHP only a month and a half ago..

The data is passing through for sure - because when things ARE filled in, it goes on properly.

The reason why I want to add a NULL value is because, I want to enable users to take off information.

For instance, if they already have an msn address listed, and want to take it off, I want them to be able to go back to the form, leave it blank, and fill in whatever else they want to fill, and click submit, and the emtpy fields will go NULL.

So my SQL syntax of

mysql_query("UPDATE users SET age=NULL where id=$_SESSION[ID]");

is correct, right?

I'm really confused here, because the form works when I start off with an empty database(new user), and i fill inonly some of it, and it goes on.

And then when i come back, the fields will automatically show the value (<input.... value="<?php echo $userdata["msn"]?>">), and when i delete one of the values, intending to make it NULL again, all the fields go NULL!!!

So passing of information from teh form to the script is working....

I am a beginner, and I am really severely confused...

Thank you so much for your help.. I just need some more advice.

Or maybe I should just add a link that sends the user to the script that deletes the value for that specific field??? And leaving the form blank will just not update anything... would that be an easier option?

[Norman_Graham]

Hi Jeff

Hope you're having fun with PHP. I'm still a newbie, but I like to try my best to help complete beginners when I can.

OK - I think it's a bad idea to delete stuff from the DB just because a user has left a field empty. If I forget to fill in a field, your code is going to start deleting my info. I might be annoyed about that. I think you should only delete data if the user has expressly requested you to do so. You could do this with checkboxes or something. Another option would be to present the user with the form containing the values already entered into the database and give them the option of deleting some of the values. Don't however, allow them to delete required information.

Personally, unless my DB is likely to get heavy traffic, I'd be inclined to not delete anything at all and just add yes/no flags to particular fields, e.g. I have a DB with user comments. I let all the comments just flow into the database and have an extra field called 'publish' which is set to 'no' by default. I check the comments and change the flag to 'yes' if the comments are acceptable and not likely to attract litigation. You could do something similar with your MSN addresses - create an extra yes/no field.

Anyway, to get back to your problem: does the first form enter the session ID into the DB? I assume it does, otherwise your second form wouldn't work. So you have a field in the DB called 'id' and it contains the session id of the person using the form? I ask because it seems that your WHERE clauses are not working - all your statements are defaulting to the else{} clauses. This leads me to suspect that something is wrong with the ID. Have you set the session ID anywhere? You have to set it - it won't just set itself.

I would do two things:

// use isset
if (isset($_POST["occupation"])
{ // do some stuff

//add quotes
$_SESSION["ID"]

HTH

Norm

PS: If this doesn't help, post more details about what your users and your code are actually supposed to be doing and post some info about the structure of your DB tables.

[jeffkee]

Thanks again Norman.

Ya i'm having funwith PHP for sure. There's so much you can do with it.

The SESSION ID is set for sure - it works everywhere else. Most of these pages automatically load up the user's name saying

Welcome Norman! YOu have been a member since XXXX-XX-XX

kinda thing. The session ID is set as soon as people log in, and all the pages that require the member to be signed in are created like this :

if ($_SESSION["ID"] )   {code to execute for members, such as editing profile, etc.}  

else 
{echo "You must be signed in to access this feature. Thank you.<br><a href=\"memberlogin.php\">Log In</a>";}

And I'll takethat checkbox advice - sounds far better to me, actually, and it'll look fancy..

so what i'm doing is, making a checkbox input with the names d_age, d_location, d_occupation, and then using the if to check if its set or not, and if the checkbox is set, I give an empty value to $age, $location, $occupation, right?

[jeffkee]

if($HTTP_POST_VARS["age"]){mysql_query("UPDATE users SET age='$HTTP_POST_VARS[age]' WHERE ID='$_SESSION[ID]'");}
if($HTTP_POST_VARS["location"]){mysql_query("UPDATE users SET location='$HTTP_POST_VARS[location]' WHERE ID='$_SESSION[ID]'");}
if($HTTP_POST_VARS["occupation"]){mysql_query("UPDATE users SET occupation='$HTTP_POST_VARS[occupation]' WHERE ID='$_SESSION[ID]'");}
if($HTTP_POST_VARS["about"]){mysql_query("UPDATE users SET about='$HTTP_POST_VARS[about]' WHERE ID='$_SESSION[ID]'");}
if($HTTP_POST_VARS["msn"]){mysql_query("UPDATE users SET msn='$HTTP_POST_VARS[msn]' WHERE ID='$_SESSION[ID]'");}
if($HTTP_POST_VARS["yahoo"]){mysql_query("UPDATE users SET yahoo='$HTTP_POST_VARS[yahoo]' WHERE ID='$_SESSION[ID]'");}
if($HTTP_POST_VARS["icq"]){mysql_query("UPDATE users SET icq='$HTTP_POST_VARS[icq]' WHERE ID='$_SESSION[ID]'");}
if($HTTP_POST_VARS["aim"]){mysql_query("UPDATE users SET aim='$HTTP_POST_VARS[aim]' WHERE ID='$_SESSION[ID]'");}
if($HTTP_POST_VARS["dislike"]){mysql_query("UPDATE users SET dislike='$HTTP_POST_VARS[dislike]' WHERE ID='$_SESSION[ID]'");}

I backtracked, started writing the script again from the beginning. Without putting any complications such as htmlspecialchars($_POST["age"]), I just wrote this.

THis worked for updating the database.

I changed it to

mysql_query("UPDATE users SET age='$_POST[age]' WHERE ID='$_SESSION[ID]'");
mysql_query("UPDATE users SET location='$_POST[location]' WHERE ID='$_SESSION[ID]'");
mysql_query("UPDATE users SET occupation='$_POST[occupation]' WHERE ID='$_SESSION[ID]'");
mysql_query("UPDATE users SET about='$_POST[about]' WHERE ID='$_SESSION[ID]'");
mysql_query("UPDATE users SET msn='$_POST[msn]' WHERE ID='$_SESSION[ID]'");
mysql_query("UPDATE users SET yahoo='$_POST[yahoo]' WHERE ID='$_SESSION[ID]'");
mysql_query("UPDATE users SET icq='$_POST[icq]' WHERE ID='$_SESSION[ID]'");
mysql_query("UPDATE users SET aim='$_POST[aim]' WHERE ID='$_SESSION[ID]'");
mysql_query("UPDATE users SET dislike='$_POST[dislike]' WHERE ID='$_SESSION[ID]'");

Which is the exact same thing except I got the aprt where it checks for the if - and just update it no amtter what.

What happened with this one was, when at least one of the fields were blank, all the fields affected by the SQL queries were all set to blank, although some of the form inputs WERE populated!!!

Also I added a line that displays all the $_POST[xx] values one by one, and all of them are there!!!! The values are gettingthere but somehow not getting in to the table when I get rid of the if.

I wonder why?? Cause I thought this way, the blank fields would be saved to blank -

And about the concern of blank fields being left unintentionally - it won't happen.

Each input on the form displays teh current value of the table, so basically if you opened the form, and clicked submit right away, it would upload the exact same data again, which will not make any difference.

So unless the user intentionally clears out a input field, it will not go blank.