Update Database troubles

Hello everyone,

I'm rather new to dealing with databases, so please bear with me. I'm going to explain what I want to do first, show you my code, and then say what's happening.

What I'm aiming to do is to create a form that if the logged in user hasn't filled out before creates a new row, and if the logged in user has filled it out before updates the existing row. I have attempted to do this via the $my->id to get the logged in user id and check it against the existing user ids in the table. I have modified the autogenerated code (I know it says not to, but this is almost working) to perform the check and then carry out one of two actions.

<?php 
global $database, $my;

$sql = "
  SELECT * 
    FROM #__chronoforms_8 
    WHERE userid = $my->id";
 
$database->setQuery($sql);

$user = NULL;
$database->loadObject($user);
if ($user->userid == $my->id) {
  $database->setQuery( "UPDATE #__chronoforms_8 SET
    surname = mosGetParam($_POST,'surname','') ,
    fname = mosGetParam($_POST,'fname','') ,
    school = mosGetParam($_POST,'school','') ,
    discipline =mosGetParam($_POST,'discipline','')
    WHERE userid = $my->id");

  if (!$database->query()) {
  echo "<script> alert('".$database->getErrorMsg()."');  
    window.history.go(-1); </script>";
  }
} else { 
  echo "Else";
  srand((double)microtime()*10000);
  $inum	= "I" . substr(base64_encode(md5(rand())), 0, 16);

  $database->setQuery("INSERT INTO #__chronoforms_8 
    VALUES
    ('' , '".$inum."','". date('Y-m-d')." -     
   .date("H:i:s")."', '".$_SERVER['REMOTE_ADDR']."' , 
  '".mosGetParam($_POST,'userid','')."' , 
  '".mosGetParam($_POST,'surname','')."' , 
  '".mosGetParam($_POST,'fname','')."' , 
  '".mosGetParam($_POST,'school','')."' , 
  '".mosGetParam($_POST,'discipline','')."');" );
  
  if (!$database->query()) {
    echo "<script> alert('".$database->getErrorMsg()."'); 
      window.history.go(-1); </script>";
  }
}
?>

What is working is that if the user has filled out the form, the program enters the code under the IF statement, and if they have not, it skips to the ELSE. As well, the ELSE code works as expected (as its the same as the original autogenerated code), but the UPDATE does not change anything in the table.

Can anyone tell me what I'm doing wrong to update the table row?

Thanks,
-Jonathan

Hi Jonathan,

I think the problem is that you aren't loading the user object correctly

$user = NULL; 
$database->loadObject($user);

probably does nothing useful.

Bob

Hi Bob,

I've echoed $user->userid and it is returning the correct value and the program does enter the IF statement. As far as I can tell the only issue is that $database->setQuery( "UPDATE... does not actually update the row.

Hi Pietro,

If there is SQL error then the update will show the error box, sometimes this doesnt show because of a JS error with anything at your site, if you have Firefox with Firebug and got a JS error at the submit page then look at the error because may be its the alert box with error!

another turnaorund is to echo the whole SQL statement and check it for errors or paste it in PHPmyadmin and see if it will generate errors there or will get executed successfully!

try adding '' around $my->id

Cheers

Max

Hi Pietro,

I agree with Max, pull out the SQL statement and test it in PHPMyAdmin, I've added '' round all the values here:

$sql = "UPDATE #__chronoforms_8 SET 
    surname = '".mosGetParam($_POST, 'surname', '')."' , 
    fname = '".mosGetParam($_POST, 'fname', '')."' , 
    school = '".mosGetParam($_POST, 'school', '')."' , 
    discipline = '".mosGetParam($_POST, 'discipline', '')."' 
    WHERE userid = '$my->id'";
  if ( $debug ) {
    echo "sql: ";print_r($sql);echo"<br />";
  }
$database->setQuery( $sql ); 

Bob

You guys are awesome, thanks to your suggestions I have it working now!