Write Data To Database Table

[Zippo]

I'm having trouble figuring out how to connect to and read or write data from or to a database table. I'd like to use form browser to gather the data, if possible, but I'm not having issues getting the data it's figuring how (and where on my site - template or form or...?) to open a connection to the database/table. I'm new to CMSMS but have been making progress until now. I tried the form builder attribute to write data to the [PREFIX]_module_formbuilder table and it did successfully write but it didn't write useful data - only 1, -1, -1, -1, etc..

Here's a sample PHP script I've tried in various places, as well:

$servername = "localhost";
$username = "db_username";
$password = "password";
$dbname = "db_name";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
die("Connection failed: "
. $conn->connect_error);
}

$sqlquery = "INSERT INTO table member_table (f_name, l_name, addr, city, zip, email_addr, phone, hdcp, tee) VALUES
({$first_name}, {$last_name}, {$street_address}, {$city}, {$zipcode}, {$emailaddress}, {$phonenumber}, {$handicap}, {$tee}";

if ($conn->query($sql) === TRUE) {
echo "record inserted successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}

It doesn't throw any errors, but it also doesn't write any data to the table. I don't think it's actually executing anything. Any help with this would have me groveling in gratitude at your feet. Thanks.

[Jo Morg]

It depends on a few things:

I- Context

In which context are you trying to use the DB? From inside a CMSMS module? From inside CMSMS custom module you are developing? From a plugin? From a UDT? From an external file? Why are you using FormBuilder? What is the objective i.e. where do you need to access the data from?

II- From Within CMSMS

Typical use would be something like this:

Code: Select all

$db = \cms_utils::get_db();
$module_name = 'my_module_name'; # if from inside a module you are developing
$table_name = \cms_db_prefix() . '_' . $module_name . '_member_table';
$query = 'INSERT INTO ';
$query .= $table_name;[url][/url]
$query .= ' (f_name, l_name, addr, city, zip, email_addr, phone, hdcp, tee) ';
$query .= '  VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?) ';
$params = [$first_name, $last_name, $street_address, $city, $zipcode, $emailaddress, $phonenumber, $handicap, $tee];
$result_array = $db->GetAll($query, $params);

echo var_dump($result_array);

This assuming the table table already exist.
CMSMS uses a custom library based on the old adodblite which is a subset of adodb.

III- From Outside CMSMS
Your code should work from both inside a CMSMS context or from an external script, provided the credentials are correct, and the db table and columns exist. I didn't look for typos or syntax errors you may have though.

A note of caution: do not write to CMSMS core tables or any modules' tables that you are not the author of. We won't provide any support in that case and you risk messing up the installation and lose data.

HTH

[Zippo]

Thanks, Jo Morg. Context - I have a website I created using CMSMS. On the website I have a content page that includes a Form Builder Form. When that form is submitted by the user, I would like to write that data to a custom table (perhaps in a database separate from the CMSMS database, if possible) - not one of the CMSMS tables.

On another content page I would like to connect to that same custom table and show the data that is in that table.

I am very familiar with ADODB from back in the day with ASP and VBScript but I can't seem to figure out how to use them in the context of CMSMS.

Thanks for the code snippet. Is there some documentation you can point me to for the "\cms_utils" object and its use? For example, the snippet doesn't specify which DB to "get" or what login info it needs to access the DB.

Thanks again for your help.

[DIGI3]

You may want to take a look at how the FormBrowser module, and its associated FormBuilder disposition, handles this - it sounds like its doing a similar task to what you want to accomplish.

[Zippo]

Thanks, DIGI3 - I did check that out but the issue with form builder (as near as I can tell) is that it's stuck to writing to one table. That table structure doesn't fit my needs - plus I'll be needing other data tables for other aspects of the site as well. I need to perform the usual SQL functions - SELECT, INSERT, UPDATE, and DELETE to the database and tables of my choosing - but not any of the CMSMS tables.

[rotezecke]

to connect to another database, I use:

Code: Select all

$cs = new \CMSMS\Database\ConnectionSpec;
$cs->type = 'mysqli';
$cs->host = 'localhost';
$cs->dbname = 'other_database';
$cs->username = 'my_username';
$cs->password = 'my_password';

$db_other = \CMSMS\Database\Connection::initialize( $cs );

$query = "SELECT * FROM yourTable";
$result = $db_other->Execute($query);

to call the connection to CMSMS database, you only need

Code: Select all

$db = \cms_utils::get_db();

[Zippo]

Thanks, rotezecke. I think I can make that work but the issue for me now, is where to place the code and how to call it so that the connection info isn't viewable to "Joe User" who happens to click on the webpage and choose "view source".

Maybe I'm trying to make this more difficult than it needs to be.

[webform]

You can have several different Form Browsers with different layouts and outputs, all pulling data from the same Form Builder Form.
Where shouldn't be any need to send Form Data to a new DB table.

[Zippo]

You can have several different Form Browsers with different layouts and outputs, all pulling data from the same Form Builder Form.
Where shouldn't be any need to send Form Data to a new DB table.

Let's suppose that on the "localhost" I have a database named "My_Database" and in that database I have a table named "my_table". I would like to read write to and read from that database table (or any other tables I might have in that DB). I haven't figured how to do that with Form Builder or any other method in CMSMS. And, it looks like "Form Browser" is no longer in the list of available modules.

[webform]

It shouldn't be necessary with a new database if you want to collect and show data submitted by the user via a Form Builder Form.

Just install Form Browser from the Forge (http://dev.cmsmadesimple.org/projects/formbrowser). It should work just fine even if it's old. I use Form Browser in several solutions without problems.

Connect your Form Builder Form with Form Browser and create a Form Browser layout to display your collected data on your target page.

If you need to display your data different ways on different pages, just create a new Form Browser layout.

I'm curious why you believe you can't use Form Browser to output your collected data from Form Builder?

[Zippo]

Hi webform! Sorry for the late reply. Time gets away from me now and then...

You asked about why I believed I couldn't use data from Form Builder using Form Browser. I didn't say that I couldn't but a couple of things:

1 - Form Browser is no longer listed on the "Additional Modules" section of CMSMS. So, I didn't think it was still available. Thanks for the link, though. I will check it out.

2 - I did not want to write to the current CMS database. I wanted to use a separate, structured database with custom tables. I wanted to be able to create and use my own queries to Select, Insert, Update, and Delete data. The data might be in multiple tables and need to be combined in multiple different ways, depending upon the need. Form Builder (and, from what I know about it), Form Browser don't appear to have those functions available. I'm used to working with SQL and ADODB, so Form Builder and Form Browser are very unfamiliar and seem somewhat limiting. But, I agree I could be missing something so I'll take a look at Form Browser at the link you provided.

Thanks again!