Form API with Database API

8 replies [Last post]
bill
Offline
Last seen: 10 years 22 weeks ago
Joined: 2011-06-04

Hi Chris,

First I love the entire video series you've produced. The videos are clear, concise and obviously well thought out and produced. I don't think I've come across anything of such great quality for this type of things. It was money very well spent on my part!

I finished the Forms API last week and can now successfully create web forms, complete with default values for each form field. My next step is to be able to pull the default values from a MySql database, instead of setting them as Default Value as a $form attribute. Do you have any code samples that would show how to use Form API in conjunction with the Database API?

Chris Shattuck
Offline
Last seen: 10 weeks 3 days ago
Joined: 2009-08-16
Hello Bill, First of all,

Hello Bill,

First of all, thank you for the kind words, they're much appreciated, and I'm glad that you're getting your money's worth. Second, sorry for the delayed response.

Showing a fusion of the Form API and Database API has been a common request, and one I will be covering before too long. However, knowing what you know now about how to access data from a form, and adding that to what you'll learn from the Database API videos, it's a small step to merge the two. I'd love to put together some practical examples, but all the pieces are there.

I'll try to remember to add to this post when I've put together some video coverage. But in the meantime, feel free to post any specific hurdles you end up facing.

Cheers!
Chris

bill
Offline
Last seen: 10 years 22 weeks ago
Joined: 2011-06-04
I'm about 80% of the way

I'm about 80% of the way there Chris, cobbling methods from your videos with other snippets of code I've found. I'm able to update my database with information submitted via the form and that seems to be working with no problem. I've also gotten a form update to work, in a rudimentary way, but I'm kind of stuck in expanding it to a broader update use case. The code below works to update a single #item in the $form array (in this case #default_value). But I'm not sure where to take it to allow multiple #items to be updated (in this case the the #title and #description elements).

To take this a step further, there are multiple $form elements that I need to update (for brevity's sake I'm only showing an additional 'v01_t_rate'), each of which has a fieldid and other columns of data stored in the database. Is there a more sweeping or elegant method I can use to get all of that data in a single select statement and then update the form elements from there?

Regards,

Bill

function v01_v01fielddata_fetch () {
  $query = db_select('v01fielddata', 'v')
    ->fields('v', array('title_default', 'desc_default', 'value_default'))
    ->condition('v.fieldid', 'v01price');
   
  $result = $query->execute();
  $data = $result->fetchAssoc();
  return $data['value_default'];
  }    

/**
 * v01 - Input Form
 */


function v01_fields ($form, &$form_submit) {
  $form['v01_price'] = array (
    '#title' => t('Vehicle purchase price'),
    '#type' => 'textfield',
    '#default_value' => v01_v01fielddata_fetch(),
    '#field_prefix' => t('$'),
    '#maxlength' => 25,
    '#size' => 20,
    '#description' => t('The price you pay for your vehicle after extras and upgrades'),    
  );
  $form['v01_t_rate'] = array(
    '#title' => t('Sales tax rate'),
    '#type' => 'textfield',
    '#default_value' => variable_get('v01_t_rate', 8),
    '#field_suffix' => t('%'),
    '#maxlength' => 25,
    '#size' => 20,
    '#description' => t('The sales tax rate in your area for new car purchases'),
  );

Chris Shattuck
Offline
Last seen: 10 weeks 3 days ago
Joined: 2009-08-16
I'm trying to wrap my mind

I'm trying to wrap my mind around what you're trying to do here. Am I right that you're pulling out data about a set of fields, rather than content stored in those fields? If so, could you add a conditional to the query to check for various v.fieldid values, so you don't have to do a separate query for each field?

Let me know if I'm on the right track.

Cheers,
Chris

bill
Offline
Last seen: 10 years 22 weeks ago
Joined: 2011-06-04
Okay. Sorry, but I have to be

Okay. Sorry, but I have to be a bit obscure here for confidentiality reasons. I'm basically building a website that will provide syndicated content that will be integrated into other websites. Content licensees will be able to customize and brand the content based on their own terminology and design preferences. As such, they'll have an admin portal we'll they'll be able to make these customizations and those customizations will show up on the syndicated content. So, in the example provided, the customer may decide that they want a different title or description on the individual field. Likewise for editorial content that might surround the form. The SQL database sits in the middle with the Admin portal allowing customizations to be made and the form you're seeing in the code presented where those customizations will be seen by the website visitor.

So yes, I am pulling both content that is stored in the fields and data about those fields. At least what I'm trying to do now is to get to the point where I can do a query for all of the information about a specific field and update all elements at once. The example I've provided only allows me to do it for a specific element or attribute of the field (i.e., default_value).

I'm able to update all of the elements in the database using statements such as the following:

  $v01price = db_update ('v01fielddata')
    ->fields (array(
        'title_default' => $form_state['values']['v01_price_title_d'],
        'desc_default' => $form_state['values']['v01_price_description_d'],
        'value_default' => $form_state['values']['v01_price_value_d'],
        'minvalue_default' => $form_state['values']['v01_price_minvalue_d'],
        'maxvalue_default' => $form_state['values']['v01_price_maxvalue_d'],
        'prefix_default' => $form_state['values']['v01_price_prefix_d'],
        'maxlength_default' => $form_state['values']['v01_price_size_d'],
    ))
    ->condition('fieldid', 'v01price')
    ->execute();

I'm basically looking to figure out how to take this info from the database and customize these same elements for each $form item.

Hope that clarifies things. Thanks for any help you can offer.

SandboxPleeb
Offline
Last seen: 2 years 15 weeks ago
Joined: 2011-07-12
working on similar functions

bill,

first, i am a novice, but i aim to ask good questions.

i supposed that your sites require various permissions for accessibility. in that case, what format(s) do you propose to save anon/ auth updated files? cookies, unmanaged/managed, etc.? have you tried "hijacking" any of the coding from the aggregator module regarding syndication, or a type of hook_table_range function?

perhaps, some concepts from the preprocessing videos that will autoload previous form settings for a unique user.

i think that this is explained in d7 core concepts #8...
also see from the example.module: ...sites/example.com/?q=examples/ajax_example/dependent_dropdown

if you've made additional progress in this area, it would be of much interest and value. hope all is well.

Chris Shattuck
Offline
Last seen: 10 weeks 3 days ago
Joined: 2009-08-16
Great questions and

Great questions and suggestions, thank you for posting these!

Cheers,
Chris

bill
Offline
Last seen: 10 years 22 weeks ago
Joined: 2011-06-04
My final solution

So here's the final code I wrote and got to work to accomplish what I was looking to do. First, I grab an individual record from my database table (v01fielddata), using the primary key for the record (v01price). That function gets called from within the hook_form function and returns the data to $v01price_recall. Individual elements for each $form are then assigned using the $v01price_recall[0]->field method. This populates all data from the v01fielddata when the form loads.

When the form is then saved, hook_form_submit is called, with all form data being passed via $form_state. Each column in the v01fielddata/v01price record is then updated using db_update and the fields method.

I did find the Examples module for both the Form API and Database API to be of great help in figuring this out.

function get_v01price_data () {
  $v01price_recall = db_select('v01fielddata', 'v');
  $v01price_recall
    ->fields('v')
    ->condition('fieldid', 'v01price');
  return $v01price_recall->execute()->fetchAll();
}

function v01_fields ($form, &$form_submit) {
 
  $v01price_recall = get_v01price_data();
   
  $form['v01_price'] = array (
    '#title' => $v01price_recall[0]->title_default,
    '#type' => 'textfield',
    '#default_value' => $v01price_recall[0]->value_default,
    '#field_prefix' => $v01price_recall[0]->prefix_default,
    '#maxlength' => 1056,
    '#size' => $v01price_recall[0]->size_default,
    '#description' => $v01price_recall[0]->desc_default,
  );

function v01_content_form_submit ($form, &$form_state) {
      $v01price_update = db_update ('v01fielddata')
        ->fields (array(
                'title_default' => $form_state['values']['v01price_title_default'],
                'desc_default' => $form_state['values']['v01price_description_default'],
                'value_default' => $form_state['values']['v01price_value_default'],
                'prefix_default' => $form_state['values']['v01price_prefix_default'],
                'size_default' => $form_state['values']['v01price_size_default'],
            ))
        ->condition('fieldid', 'v01price')
        ->execute();
}

Chris Shattuck
Offline
Last seen: 10 weeks 3 days ago
Joined: 2009-08-16
Awesome, thank you for

Awesome, thank you for posting your solution, Bill!

Add to playlist

Add to , or

Add to new playlist:

Add to playlist
This is a member-only feature
But, it's easy to become a member

Add to cart:


Loading...
Contact us

Below are the supported keyboard shortcuts for our video player.

space
Start / stop video
shift
Go to next video
shift
Go to previous video
Increase or decrease player speed by 20%
Skip ahead or go back by 15 seconds
1...9
Jump to a percentage (2 is 20%, for example)
f
Toggle full screen
Videos Pricing More Search
BuildAModule Logo Icon BuildAModule Logo Icon
Become a member!
$29
1 month
$156
6 months
10% off
$276
12 months
20% off
All memberships come with the following:
24 / 7 access to the entire 1800+ video library
An innovative video player with a rolling transcript
Extensive Drupal and cross-platform training
A thorough and thoughtful training style
Progress tracking
Take notes on specific video sections
Intuitive multi-user management
How many users?
user
Go solo with a single user, or give multiple team members access through one account and get discounts by bundling multiple users into a single membership.