SQL update statement in chronoforms?

aa

aaron 02 Feb, 2008

I'm new to Joomla & chronoforms, and with a little help I've put together a form which writes to a database & another one which displays the data.

I've also created an edit page (again using chronoforms) which accepts the record's ID as a paramenter & populates the edit page text boxes with that record's values.

Now I'm stuck, as I can't get it to save the updated values to the database. I assume I need to enter some modified code in the 'autogenerated code' section for the edit page to function as an update statement, but I don't know how to proceed. I have a basic knowledge of SQL syntax, but the information I've found on PHP & SQL doesn't seem to match the syntax chronoforms uses.

Thanks for any help.

here's my 'edit page' form code which receives the recordID parameter:

<?php
// initialise the database code
$recid = $_GET['recordID']; 
global $database;
$sql = "SELECT cf_id, name, location, notes 
FROM #__chronoforms_6
WHERE cf_id = $recid";

$database->setQuery($sql);
$rows = $database->loadObjectList();
foreach ( $rows as $record ) {
echo "

<p>
  <label>Name</label>
  <input type=\"text\" name=\"name\" value=\"".$record->name."\">
</p> 
<p>
  <label>Location</label>
  <input type=\"text\" name=\"location\" value=\"".$record->location."\">
</p> 
<p>
  <label>Site Notes</label>
  <textarea name=\"notes\" rows=\"5\" columns=\"20\">".$record->notes."</textarea>
</p>";
}

?>

<p>
<input type="submit" name="submit" value="Save Changes" />
</p>

Post edited by: aaron, at: 2008/02/01 23:10 Post edited by: aaron, at: 2008/02/01 23:28

Gr

GreyHead 02 Feb, 2008

Hi aaron,

The quick answer is that you can edit the Autogenerated code on your 'edit' form. Set the table name to be the same as the entry form, then use SQL like

UPDATE table_name 
  SET name='$_POST["name"]', 
      . . . 
  WHERE WHERE cf_id = $recid;

Bob

aa

aaron 02 Feb, 2008

Thanks very much for the reply!

My autogenerated code section on the edit form is totally empty.
I copied the original input form code and replaced the 'insert into' statement with the update. I get an error though -

Parse error: syntax error, unexpected T_STRING in [site name]/components/com_chronocontact/chronocontact.php(502) : eval()'d code on line 6

Below is the code I used, if you could tell me where I went wrong I'd really appreciate it.

Also, for my own reference - is this syntax specific to Joomla, Chronoforms, or neither?

Thanks again for the help

<?php 
	$database =& JFactory::getDBO();
	
$database->setQuery( UPDATE #__chronoforms_6 
SET name='$POST["name"]' , location='$POST["location"]' , notes='$POST["notes"]'

WHERE cf_id = $recid;
);

if (!$database->query()) {
echo "<script> alert('".$database->getErrorMsg()."'); window.history.go(-1); </script>
";
}
?>

Post edited by: aaron, at: 2008/02/02 00:02 Post edited by: aaron, at: 2008/02/02 00:08

Gr

GreyHead 02 Feb, 2008

Hi aaron,

I think you just need quotes round the sql string and the inside ones cleaned up:

<?php 
$database =& JFactory::getDBO();
$sql = "
  UPDATE #__chronoforms_6 
    SET name     = '".$POST['name']."', 
        location = '".$POST['location']."',   
        notes    = '".$POST["notes"]."'
    WHERE cf_id = $recid;";  
$database->setQuery( $sql );
if (!$database->query()) {
  echo "<script> alert('".$database->getErrorMsg()."'); window.history.go(-1); </script>";
}
?>

Bob

aa

aaron 02 Feb, 2008

Thanks again Bob, I'm not getting the error any more (just goes to the default submit screen) but it doesn't seem to be saving the data.

Anything else I could try? Post edited by: aaron, at: 2008/02/02 00:44

Gr

GreyHead 02 Feb, 2008

Hi aaron,

What I would do to debug this is to put in a statement to get the SQL command: print_r($sql); then copy and paste this into PHPMyAdmin to see what errors show up (Note MySQL errors are not terribly helpful).

Bob

aa

aaron 04 Feb, 2008

Thanks Bob,

Think I have got it working now - it wasn't populating the field values into the query. I added an underscore after each $ sign, and to populate the cf_id field I used a hidden form field.

Thanks again for all the help! Post edited by: aaron, at: 2008/02/04 19:12

Gr

GreyHead 05 Feb, 2008

Hi aaron,

Glad you've got it working (I should have spotted those missing underlines but I read right past them each time).

Bob

PS Answering a question from a few posts back: this syntax is fairly generic Joomla, not specific to ChronoForms (though each Joomla component has its own quirks).

Forums

SQL update statement in chronoforms?