close ad
 
Important WebAssist Announcement
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

Data Assist Recordset MySQL Error when try to expand

Thread began 1/15/2012 6:15 pm by toni272059 | Last modified 1/16/2012 6:57 pm by Ray Borduin | 8213 views | 7 replies |

toni272059

Data Assist Recordset MySQL Error when try to expand

I have been working thru the DataAssist Getting Started Guide. Everything was going smoothly until I reached Customizing your Pages, Details.php.

When I try to Expand the Recordset in the Bindings Panel I receive the following 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 'group, `item-id`, item, `item-photo`, item-desc, `item-size`, item-price FROM `b' at line 1

I have gone thru the DataAssist Wizard twice trying different settings. The MySQL table items are all set to VARCHAR or TEXT or MEDIUMBLOB (for photos) . Database was created on my local computer using MAMP

If I try to open a file on my local computer in the browser the following error loads
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 '-products-table ORDER BY item LIMIT 0, 1000' at line 1

Follows content of exported .sql table. What am I missing...?

-- phpMyAdmin SQL Dump
-- version 2.11.7.1
-- www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jan 15, 2012 at 09:12 PM
-- Server version: 5.0.41
-- PHP Version: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `boomerang-products`
--

-- --------------------------------------------------------

--
-- Table structure for table `boomerang-products-table`
--

CREATE TABLE `boomerang-products-table` (
`maincategory` varchar(55) NOT NULL,
`subcategory` varchar(55) NOT NULL,
`group` varchar(55) NOT NULL,
`item-id` varchar(15) NOT NULL,
`item` varchar(55) NOT NULL,
`item-photo` mediumblob NOT NULL,
`item-desc` text NOT NULL,
`item-size` varchar(55) default NULL,
`item-price` varchar(11) NOT NULL,
PRIMARY KEY (`item-id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `boomerang-products-table`
--

Sign in to reply to this post

Ray BorduinWebAssist

It may not like the dashes in your table name. A dash will look like you are trying to subtract in your sql statement. Try using underscores instead of dashes in your table names.

Sign in to reply to this post
Did this help? Tips are appreciated...

toni272059

I have tried changing the database table field names to use underscores, I have deleted files and started again with Data Assist. Everytime I have the same problem with the Recordset error. The latest error message is

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 'group, product_number, item_name, item_photo, item_desc, item_size, item_price F' at line 1

I have tried changing field names, field types.

Follows the latest sql file content and also the php at head of Details page

-- phpMyAdmin SQL Dump
-- version 2.11.7.1
-- www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jan 16, 2012 at 03:25 PM
-- Server version: 5.0.41
-- PHP Version: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `boomerang-products`
--

-- --------------------------------------------------------

--
-- Table structure for table `boomerang-products-table`
--

CREATE TABLE `boomerang-products-table` (
`maincategory` varchar(55) NOT NULL,
`subcategory` varchar(55) NOT NULL,
`group` varchar(55) NOT NULL,
`item_id` int(10) unsigned NOT NULL auto_increment,
`product_number` varchar(25) NOT NULL,
`item_name` varchar(55) NOT NULL,
`item_photo` mediumblob NOT NULL,
`item_desc` text NOT NULL,
`item_size` varchar(55) default NULL,
`item_price` decimal(5,2) NOT NULL,
PRIMARY KEY (`item_id`),
UNIQUE KEY `product_number` (`product_number`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `boomerang-products-table`
--


<?php require_once('../Connections/Boomerang.php'); ?>
<?php require_once("../WA_DataAssist/WA_AppBuilder_PHP.php"); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
}

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}
?><?php
if (!session_id()) session_start();
?>
<?php
$Paramitem_id_WADAboomerangproductstable = "-1";
if (isset($_GET['item_id'])) {
$Paramitem_id_WADAboomerangproductstable = (get_magic_quotes_gpc()) ? $_GET['item_id'] : addslashes($_GET['item_id']);
}
$ParamSessionitem_id_WADAboomerangproductstable = "-1";
if (isset($_SESSION['WADA_Insert_boomerangproductstable'])) {
$ParamSessionitem_id_WADAboomerangproductstable = (get_magic_quotes_gpc()) ? $_SESSION['WADA_Insert_boomerangproductstable'] : addslashes($_SESSION['WADA_Insert_boomerangproductstable']);
}
$Paramitem_id2_WADAboomerangproductstable = "-1";
if (isset($_GET['item_id'])) {
$Paramitem_id2_WADAboomerangproductstable = (get_magic_quotes_gpc()) ? $_GET['item_id'] : addslashes($_GET['item_id']);
}
mysql_select_db($database_Boomerang, $Boomerang);
$query_WADAboomerangproductstable = sprintf("SELECT item_id, subcategory, group, product_number, item_name, item_photo, item_desc, item_size, item_price FROM `boomerang-products-table` WHERE item_id = %s OR ( -1= %s AND item_id= %s)", GetSQLValueString($Paramitem_id_WADAboomerangproductstable, "int"),GetSQLValueString($Paramitem_id2_WADAboomerangproductstable, "int"),GetSQLValueString($ParamSessionitem_id_WADAboomerangproductstable, "int"));
$WADAboomerangproductstable = mysql_query($query_WADAboomerangproductstable, $Boomerang) or die(mysql_error());
$row_WADAboomerangproductstable = mysql_fetch_assoc($WADAboomerangproductstable);
$totalRows_WADAboomerangproductstable = mysql_num_rows($WADAboomerangproductstable);?>
<?php
// WA Application Builder Delete
if (isset($_POST["Delete_x"])) // Trigger
{
$WA_connection = $Boomerang;
$WA_table = "boomerang-products-table";
$WA_redirectURL = "boomerang_Results.php";
if (function_exists("rel2abs")) $WA_redirectURL = $WA_redirectURL?rel2abs($WA_redirectURL,dirname(__FILE__)):"";
$WA_keepQueryString = false;
$WA_fieldNamesStr = "item_id";
$WA_columnTypesStr = "none,none,NULL";
$WA_fieldValuesStr = "".((isset($_POST["WADADeleteRecordID"]))?$_POST["WADADeleteRecordID"]:"") ."";
$WA_comparisonStr = "=";
$WA_fieldNames = explode("|", $WA_fieldNamesStr);
$WA_fieldValues = explode("|", $WA_fieldValuesStr);
$WA_columns = explode("|", $WA_columnTypesStr);
$WA_comparisions = explode("|", $WA_comparisonStr);
$WA_connectionDB = $database_Boomerang;
mysql_select_db($WA_connectionDB, $WA_connection);
if (!session_id()) session_start();
$deleteParamsObj = WA_AB_generateWhereClause($WA_fieldNames, $WA_columns, $WA_fieldValues, $WA_comparisions);
$WA_Sql = "DELETE FROM `" . $WA_table . "` WHERE " . $deleteParamsObj->sqlWhereClause;
$MM_editCmd = mysql_query($WA_Sql, $WA_connection) or die(mysql_error());
if ($WA_redirectURL != "") {
if ($WA_keepQueryString && $WA_redirectURL != "" && isset($_SERVER["QUERY_STRING"]) && $_SERVER["QUERY_STRING"] !== "" && sizeof($_POST) > 0) {
$WA_redirectURL .= ((strpos($WA_redirectURL, '?') === false)?"?":"&").$_SERVER["QUERY_STRING"];
}
header("Location: ".$WA_redirectURL);
}
}
?>

Sign in to reply to this post

Ray BorduinWebAssist

"group" is a reserved word in mysql. You need to avoid using any of these reserved words as column names:

reserved-words.html

Sign in to reply to this post
Did this help? Tips are appreciated...

toni272059

OK, I changed the field names (plus some other changes noted below) and tried to restart DataAssist Wizard. Now it does not see the Key Column/Primary Key, gives me a MM_ERROR: An unidentified error has occured.

I checked some other forum posts for MM_ERROR but do not see what could be wrong with my database table.

I have tried renaming the database, the table. Emptying cache. Deleting the &#65279;WinFileCache.dat file. Uninstalling and reinstalling Data Assist. Creating a new dynamic site. Creating new .dwt and .php files.

Using phpMyAdmin I inserted two entries into the table because I wanted to create a Menu option in the Insert page for the main category to be either Standard Products or Custom Products

I also changed some of the fields so they are not Required because the Custom Products do not have content for all Fields

ZIP attached with the latest .sql table

Thanks

Attached Files
boomerang_products_table_2.sql.zip
Sign in to reply to this post

Ray BorduinWebAssist

The unidentified error has occurred message usually means that DW can't communicate with your database server. This is usually because the url setting in your site definition is wrong.

Are you able to edit and test your Connection without errors? Start by verifying that you can test your database connection by opening the define connection dialog and clicking the test button. If you get the same error, that means your site definition testing server doesn't have the correct url and/or default directory specified.

Sign in to reply to this post
Did this help? Tips are appreciated...

toni272059

Yes, I can connect to the database. See attached screen grab showing Dreamweaver, Dynamic MySQL Connection and Data Assist windows.

The Dreamweaver Database window shows the Table and all the fields. One of which is identified as a primary key.

Sign in to reply to this post

Ray BorduinWebAssist

I've opened a support incident so that we can have someone call and debug this with you directly. Please update the incident with a phone number and good time to reach you and we'll help figure out exactly what is going on.

Sign in to reply to this post
Did this help? Tips are appreciated...

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...