Forums

Inserting into databae

Permalink 1 user found helpful
Hi All,

I am really stuck, I have created a new single page with a form. When i click save the single page controller loads the add function. I am trying to add the data into a database table but keep getting errors.

Any helkp would be appreciated

public function add() {
      $u = new User();
      if ($u->isRegistered()) {
         $ui = UserInfo::getByID($u->getUserID());
         $user_id = $ui->getUserID();
         $this->set('userid', $user_id); 
      }
      if ($this->isPost()) {
         $db = Loader::db();
         $sql = "INSERT INTO btCpd (bID,date_added,user_id,title,hours,description,date_conducted) VALUES '','',$user_id,$this->post('title'),$this->post('hours'),$this->post('description'),Loader::helper('form/date_time')->translate('date_conducted' ";
         $db->Execute($sql);
      }
      $this->redirect('/secure_members/cpd', 'added_successfully');
   }
 
ScottSandbakken replied on at Permalink Reply
ScottSandbakken
The add() function is already used by the controller. Rename your add() function to addNew() and alter your form action to match.
obaudains replied on at Permalink Reply
Hi NetJunky,

Yes I have done this.

I am getting an SQL error.

Is my syntax correct in the below..?
There is no clear tutorial on inserting data into the db on c5 im not even sure if this is the right syntax for this..?

$db = Loader::db();
         $sql = "INSERT INTO btCpd (bID,date_added,user_id,title,hours,description,date_conducted) VALUES '','',$user_id,$this->post('title'),$this->post('hours'),$this->post('description'),Loader::helper('form/date_time')->translate('date_conducted' ";
         $db->Execute($sql);


Thanks
ScottSandbakken replied on at Permalink Reply
ScottSandbakken
should be
$db = Loader::db();
         $sql = "INSERT INTO btCpd (bID,date_added,user_id,title,hours,description,date_conducted) VALUES '','',?,?,?,?,?";
       $args = array(
         $user_id,
         $this->post('title'),
         $this->post('hours'),
         $this->post('description'),
         Loader::helper('form/date_time')->translate('date_conducted')
       );
         $db->Execute($sql,$args);
ScottSandbakken replied on at Permalink Reply
ScottSandbakken
Actually, you need to leave the bID field off. That is only used for saving blocks and should not be used on a single page.
<?
$db = Loader::db();
         $sql = "INSERT INTO btCpd (date_added,user_id,title,hours,description,date_conducted) VALUES '','',?,?,?,?,?";
       $args = array(
         $user_id,
         $this->post('title'),
         $this->post('hours'),
         $this->post('description'),
         Loader::helper('form/date_time')->translate('date_conducted')
       );
         $db->Execute($sql,$args);


You also need to verify that the date_added field can accept a value of ''.
obaudains replied on at Permalink Reply
Thanks for the response:

I have updated but still appear to be getting a mysql error:

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 ''1','5','a','a','a','2013-02-19 17:10:00'' at line 1] in EXECUTE("INSERT INTO btCpd (date_added,user_id,title,hours,description,date_conducted) VALUES '1','5','a','a','a','2013-02-19 17:10:00'")


The first entry in the table is date_added which should be a date/timestamp. In the values i have left this blank using ''. Can you see where it may be throwing the error..?

Thank in advance.
ScottSandbakken replied on at Permalink Reply
ScottSandbakken
You need to insert a date into the date_added field and a numeric value into the hours field (unless this is a char or varchar field).
$db = Loader::db();
         $sql = "INSERT INTO btCpd (date_added,user_id,title,hours,description,date_conducted) VALUES ?,?,?,?,?";
       $dteAdded = new DateTime;
       $args = array(
         $DateTime->format('Y-n-j'),
         $user_id,
         $this->post('title'),
         (float)$this->post('hours'),
         $this->post('description'),
         Loader::helper('form/date_time')->translate('date_conducted')
       );
         $db->Execute($sql,$args);
obaudains replied on at Permalink Reply
Ahh right.

Unfortunately it errors and states: Fatal error: Call to a member function format() on a non-object

this is on the line where we have 
$DateTime->format('Y-n-j'),
obaudains replied on at Permalink Reply
I have got around this... there was a typo on the daste variable. But is is still throuwing a mysql error which i cant see ...

$db = Loader::db();
            $sql = "INSERT INTO btCpd (date_added,user_id,title,hours,description,date_conducted) VALUES ?,?,?,?,?,?";
             $dateAdded = new DateTime;
             $args = array(
          $dateAdded->format('Y-n-j'),
          $user_id,
          $this->post('title'),
          (float)$this->post('hours'),
          $this->post('description'),
          Loader::helper('form/date_time')->translate('date_conducted')
         );
         $db->Execute($sql,$args);
      }
ScottSandbakken replied on at Permalink Reply
ScottSandbakken
Add this just above the $db->Execute line.
echo vsprintf(str_replace('?','%s',$sql),$args);

And paste the output here.

Also, list btCpd table's columns and column types. List all columns, not just the ones you are updating.

For instance:
date_added = DateTime
user_id = Integer
title = varchar/60
hours = Float
description = Text
date_conducted = DateTime
obaudains replied on at Permalink Reply
Ok Thanks, Ive got it working now.

Thanks a lot for your help