Jump to content


Photo

database update form


  • Please log in to reply
5 replies to this topic

#1 mouli

mouli
  • Members
  • PipPip
  • Member
  • 23 posts

Posted 18 June 2006 - 11:52 PM

Hi
I have a form that uploads images to a mysql database and it works great. If it detects a duplicate entry based on the unique key it then uses an UPDATE query to update the record. This works as well except that you must enter values in all the form fields or it obviously updates those field with null values.
I'd like to be able to only update fields for which the posted variable from the form is not null so that it leaves fields unchanged where the posted variable is null.
Many thanks

mouli

#2 poirot

poirot
  • Members
  • PipPipPip
  • Advanced Member
  • 646 posts
  • LocationAustin, TX

Posted 19 June 2006 - 02:01 AM

Make PHP check whether the variables are null; if they are, don't do the query.
~ D Kuang

#3 mouli

mouli
  • Members
  • PipPip
  • Member
  • 23 posts

Posted 19 June 2006 - 03:23 AM

[!--quoteo(post=385498:date=Jun 19 2006, 02:01 PM:name=poirot)--][div class=\'quotetop\']QUOTE(poirot @ Jun 19 2006, 02:01 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Make PHP check whether the variables are null; if they are, don't do the query.
[/quote]
Thanks for that. What I cant figure out is how I use php to construct the query so it only updates fields for which the posted variable is not null. There are about 10 fields in the form, the user simply enters values in those fields that need updating, leaving the rest empty, so the query has to only update fields that are not null in the posted variables. Its the dynamic construction of the query that has got me stumped.

Many thanks for your help.

mouli


#4 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 19 June 2006 - 03:55 AM

there's the easy way and then there's the hard way:

the easy way: if your form variable names match your column names, you can run a loop to build your query like so:

simplified:
$sql = "update tablename set "; 
foreach($_POST as $key => $val) {
   if (isset($val) && trim($val) !== '') {
      $sql.= $key . "='" . $val . "',";
   }
}
$sql = substr_replace($sql,"",-1); //get rid of the last comma
$sql.= " where id = '$id'"; //assuming you are updating based on id
mysql_query($sql);

the hard way: doing the exact same thing except for with an if statement for each and every value, named individually.
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#5 mouli

mouli
  • Members
  • PipPip
  • Member
  • 23 posts

Posted 19 June 2006 - 04:13 AM

[!--quoteo(post=385514:date=Jun 19 2006, 03:55 PM:name=Crayon Violent)--][div class=\'quotetop\']QUOTE(Crayon Violent @ Jun 19 2006, 03:55 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
there's the easy way and then there's the hard way:

the easy way: if your form variable names match your column names, you can run a loop to build your query like so:

simplified:
$sql = "update tablename set "; 
foreach($_POST as $key => $val) {
   if (isset($val) && trim($val) !== '') {
      $sql.= $key . "='" . $val . "',";
   }
}
$sql = substr_replace($sql,"",-1); //get rid of the last comma
$sql.= " where id = '$id'"; //assuming you are updating based on id
mysql_query($sql);

the hard way: doing the exact same thing except for with an if statement for each and every value, named individually.
[/quote]

Mmmm, this looks good. Variables match columns so I'll give it a go and get back to you.
Many many thanks :)

#6 mouli

mouli
  • Members
  • PipPip
  • Member
  • 23 posts

Posted 28 June 2006 - 07:28 AM

Yup it worked a treat.
I only had to remove the $submit variable and it worked beautifully. Many thanks




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users