[SOLVED] Trouble with MySQL Update Command in PHP

[djpic]

I need some help with this.  For some reason, I am not able to update the database when I run this command.  (of course the data is coming from an HTML form).

 

Here is the code:

 

<?php
  define("DATABASE_SERVER","localhost");
  define("DATABASE_USERNAME","root");
  define("DATABASE_PASSWORD","webmaster");
  define("DATABASE_NAME","flhospital");

  $mysql = mysql_connect(DATABASE_SERVER, DATABASE_USERNAME, DATABASE_PASSWORD);

  mysql_select_db(DATABASE_NAME);

  function quote_smart($value)  {
    if (get_magic_quotes_gpc()) {
      $value = stripslashes($value);
    }
    if (!is_numeric($value)) {
      $value = "'" . mysql_real_escape_string($value) . "'";
    }
    return $value;
  }
  
  $Query = sprintf("UPDATE main SET Key='%s', PERIOD='%s', PERIOD_DATE='%s', PERIOD_DESC='%s', TEXT_BALLON='%s', ACT='%s', MA_1='%s', LPL_1='%s', UPL_1='%s', GREEN='%s', RED='%s', POS='%s', NEG='%s', DV='%s', CV='%s', MV='%s', PV='%s', MA_3='%s', LPL_3='%s', UPL_3='%s', BELOW='%s', WITHIN='%s', ABOVE='%s' WHERE RowID='%s'", quote_smart($_POST['keyid']), quote_smart($_POST['period']), quote_smart($_POST['period_date']), quote_smart($_POST['period_desc']), quote_smart($_POST['act']), quote_smart($_POST['ma1']), quote_smart($_POST['lpl1']), quote_smart($_POST['lpl1']), quote_smart($_POST['green']), quote_smart($_POST['red']), quote_smart($_POST['pos']), quote_smart($_POST['neg']), quote_smart($_POST['dv']), quote_smart($_POST['cv']), quote_smart($_POST['mv']), quote_smart($_POST['pv']), quote_smart($_POST['ma3']), quote_smart($_POST['lpl3']), quote_smart($_POST['upl3']), quote_smart($_POST['below']), quote_smart($_POST['within']), quote_smart($_POST['above'], quote_smart($_POST['rowid']));
  $Result = mysql_query( $Query ) or die (mysql_error());
?>

 

Even though I am using the die mysql_error; I am not getting an error!

[BlueSkyIS]

have you looked at the SQL that is generated? maybe set it to a variable and echo the variable to make sure the WHERE statement is what you expect.

[djpic]

I tried just placing an echo to post the $Query but it didn't work..nothing came up.  Then, tried an echo and just returned "test" and still nothing!  what is going on?  What am I missing?

 

<?php
  define("DATABASE_SERVER","localhost");
  define("DATABASE_USERNAME","root");
  define("DATABASE_PASSWORD","webmaster");
  define("DATABASE_NAME","flhospital");

  $mysql = mysql_connect(DATABASE_SERVER, DATABASE_USERNAME, DATABASE_PASSWORD);

  mysql_select_db(DATABASE_NAME);

  function quote_smart($value)  {
    if (get_magic_quotes_gpc()) {
      $value = stripslashes($value);
    }
    if (!is_numeric($value)) {
      $value = "'" . mysql_real_escape_string($value) . "'";
    }
    return $value;
  }
  
  $Query = sprintf("UPDATE main SET Key='%s', PERIOD='%s', PERIOD_DATE='%s', PERIOD_DESC='%s', TEXT_BALLON='%s', ACT='%s', MA_1='%s', LPL_1='%s', UPL_1='%s', GREEN='%s', RED='%s', POS='%s', NEG='%s', DV='%s', CV='%s', MV='%s', PV='%s', MA_3='%s', LPL_3='%s', UPL_3='%s', BELOW='%s', WITHIN='%s', ABOVE='%s' WHERE RowID='%s'", quote_smart($_POST['keyid']), quote_smart($_POST['period']), quote_smart($_POST['period_date']), quote_smart($_POST['period_desc']), quote_smart($_POST['act']), quote_smart($_POST['ma1']), quote_smart($_POST['lpl1']), quote_smart($_POST['lpl1']), quote_smart($_POST['green']), quote_smart($_POST['red']), quote_smart($_POST['pos']), quote_smart($_POST['neg']), quote_smart($_POST['dv']), quote_smart($_POST['cv']), quote_smart($_POST['mv']), quote_smart($_POST['pv']), quote_smart($_POST['ma3']), quote_smart($_POST['lpl3']), quote_smart($_POST['upl3']), quote_smart($_POST['below']), quote_smart($_POST['within']), quote_smart($_POST['above'], quote_smart($_POST['rowid']));
  //$Result = mysql_query( $Query ) or die (mysql_error());
  
  echo "Test";
?>

[djpic]

Ok, solves the nothing problem.  Now I have another problem.  It excutes the SQL command but returns an error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Key=1234, PERIOD=4321, PERIOD_DATE=2, PERIOD_DESC=2, MARKER='', ACT=2, MA_1=2, L' at line 1

 

Here is the new mofidied code.  The last problem was I forgot a field and didn't close a ( with a ):

<?php
  define("DATABASE_SERVER","localhost");
  define("DATABASE_USERNAME","root");
  define("DATABASE_PASSWORD","webmaster");
  define("DATABASE_NAME","flhospital");

  $mysql = mysql_connect(DATABASE_SERVER, DATABASE_USERNAME, DATABASE_PASSWORD) or die("could not connect");

  mysql_select_db(DATABASE_NAME) or die ("could not connect to database");

  function quote_smart($value)  {
    if (get_magic_quotes_gpc()) {
      $value = stripslashes($value);
    }
    if (!is_numeric($value)) {
      $value = "'" . mysql_real_escape_string($value) . "'";
    }
    return $value;
  }
  
  $Query = sprintf("UPDATE main SET Key=%s, PERIOD=%s, PERIOD_DATE=%s, PERIOD_DESC=%s, MARKER=%s, ACT=%s, MA_1=%s, LPL_1=%s, UPL_1=%s, GREEN=%s, RED=%s, POS=%s, NEG=%s, DV=%s, CV=%s, MV=%s, PV=%s, MA_3=%s, LPL_3=%s, UPL_3=%s, BELOW=%s, WITHIN=%s, ABOVE=%s WHERE RowID=%s", quote_smart($_POST['keyid']), quote_smart($_POST['period']), quote_smart($_POST['period_date']), quote_smart($_POST['period_desc']), quote_smart($_POST['marker']), quote_smart($_POST['act']), quote_smart($_POST['ma1']), quote_smart($_POST['lpl1']), quote_smart($_POST['upl1']), quote_smart($_POST['green']), quote_smart($_POST['red']), quote_smart($_POST['pos']), quote_smart($_POST['neg']), quote_smart($_POST['dv']), quote_smart($_POST['cv']), quote_smart($_POST['mv']), quote_smart($_POST['pv']), quote_smart($_POST['ma3']), quote_smart($_POST['lpl3']), quote_smart($_POST['upl3']), quote_smart($_POST['below']), quote_smart($_POST['within']), quote_smart($_POST['above']), quote_smart($_POST['rowid']));
  $Result = mysql_query( $Query ) or die (mysql_error());
?>

[atlanta]

Ok, solves the nothing problem.  Now I have another problem.  It excutes the SQL command but returns an error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Key=1234, PERIOD=4321, PERIOD_DATE=2, PERIOD_DESC=2, MARKER='', ACT=2, MA_1=2, L' at line 1

 

Here is the new mofidied code.  The last problem was I forgot a field and didn't close a ( with a ):

<?php
  define("DATABASE_SERVER","localhost");
  define("DATABASE_USERNAME","root");
  define("DATABASE_PASSWORD","webmaster");
  define("DATABASE_NAME","flhospital");

  $mysql = mysql_connect(DATABASE_SERVER, DATABASE_USERNAME, DATABASE_PASSWORD) or die("could not connect");

  mysql_select_db(DATABASE_NAME) or die ("could not connect to database");

  function quote_smart($value)  {
    if (get_magic_quotes_gpc()) {
      $value = stripslashes($value);
    }
    if (!is_numeric($value)) {
      $value = "'" . mysql_real_escape_string($value) . "'";
    }
    return $value;
  }
  
  $Query = sprintf("UPDATE main SET Key=%s, PERIOD=%s, PERIOD_DATE=%s, PERIOD_DESC=%s, MARKER=%s, ACT=%s, MA_1=%s, LPL_1=%s, UPL_1=%s, GREEN=%s, RED=%s, POS=%s, NEG=%s, DV=%s, CV=%s, MV=%s, PV=%s, MA_3=%s, LPL_3=%s, UPL_3=%s, BELOW=%s, WITHIN=%s, ABOVE=%s WHERE RowID=%s", quote_smart($_POST['keyid']), quote_smart($_POST['period']), quote_smart($_POST['period_date']), quote_smart($_POST['period_desc']), quote_smart($_POST['marker']), quote_smart($_POST['act']), quote_smart($_POST['ma1']), quote_smart($_POST['lpl1']), quote_smart($_POST['upl1']), quote_smart($_POST['green']), quote_smart($_POST['red']), quote_smart($_POST['pos']), quote_smart($_POST['neg']), quote_smart($_POST['dv']), quote_smart($_POST['cv']), quote_smart($_POST['mv']), quote_smart($_POST['pv']), quote_smart($_POST['ma3']), quote_smart($_POST['lpl3']), quote_smart($_POST['upl3']), quote_smart($_POST['below']), quote_smart($_POST['within']), quote_smart($_POST['above']), quote_smart($_POST['rowid']));
  $Result = mysql_query( $Query ) or die (mysql_error());
?>

 

Its in your query statement put ` around your field names

[djpic]

That worked!  Thanks.