Saving in database
husainsn - October 23, 2007 - 17:56
Hi
I would appreciate if someone could point towards documentation or tutorial to save data from a Drupal form in a database. It is a very basic step with which I am familiar in settings out side Drupal.
I created a form using Formapi, now I want to use this form to populate a table. I created the table and have insert SQl statement ready. Thanks
Is this form for node content or something else?
Are you extending the node (using hook_node_info to define the node type and hook_form to produce the form) or is it a custom form?
It is a form
It is an ordinary form. I constructed it using info from the book Drupal Pro. It is in fact a new module using FileApi. It has an .info file and module in which implemented are: hook_menu(), function example_nameform(), validate function and post handling submission. All I want is to be able to recognize inputs in form fields, and use insert clause. The data is simple name, addresses etc for a petition. I do not want to use civicrm with its 30 plus tables and overhead.
It is such a basic action and unfortunately I can not find any documentation. Any insight will be appreciated. Thanks in advance.
"post handling submission" ?
By "post handling submission" do you mean a submit function? Typically the function that produces the form is the form id, so if your form function (form id) is 'yourmodule_form', Your submit function would then be 'yourmodule_form_submit' and declared as
function yourmodule_form_submit($form_id, $form_values) {}
$form_values contains the values in the form (there is no need to use $_POST and is recommended that it is not). $form_values is an array and reflects the structure of your form so if you have added a form element as
$form['name'] = array(...)then you will find that value as$form_values['name'].To save the data you would call db_query with the approriate SQL. Using name as an example field and yourtable as the table name this would look something like
$sql = "INSERT INTO {yourtable} (name) VALUES('%s')";db_query($sql, $form_values['name']);
VALUES should use %d for numbers, '%s' for strings, there should be one for each field be inserted. The call to db_query should iinclude the SQL as the first argument and then one argument for each of the values being saved. So for example saving name and address would look like
$sql = "INSERT INTO {yourtable} (name,address) VALUES('%s','%s')";db_query($sql, $form_values['name'], $form_values['address']));
Saving Form input in DB
Please excuse my shameless insistence for assistance...
The code for the module is below. There are 5 fields in the DB: Incremental no (Primary key), datetime, firstname, lastname & city.
The form has only 3 fields (first, last & city) the other two are in sql as 0, Now(). I can only save primary key & datetime, nothing else. Thanking you for your help.
The code is as follows. Please check Submit clause :
<?php
// $Id$
/**
*Implementation of hook_menu().
*/
function formexample_menu($may_cache){
$items = array();
if ($may_cache) {
$items[] = array(
'path' => 'formexample',
'title' => t('View the Form'),
'callback' => 'formexample_page',
'access' => TRUE
);
}
return $items;
}
/**
* called when user goes to example.com/?q=formexample
*/
function formexample_page() {
$output = t('This page contains our example form');
//return the HTML generated form the $form data structure.
$output .= drupal_get_form( 'formexample_nameform');
return $output;
}
/**
* Defines a form
*/
function formexample_nameform() {
$form['first_name'] = array(
'#title' => t('First Name'),
'#type' => 'textfield',
'#description' => t(''),
);
$form['last_name'] = array(
'#title' => t('Last Name'),
'#type' => 'textfield',
'#description' => t(''),
);
$form['city'] = array(
'#title' => t('City'),
'#type' => 'textfield',
'#description' => t(''),
);
$form['submit'] = array(
'#type' => 'submit',
'#value' => t('submit')
);
return $form;
}
/**
* Validate the form
*/
function formexample_nameform_validate($form_id, $form_values) {
if ($form_values['user_name'] == 'King Kong') {
//we notify the form API that this field has failed validation.
form_set_error ('user_name',
t('King Kong is not allowed to use this form.'));
}
}
/**
* Handle post-validation form submission
*/
function formexample_nameform_submit($form_id, $form_values) {
$name = $form_values['number'];
drupal_set_message(t('Thanks for Petioning. %name', array('%name' => $name)));
}
$sql= "INSERT into {petition_ihm} (id, datetime, first_name, last_name, city)VALUES(0, NOW(), '%s', '%s', '%s')";
db_query ($sql, $form_values['number'], $form_values['date_time'], $form_values['first_name'], $form_values['last_name'], $form_values['city']);
?>
This should help
You do not need to insert id since it is auto incrementing, the way you are doing it now only the first insert will work as they would all have an id of 0. Also you have two many parameters for db_query(), some of them having non existant values. So lets change the SQL part to
$sql= "INSERT into {petition_ihm} (datetime, first_name, last_name, city)VALUES(%d, '%s', '%s', '%s')";db_query ($sql, time(), $form_values['first_name'], $form_values['last_name'], $form_values['city']);
I also changed datetime to use the unix function time() instead of the SQL function NOW() to be more consistent with core.
Now lets put it in the correct place, it needs to be part of formexample_nameform_submit() (Right now it is outside the scope of any function so it runs when the module is loaded (every page load), so currently none of the values would be what you expect (except datetime).
function formexample_nameform_submit($form_id, $form_values) {
$name = $form_values['number'];
$sql= "INSERT into {petition_ihm} (id, datetime, first_name, last_name, city)VALUES(0, NOW(), '%s', '%s', '%s')";
db_query ($sql, $form_values['number'], $form_values['date_time'], $form_values['first_name'], $form_values['last_name'], $form_values['city']);
drupal_set_message(t('Thanks for Petioning. %name', array('%name' => $name)));
}
Also I suggest losing the closing
?>as it is not required and can cause problems. The function is almost there, the call to drupal_set_message() will not work as expected since $name is set to $form_values['number'] which is not set as part of the form.Thanks
I have been away and have not tried it yet. I am sure it will work. Many thanks for your help.