convert from insert to update (mysql data)

Johnny
Johnny used Ask the Experts™
on
i had came across this nifty script a bit back but i now have need to make it update instead of insert
can someone help me convert this so it can be used on a an edit for please

i need to pass the value of
where ID='".$id."'

please see snip attached

thank you in advance  for any code or help you may provide
$TableName="contacts";

function quote_smart($val)
{
    if( is_array($val) ) {
        return array_map("quote_smart", $val);
    } else {
        if( get_magic_quotes_gpc() ) {
            $val = stripslashes($val);
        }
        if( $val == '' ) {
            //$val = 'No Description Available';
            $val = '';
        } if( !is_numeric($val) || $val[0] == '0' ) {
            $val = "'".mysql_real_escape_string($val)."'";
        }
        return $val;
    }
}

$keyStr='';$valStr='';
$dontUse = array("submit");
while (list ($key, $val) = each ($HTTP_POST_VARS)) {
      if (!in_array($key,$dontUse)){      if($keyStr) $keyStr.=',';
      $keyStr.= str_replace(')','',$key);
      if($valStr) $valStr.=',';
      $valStr.= quote_smart($val);
      $message .=" $key &nbsp;=&nbsp; $val<br>\n";
      //echo $message;
      }
}
// Enter Info into the Database.
// where ID='".$id."' <<< need clause
$insert = "INSERT INTO $TableName($keyStr) VALUES($valStr)";
mysql_query ($insert, $mysql_link)
or die(mysql_error());
$count=0;
$i=1;

while($i <= 4)
{
  if($HTTP_POST_VARS["item$i"] != NULL)
  {
    if($count == 0)
      $items .= $HTTP_POST_VARS["item$i"];
    else
      $items .= ",".$HTTP_POST_VARS["item$i"];

      $count++;
  }
  $i++;
  next;
}

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Greg AlexanderLead Developer

Commented:
Is this posting from a form?
Greg AlexanderLead Developer

Commented:
nvm that last comment, I see now

Author

Commented:
i came across this code works great for adding a record just want to use for updating the record.

where should probably be a hidden post value huh
like
<input name='id' type='hidden' value='1'>
Greg AlexanderLead Developer

Commented:
Yeah, you can post it ad a hidden id.. I am almost done rewriting ift for ya

Author

Commented:
sweet
Greg AlexanderLead Developer

Commented:
I can't spell today.. sorry
Lead Developer
Commented:
I have no way of testing it, so it may be a couple trys but here you go
<?
$TableName="contacts";
$id = 10;//or $id = $_POST['id'];

function quote_smart($val)
{
    if( is_array($val) ) {
        return array_map("quote_smart", $val);
    } else {
        if( get_magic_quotes_gpc() ) {
            $val = stripslashes($val);
        }
        if( $val == '' ) {
            //$val = 'No Description Available';
            $val = '';
        } if( !is_numeric($val) || $val[0] == '0' ) {
            $val = "'".mysql_real_escape_string($val)."'";
        }
        return $val;
    }
}

$keyStr='';$valStr='';
$dontUse = array("submit => '', id => ''");
while (list ($key, $val) = each ($HTTP_POST_VARS)) {
  if (!in_array($key,$dontUse)){      
    $query = "update $TableName set $key = '$val' where ID = '$id'";
    mysql_query($query, $mysql_link)or die(mysql_error());
  }
}

$count=0;
$i=1;

while($i <= 4)
{
  if($HTTP_POST_VARS["item$i"] != NULL)
  {
    if($count == 0)
      $items .= $HTTP_POST_VARS["item$i"];
    else
      $items .= ",".$HTTP_POST_VARS["item$i"];

      $count++;
  }
  $i++;
  next;
}

?>

Open in new window

Author

Commented:
testing
Hi,

The below is another option, it will create just 1 sql statement to run instead of one per key, as well as you still need to mysql_real_escape_string the posted id as it is easily possible for a user to modify that id and put malicious code into it.  I also placed `` around each of the fieldname values and did a mysql_real_escape_string on them as a user could also modify the post variable name and since you are placing those keys directly into the sql it is also an injection point.
$TableName="contacts";

function quote_smart($val)
{
    if( is_array($val) ) {
        return array_map("quote_smart", $val);
    } else {
        if( get_magic_quotes_gpc() ) {
            $val = stripslashes($val);
        }
        if( $val == '' ) {
            //$val = 'No Description Available';
            $val = '';
        } if( !is_numeric($val) || $val[0] == '0' ) {
            $val = "'".mysql_real_escape_string($val)."'";
        }
        return $val;
    }
}

$updateStr='';$valStr='';
$dontUse = array("submit");
while (list ($key, $val) = each ($HTTP_POST_VARS)) {
      if (!in_array($key,$dontUse))
      {
         if(strlen($updateStr)>0) $updateStr.=', ';
         $updateStr .= "`" . mysql_real_escape_string(str_replace(')','',$key)) . "`";
         $updateStr .= "=" . quote_smart($val);
         $message .=" $key &nbsp;=&nbsp; $val<br>\n";
      //echo $message;
      }
}
$id = mysql_real_escape_string($_POST['id']);
$strWhere = " where id = '$id';
// Enter Info into the Database.
// where ID='".$id."' <<< need clause
$update = "Update $TableName set $updateStr $strWhere";

Open in new window

Greg AlexanderLead Developer

Commented:
Change the while loop to include your quote_smart() function
while (list ($key, $val) = each ($HTTP_POST_VARS)) {
  if (!in_array($key,$dontUse)){      
    $query = "update $TableName set $key = '".quote_smart($val)."' where ID = '$id'";
    mysql_query($query, $mysql_link)or die(mysql_error());
  }
}

Open in new window

Author

Commented:
i see we have
if (!in_array($key,$dontUse)){

but im still getting
Unknown column 'submit' in 'field list'
from both examples

Author

Commented:
nvm found it

Author

Commented:
thx for the help

Author

Commented:
both worked great thank you
hi,

just a note, if you use the first example, please make sure to mysql_real_escape_string your keys as well as your  values, but don't use the quote_smart function to do it because you don't want quotes around the key names, if anything you want ` around the key names.

Glad these worked for you

Author

Commented:
i used both some elements where missing from both i combined them.. thanks again and for the heads up too

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial