Forums

Single Page form insert into database

Permalink 1 user found helpful
Hi all
I'm pretty new to coding and am trying my hardest to get to grips with C5 and all of it's possibilities.

What i'm trying to do is create a single page with a form on it that inputs data into a table in the database i have created.
I have the form looking the way i want using the form helper and code like
<?php  defined('C5_EXECUTE') or die("Access Denied."); ?>
<form method="post" enctype="multipart/form-data">
<?php
$form = Loader::helper('form');
print $form->label('v_id','Vehicle ID');
print $form->text('v_id',"");
//etc etc


What i am sruggling with is what the controller should include and also if i have the neccessery info in the single page.

this is how i have started the controller file
<?php
defined('C5_EXECUTE') or die(_("Access Denied."));
class VehicleCheckController extends Controller {
    public function SaveFormData($post){
$db= Loader::db();
$query="INSERT INTO btcheckreport (v_id, u_id, date, lat, long, wheels, wheels_desc, wheel_img, body, body_desc, body_img, interior, interior_desc, interior_img, warning_lights, wl_desc, mileage, notes) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
$vals = array(
$this->post('v_id'),
$this->post('u_id'),
etc etc
.
.
.
$db->Execute($query, $vals);
}
Viewing 15 lines of 16 lines. View entire code block.


What i want it to do is simply insert a new record into the table and redirect to a different page if successful but i am completely out of my depth to be honest. I've worked everything i have so far together from various posts on the site. I just haven't found any documentation that just lays it out in a way i understand.

any help would be greatly appreciated.

Adam
 
JohntheFish replied on at Permalink Reply
JohntheFish
Its probably not a good idea to write directly to your own block table. Try and separate the meta data about the block (the block table that c5 builds) from the data from your users. Put the data from your form into a table that you have created specifically for that purpose.

That is both cleaner architecturally, less complicated to implement, and safer in that any mistakes you make will not break your site.
fauners replied on at Permalink Reply
Hi John

thanks for the quick reply. The table i created isn't actually part of a block, i know i called it btcheckreport, i probably shouldn't have, i just did it at the start as it made sense to me and kept everything i was doing together. It's not part of a block at all.
The table will only be updated from the single page form.
I will actually be having a couple of other forms and tables that i will name properly. this is just testing phase at the minute just to try and get it working.
I just don't know the format the two files should be in for a simple form table insert.

Adam
jordanlev replied on at Permalink Best Answer Reply
jordanlev
In the single_page file (the front-end view), do this:
<form method="post" action="<?php echo $this->action('saveFormData'); ?>">
  <?php
  $form = Loader::helper('form');
  echo $form->label('v_id', 'Vehicle ID');
  echo $form->text('v_id');
  //etc. etc.
  ?>
</form>

The important difference here is that I've added the "action" to the form -- you need this so C5 knows which controller function to call upon submission.
And you only need the "enctype" attribute on the form if you have file uploads (so if you actually do have file uploads in the form, add it back in otherwise it won't work).


Then in the controller, do this:
public function saveFormData($post) {
  //Save the data to the database
  $db = Loader::db();
  $data = array(
    'v_id' => $this->post('v_id'),
    'u_id' => $this->post('u_id'),
    //etc...
  );
  $db->AutoExecute('btcheckreport', $data, 'INSERT');
  //Redirect the user
  $this->redirect('/path/to/page');
}


The important part here is that the function name matches the "action" we added to the form on the front-end. I've also shown an example of ADODB's awesome "AutoExecute" function, which will save you trouble if you ever add/remove fields from the table (because you won't need to mess around with question marks and field names in the SQL string). But that's not necessary... just makes for cleaner code IMHO.

This will work as-is, but is lacking any kind of data validation. To do that, you'd want to check the values in $post first, and if they're ok then proceed with the database insertion and the redirect, but if they're not then construct a message explaining the problem(s) and pass that back to the front-end via $this->set('error', $your_error_message) ... and then on the frontend have something like:
if (!empty($error)) {
  echo '<div class="error">Please correct the following error: ' . $error . '</div>';
}

There are more robust ways to handle validation, but if you're just starting out coding, this should be enough for now :)
fauners replied on at Permalink Reply
Hi Jordan

Sorry for the late reply, but hanks so much for this. I'm going to go test this now and try get it working.
I will have file uploads so i will include the enctype part.
i will also look at the form validation as well, but most of it will be check boxes and stuff, but would like to get more knowledgable on it anyway.
I'll let you know how it goes and any issues i have just in case their are other people out their looking to do the same thing and are as lost as me!
fauners replied on at Permalink Reply 1 Attachment
Hi Jordan

i have done everything but i appear to be getting some errors, maybe you might be able to help. First i know the sample data i am inserting is fine as i have used phpmyadmin to insert the same values and it inserts perfectly, but when i use the form i get the following errors:
on the top of the page is
Warning: Missing argument 1 for VehicleCheckController::SaveFormData() in C:\xampp\htdocs\fleet\controllers\vehicle_check.php on line 4

and then below is

mysql error: [1064: 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 'LONG, WHEELS, WHEELS_DESC, WHEEL_IMG, BODY, BODY_DESC, BODY_IMG, INTERIOR, INTER' at line 1] in EXECUTE("INSERT INTO btcheckreport ( CR_ID, V_ID, U_ID, DATE, LAT, LONG, WHEELS, WHEELS_DESC, WHEEL_IMG, BODY, BODY_DESC, BODY_IMG, INTERIOR, INTERIOR_DESC, INTERIOR_IMG, WARNING_LIGHTS, WL_DESC, MILEAGE, NOTES ) VALUES ( 0, 4, 1, '2013-04-08 13:40:22', 12.234562, 12.123456, 0, 'edfvdfv', null, 1, 'evdfvdf', null, 2, 'edfvdfvfdv', null, 0, 'efvfvdfvdfv', 1234, 'dfvevfev' )")


I've attached my single page and controller files also.

adam
jordanlev replied on at Permalink Reply
jordanlev
You're missing a closing </form> tag in your view. Also, remove the $post argument from your controller action (not sure why I didn't notice that the first time). That will fix your first error.
I'm not sure about the MySQL error... I can't see why it would be doing that. Although it's weird that you've set lowercase field names in your controller code, but the MySQL error is showing uppercase field names. Makes me think something isn't hooked up right perhaps?
fauners replied on at Permalink Reply
hi Jordan
perfect thanks for that.
I fixed the form tag and the post part. But something really weird is happening.
I reverted back to the first type of code i had with all the ????
and i got it working fine the way i wanted while i was waiting to see if you had a solution.
In the meantime i started another form on a single page for a different table. I used your code style with the Autoexecute etc and it works perfectly, really neat and everything.
So i saw your reply and went back and tweaked everything on the original form and no joy.
I've rewritten the code 4 times now just to make sure i haven't done anything silly like spelling mistakes. But it will not work.
It works fine on one form, but not on the other, same format just different table and number of entries.
I have double and triple checked and it all looks good from my point of view, but an error keeps coming up like before. Mysql syntax blah blah.
Is there a limit on number of entries using the autoexecute code or anything? the form that works only has 9 entries but the one that doesn't work has 19.
What is really confusing me is that I have tried it with the same exact data going through the form. Just thought i'd let you know, maybe it's just a once off.
mkly replied on at Permalink Reply
mkly
@jordanlev,
Like that AutoExecute() $this->post() idiom right there.
jordanlev replied on at Permalink Reply
jordanlev
Thanks -- you're the one that taught it to me!
http://www.concrete5.org/community/forums/customizing_c5/orm-sql-he... ?
mkly replied on at Permalink Reply
mkly
Heh, yeah i know ;), just like the particular style on the $this->post()
jshannon replied on at Permalink Reply
jshannon
The creation of $data looks too manual for me. You can define a set of "allowed" properties (e.g., v_id, u_id, etc) as an array and then do an array_intersect_key() to get only those array items that are in both.

Actually, I recently got tired of creating and maintaining a bunch of db columns that I never need to filter/join and went one step further:

class NoSql {
   protected $fields = array();
   protected $fieldsAsKeys = array();
   protected $data = array();
   public function __construct($validFields, $data = null) {
      if (is_array($validFields)) {
         $this->fields = $validFields;
      } else {
         $this->fields = array_filter(explode(',', $validFields));
      }
      $this->fieldsAsKeys = array_fill_keys($this->fields, '');
      $this->setData($data);
   }
   public function setData($data) {
      if (is_array($data)) {
Viewing 15 lines of 30 lines. View entire code block.


So, assuming your db table is ID (auto-created) and "values", you can do:
$nosql = new NoSql($allowedFields, $_POST);
$db->AutoExecute('Table', array('values' => $nosql), 'INSERT');
fauners replied on at Permalink Reply
Wow,That looks like it could be really useful alright.
It's kind of hurting my brain trying to get my head around it all though. I think i'll stick to the simpler stuff for now! At least unitl i get better at thisstuff.

I have the forms working well now. The only thing i am trying to do now is create a form select drop down from a list of vehicles in another table.
So the drop down would be the list of vehicles the current user has privilages to.
JohntheFish replied on at Permalink Reply
JohntheFish
This is a really neat trick.

I did a similar thing to create a generic model that works out fields automatically and I now just inherit from it when I need a new model, but my code is nowhere as clean as yours. I am bookmarking this trick for next time I update my base class code.

You should copy this into a howto and get double karma.
fauners replied on at Permalink Reply
Yes I agree with John
I've been trawling through the forums bookmarking stuff as i go that might be useful one day!
It would be great to get everything into one spot so others can find it. I'm sure this could save someone a lot of heartache one day