hi,

i have webforms install and i am trying to use it with mysql views,
my problme is: the view is created but the 'submitted' field type is date and the module does not recognize it, when trying to save schema of the view i get the following error:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 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 'NULL DEFAULT NULL' at line 1: ALTER TABLE {webform_views_testemonial} CHANGE `submitted` `submitted` VARCHAR NULL DEFAULT NULL; Array ( ) in db_change_field() (line 3017 of /home2/sagigree/public_html/includes/database/database.inc).

i tried to change the field type to varchar but unsuccessfuly,
need help

thanks

Sagi Green
VP Biz Dev
Crazy Designs
info@crazydesigns.biz
www.crazydesigns.biz

Comments

sagigreen’s picture

it was fix by mnipulating the view (see bold):

USE sagigree_CrazyDesigns;
DROP VIEW sagigree_CrazyDesigns.webform_views_testemonial;

CREATE
SQL SECURITY INVOKER
VIEW sagigree_CrazyDesigns.webform_views_testemonial
AS
SELECT
`parent`.`sid` AS `sid`,
`s`.`uid` AS `uid`,
(SELECT
GROUP_CONCAT(`child`.`data` SEPARATOR ',')
FROM `webform_submitted_data` `child`
WHERE ((`child`.`sid` = `parent`.`sid`)
AND (`child`.`cid` = 7))) AS `full_name`,
(SELECT
GROUP_CONCAT(`child`.`data` SEPARATOR ',')
FROM `webform_submitted_data` `child`
WHERE ((`child`.`sid` = `parent`.`sid`)
AND (`child`.`cid` = 8))) AS `email_address`,
(SELECT
GROUP_CONCAT(`child`.`data` SEPARATOR ',')
FROM `webform_submitted_data` `child`
WHERE ((`child`.`sid` = `parent`.`sid`)
AND (`child`.`cid` = 9))) AS `confirm_email_address`,
(SELECT
GROUP_CONCAT(`child`.`data` SEPARATOR ',')
FROM `webform_submitted_data` `child`
WHERE ((`child`.`sid` = `parent`.`sid`)
AND (`child`.`cid` = 10))) AS `product`,
(SELECT
GROUP_CONCAT(`child`.`data` SEPARATOR ',')
FROM `webform_submitted_data` `child`
WHERE ((`child`.`sid` = `parent`.`sid`)
AND (`child`.`cid` = 11))) AS `testemonial`,
CAST(DATE_FORMAT(FROM_UNIXTIME(`s`.`submitted`), '%Y-%m-%d %h:%m:%s') AS CHAR(19)) AS `submitted`,
-- FROM_UNIXTIME(`s`.`submitted`) AS `submitted1`,
`s`.`remote_addr` AS `remote_addr`
FROM (`webform_submitted_data` `parent`
JOIN `webform_submissions` `s`
ON ((`s`.`sid` = `parent`.`sid`)))
WHERE (`parent`.`nid` = 530)
GROUP BY `parent`.`sid`
ORDER BY `parent`.`sid` DESC;