Can't insert data to external DB

Ol

Olivia 11 Dec, 2015

I was trying to insert data from form to mysql DB. I've read some tutorials and watched the videos, but the examples in those are usually about inserting into Joomla DB.

I added an auto-increment field in my table in mysql. I am not sure if I should make my field in my form to be auto-increment as well. If so, how can I do the 2-way data-binding? I mean, if I wrote id++ in js code, how does js knows id means the id field in the form?

If not, is there something else causing my failure?

Gr

GreyHead 12 Dec, 2015

Hi Olivia,

Are you sure that you need to use an External database - that's not clear from your post.

For the code that ChronoForms uses to insert new records or update existing ones to work correctly the table should have an auto-incrementing primary key - typically the first column named id if cf_id.

I have no idea what JavaScript you are using so can’t say much about that expect that it is a bit odd to be using JavaScript to save data to a database. Possibly you mean PHP?

Bob

Ol

Olivia 20 Dec, 2015

Hi Bob,

Sorry for the late reply. I am evaluating some CMSs. To save in internal or external DB is up to the client. I'm just testing the possibilities.

I've add an auto-increment PK in the table.

I thought PHP code to insert data to DB is already been taken care of, since you have a GUI for DB save. However, if I need to write own code, which would probably be something like this (I'm not very familiar with PHP):
<?php
define("DB_HOST", "localhost");
define("DB_USER", "root");
define("DB_PASS", "password");
define("DB_DBASE", "mysql");
$DBconn =new PDO('mysql:host='.DB_HOST.';dbname='.DB_DBASE, DB_USER, DB_PASS);
$DBconn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$SQLstmt = $DBconn->prepare("INSERT INTO table VALUES (:TableField)");
$SQLstmt->bindParam(':TableField', {FormField});
$SQLstmt->execute();
?>

Your DB Save GUI already solved the DB connection, so how can I use $DBconn?

Thank you.

Gr

GreyHead 20 Dec, 2015

Answer

1 Likes

Hi Olivia,

I'm not clear what the question is any more?

You can use the Joomla! methods to save to, or read from, the database (and to an external one) if, for some reason, the CF action doesn't do what you need. Here's an example:

$db = \JFactory::getDBO();
$query = "
    SELECT `aaa`
        FROM `#__some_table`
        WHERE `xxx` = 'yyy' ;
";
$db->setQuery($query);
$data = $db->loadObjectList();
//$data = $db->loadAssocList();
//$data = $db->loadResult();
. . .
?>

Ol

Olivia 22 Dec, 2015

Hello,

Thank you. I wasn't aware of the existence of Joomla build-in methods to interact with DB. My code is shown below, which is basically from Joomla documentation, if anyone needed.

<?php
$option = array(); //prevent problems 
$option['driver']   = 'mysql';            // Database driver name
$option['host']     = 'localhost';    // Database host name
$option['user']     = 'root';       // User for database authentication
$option['password'] = 'password';   // Password for database authentication
$option['database'] = 'mysql';      // Database name
//$option['prefix']   = 'abc_';             // Database prefix (may be empty)
 
$db = JDatabaseDriver::getInstance( $option );
//$db = JFactory::getDbo();
 
// Create a new query object.
$query = $db->getQuery(true);
 
// Insert columns.
$columns = array('cf_id', 'AnotherTableField');
 
// Insert values.
$values = array($form->data['cf_id'], $db->quote($form->data['AnotherFormField']));
 
// Prepare the insert query.
$query
    ->insert($db->quoteName('table'))
    ->columns($db->quoteName($columns))
    ->values(implode(',', $values));
 
// Set the query using our newly populated query object and execute it.
$db->setQuery($query);
$db->execute();
?>

Forums

Can't insert data to external DB