/**
* adds a new column (field) to the databse
*
* @global object $wpdb
* @param array $atts a set of attributrs to define the new columns
* @retun bool success of the operation
*/
private static function _add_db_column($atts)
{
global $wpdb;
$datatype = PDb_FormElement::get_datatype($atts['form_element']);
$sql = 'ALTER TABLE `' . self::$participants_table . '` ADD `' . $atts['name'] . '` ' . $datatype . ' NULL';
return $wpdb->query($sql);
}
/**
* processes the form submission
*
* @global object $wpdb
* @return null
*/
protected function process_submit()
{
global $wpdb;
// process form submission
$action = filter_input(INPUT_POST, 'action', FILTER_SANITIZE_STRING);
switch ($action) {
case 'reorder_fields':
unset($_POST['action'], $_POST['submit-button']);
foreach ($_POST as $key => $value) {
$wpdb->update(Participants_Db::$fields_table, array('order' => filter_var($value, FILTER_VALIDATE_INT)), array('id' => filter_var(str_replace('row_', '', $key), FILTER_VALIDATE_INT)));
}
break;
case 'reorder_groups':
unset($_POST['action'], $_POST['submit-button']);
foreach ($_POST as $key => $value) {
$wpdb->update(Participants_Db::$groups_table, array('order' => filter_var($value, FILTER_VALIDATE_INT)), array('name' => filter_var(str_replace('order_', '', $key), FILTER_SANITIZE_STRING)));
}
break;
case $this->i18n['update fields']:
// dispose of these now unneeded fields
unset($_POST['action'], $_POST['submit-button']);
foreach ($_POST as $name => $row) {
// skip all non-row elements
if (false === strpos($name, 'row_')) {
continue;
}
if ($row['status'] == 'changed') {
$id = filter_var($row['id'], FILTER_VALIDATE_INT);
if (!empty($row['values'])) {
$row['values'] = serialize($this->prep_values_array($row['values']));
}
if (!empty($row['validation']) && !in_array($row['validation'], array('yes', 'no'))) {
$row['validation'] = str_replace('\\\\', '\\', $row['validation']);
}
/*
* modify the datatype if necessary
*
* we prevent the datatype from being changed to a smaller type to protect
* data. If the user really wants to do this, they will have to do it manually
*/
if (isset($row['group']) && $row['group'] != 'internal') {
$sql = "SHOW FIELDS FROM " . Participants_Db::$participants_table . ' WHERE `field` = "%s"';
$field_info = $wpdb->get_results($wpdb->prepare($sql, $row['name']));
$new_type = PDb_FormElement::get_datatype($row['form_element']);
$current_type = current($field_info)->Type;
if ($new_type != $current_type and !($new_type == 'tinytext' and $current_type == 'text')) {
$sql = "ALTER TABLE " . Participants_Db::$participants_table . " MODIFY COLUMN `" . esc_sql($row['name']) . "` " . $new_type;
$result = $wpdb->get_results($sql);
}
}
/*
* enforce the values for a captcha field
*/
if (isset($row['form_element']) && $row['form_element'] === 'captcha') {
$row['validation'] = 'captcha';
foreach (array('display_column', 'admin_column', 'CSV', 'persistent', 'sortable') as $c) {
$row[$c] = 0;
}
$row['readonly'] = 1;
}
foreach (array('title', 'help_text', 'default') as $field) {
if (isset($row[$field])) {
$row[$field] = stripslashes($row[$field]);
}
}
// remove the fields we won't be updating
unset($row['status'], $row['id'], $row['name']);
$wpdb->update(Participants_Db::$fields_table, $row, array('id' => $id));
}
}
break;
case $this->i18n['update groups']:
// dispose of these now unneeded fields
unset($_POST['action'], $_POST['submit-button'], $_POST['group_title'], $_POST['group_order']);
foreach ($_POST as $name => $row) {
foreach (array('title', 'description') as $field) {
if (isset($row[$field])) {
$row[$field] = stripslashes($row[$field]);
}
}
// make sure name is legal
//$row['name'] = $this->make_name( $row['name'] );
$wpdb->update(Participants_Db::$groups_table, $row, array('name' => stripslashes_deep($name)));
}
break;
// add a new blank field
// add a new blank field
case $this->i18n['add field']:
// use the wp function to clear out any irrelevant POST values
$atts = shortcode_atts(array('name' => $this->make_name(filter_input(INPUT_POST, 'title', FILTER_SANITIZE_STRING)), 'title' => htmlspecialchars(stripslashes(filter_input(INPUT_POST, 'title', FILTER_SANITIZE_STRING)), ENT_QUOTES, "UTF-8", false), 'group' => filter_input(INPUT_POST, 'group', FILTER_SANITIZE_STRING), 'order' => filter_input(INPUT_POST, 'order', FILTER_SANITIZE_NUMBER_INT), 'validation' => 'no'), $_POST);
if (empty($atts['name'])) {
break;
// ignore empty field name
}
// if they're trying to use a reserved name, stop them
if (in_array($atts['name'], Participants_Db::$reserved_names)) {
Participants_Db::set_admin_message(sprintf('<h3>%s</h3> %s:<br />%s', __('Cannot add a field with that name', 'participants-database'), __('This name is reserved; please choose another. Reserved names are', 'participants-database'), implode(', ', Participants_Db::$reserved_names)), 'error');
break;
}
// prevent name from beginning with a number
if (preg_match('/^(\\d)/', $atts['name'])) {
Participants_Db::set_admin_message(sprintf('<h3>%s</h3> %s', __('The name cannot begin with a number', 'participants-database'), __('Please choose another.', 'participants-database')), 'error');
break;
}
$result = Participants_Db::add_blank_field($atts);
if (false === $result) {
Participants_Db::set_admin_message($this->parse_db_error($wpdb->last_error, $action), 'error');
}
break;
// add a new blank field
// add a new blank field
case $this->i18n['add group']:
global $wpdb;
$wpdb->hide_errors();
$atts = array('name' => $this->make_name($_POST['group_title']), 'title' => htmlspecialchars(stripslashes($_POST['group_title']), ENT_QUOTES, "UTF-8", false), 'order' => $_POST['group_order']);
$wpdb->insert(Participants_Db::$groups_table, $atts);
if ($wpdb->last_error) {
Participants_Db::set_admin_message($this->parse_db_error($wpdb->last_error, $action), 'error');
}
break;
case 'delete_field':
global $wpdb;
$wpdb->hide_errors();
$result = $wpdb->query($wpdb->prepare('
DELETE FROM ' . Participants_Db::$fields_table . '
WHERE id = "%s"', $_POST['delete']));
break;
case 'delete_group':
global $wpdb;
//$wpdb->hide_errors();
$group_count = $wpdb->get_var($wpdb->prepare('SELECT COUNT(*) FROM ' . Participants_Db::$fields_table . ' WHERE `group` = "%s"', $_POST['delete']));
if ($group_count == 0) {
$result = $wpdb->query($wpdb->prepare('DELETE FROM ' . Participants_Db::$groups_table . ' WHERE `name` = "%s"', $_POST['delete']));
}
break;
default:
$action = '';
}
if (!empty($action) && empty(Participants_Db::$admin_message)) {
Participants_Db::set_admin_message(__('Your information has been updated', 'participants-database'), 'updated');
}
}
if (!empty($row['values'])) {
$row['values'] = serialize(PDb_prep_values_array($row['values']));
}
if (!empty($row['validation']) && !in_array($row['validation'], array('yes', 'no'))) {
$row['validation'] = str_replace('\\\\', '\\', $row['validation']);
}
/*
* modify the datatype if necessary
*
* we prevent the datatype from being changed to a smaller type to protect
* data. If the user really wants to do this, they will have to do it manually
*/
if (isset($row['group']) && $row['group'] != 'internal') {
$sql = "SHOW FIELDS FROM " . Participants_Db::$participants_table . " WHERE field = '" . $row['name'] . "'";
$field_info = $wpdb->get_results($sql);
$new_type = PDb_FormElement::get_datatype($row['form_element']);
$current_type = current($field_info)->Type;
if ($new_type != $current_type and !($new_type == 'tinytext' and $current_type == 'text')) {
$sql = "ALTER TABLE " . Participants_Db::$participants_table . " MODIFY COLUMN `" . $row['name'] . "` " . $new_type;
$result = $wpdb->get_results($sql);
}
}
/*
* enforce the values for a captcha field
*/
if ($row['form_element'] == 'captcha') {
$row['validation'] = 'captcha';
foreach (array('display_column', 'admin_column', 'CSV', 'persistent', 'sortable') as $c) {
$row[$c] = 0;
}
$row['readonly'] = 1;
/**
* installs the plugin database tables and default fields
*/
private function _fresh_install()
{
global $wpdb;
// define the arrays for loading the initial db records
$this->_define_init_arrays();
// create the field values table
$sql = 'CREATE TABLE ' . Participants_Db::$fields_table . ' (
`id` INT(3) NOT NULL AUTO_INCREMENT,
`order` INT(3) NOT NULL DEFAULT 0,
`name` VARCHAR(64) NOT NULL,
`title` TINYTEXT NOT NULL,
`default` TINYTEXT NULL,
`group` VARCHAR(64) NOT NULL,
`help_text` TEXT NULL,
`form_element` TINYTEXT NULL,
`values` LONGTEXT NULL,
`validation` TINYTEXT NULL,
`display_column` INT(3) DEFAULT 0,
`admin_column` INT(3) DEFAULT 0,
`sortable` BOOLEAN DEFAULT 0,
`CSV` BOOLEAN DEFAULT 0,
`persistent` BOOLEAN DEFAULT 0,
`signup` BOOLEAN DEFAULT 0,
`readonly` BOOLEAN DEFAULT 0,
UNIQUE KEY ( `name` ),
INDEX ( `order` ),
INDEX ( `group` ),
PRIMARY KEY ( `id` )
)
DEFAULT CHARACTER SET utf8
COLLATE utf8_unicode_ci
AUTO_INCREMENT = 0
';
$wpdb->query($sql);
// create the groups table
$sql = 'CREATE TABLE ' . Participants_Db::$groups_table . ' (
`id` INT(3) NOT NULL AUTO_INCREMENT,
`order` INT(3) NOT NULL DEFAULT 0,
`display` BOOLEAN DEFAULT 1,
`admin` BOOLEAN NOT NULL DEFAULT 0,
`title` TINYTEXT NOT NULL,
`name` VARCHAR(64) NOT NULL,
`description` TEXT NULL,
UNIQUE KEY ( `name` ),
PRIMARY KEY ( `id` )
)
DEFAULT CHARACTER SET utf8
COLLATE utf8_unicode_ci
AUTO_INCREMENT = 1
';
$wpdb->query($sql);
// create the main data table
$sql = 'CREATE TABLE ' . Participants_Db::$participants_table . ' (
`id` int(6) NOT NULL AUTO_INCREMENT,
`private_id` VARCHAR(9) NULL,
';
foreach (array_keys(self::$field_groups) as $group) {
// these are not added to the sql in the loop
if ($group == 'internal') {
continue;
}
foreach (self::${$group . '_fields'} as $name => &$defaults) {
if (!isset($defaults['form_element'])) {
$defaults['form_element'] = 'text-line';
}
$datatype = PDb_FormElement::get_datatype($defaults['form_element']);
$sql .= '`' . $name . '` ' . $datatype . ' NULL, ';
}
}
$sql .= '`date_recorded` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`date_updated` TIMESTAMP NULL DEFAULT NULL,
`last_accessed` TIMESTAMP NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
DEFAULT CHARACTER SET utf8
COLLATE utf8_unicode_ci
;';
$wpdb->query($sql);
// save the db version
add_option(Participants_Db::$db_version_option);
update_option(Participants_Db::$db_version_option, Participants_Db::$db_version);
// now load the default values into the database
$i = 0;
unset($defaults);
foreach (array_keys(self::$field_groups) as $group) {
foreach (self::${$group . '_fields'} as $name => $defaults) {
$defaults['name'] = $name;
$defaults['group'] = $group;
$defaults['order'] = $i;
$defaults['validation'] = isset($defaults['validation']) ? $defaults['validation'] : 'no';
if (isset($defaults['values']) && is_array($defaults['values'])) {
$defaults['values'] = serialize($defaults['values']);
}
$wpdb->insert(Participants_Db::$fields_table, $defaults);
$i++;
}
}
// put in the default groups
$i = 1;
$defaults = array();
foreach (self::$field_groups as $group => $title) {
$defaults['name'] = $group;
$defaults['title'] = $title;
$defaults['display'] = in_array($group, array('internal', 'admin', 'source')) ? 0 : 1;
$defaults['order'] = $i;
$wpdb->insert(Participants_Db::$groups_table, $defaults);
$i++;
}
}
