error on Update Query

Dave · Feb 6, 2002

Looks like I'm having a similar problem as wajdy reported! No problem with the script adding (ID initializes and supplies value) and deleting records, received error when updating records....huh

Sure it's me boneheading the install. Here's my example:
http://www.dw-graphics.com/dbtest/
It'll take you to the directory, clicking on program files will open the index.php scripting!

MYSQL dbase has 3 Fields:
ID (Primary, Keyed Field)
Uname (CHAR Field)
real_name (CHAR Field)

Details:
Version 1.5b
System Linux ns2.he.net 2.2.12 #1 SMP Fri Oct 1 00:58:41 PDT 1999 i686
Build Date Oct 27 2000
apache=//home/mleber/src.he.net/apache-1.3.6.work/apache_1.3.6' '--enable-track-vars' '--enable-magic-quotes' '--with-mysql' '--with-gd' '--with-ttf' '--enable-ftp' '--with-zlib' '--with-db'
Server API Apache
PHP 4.0

Thanks for any help, sure it's a minor problem I created!

Thx,
D-

Eugenio · Feb 7, 2002

Dave wrote:
>
> Looks like I'm having a similar problem as wajdy reported!
> No problem with the script adding (ID initializes and
> supplies value) and deleting records, received error when
> updating records....huh

I can't guess the problem just by viewing the site, you could post here the dump of your db so that I can test it on my system.

(Latest version of DaDaBIK when this message was posted: 1.5b)

Dave · Feb 7, 2002

Jumped on DBase this morning...changed Primary from Int to CHAR, no effect... Here's the Structure and data dump:

# Table structure for table usr
#

CREATE TABLE usr (
ID char(50) NOT NULL auto_increment,
Uname char(50) NOT NULL,
real_name char(50) NOT NULL,
PRIMARY KEY (ID, ID)
);

#
# Dumping data for table usr
#

INSERT INTO usr VALUES ('1', 'TEST', 'TEST1');

Any thoughts?

Eugenio · Feb 8, 2002

Dave wrote:
>
> Jumped on DBase this morning...changed Primary from Int to
> CHAR, no effect... Here's the Structure and data dump:
>
> # Table structure for table usr
> #
>
> CREATE TABLE usr (
> ID char(50) NOT NULL auto_increment,

How a char(50) can be auto_increment?
I've tried with ID as int and works fine, maybe you have made some mistakes in config.php

(Latest version of DaDaBIK when this message was posted: 1.5b)

Dave · Feb 8, 2002

Unfortunately, already had it set up with INT, was trying CHAR to see if I could work around query problem... Changed table back to INT, reran the Admin.php script to update the user table:

Dump for USR table:
# Table structure for table usr
#

CREATE TABLE usr (
ID int(5) DEFAULT '0' NOT NULL auto_increment,
Uname char(50) NOT NULL,
real_name char(50) NOT NULL,
PRIMARY KEY (ID, ID)
);

#
# Dumping data for table usr
#

INSERT INTO usr VALUES (1, 'TEST', 'TEST1');
INSERT INTO usr VALUES (2, 'Test 2', '2 Test');
INSERT INTO usr VALUES (3, 'test 3', '3 test');

-----------------------------------------------
Here's the dump for the USERTEST that the script builds:
______________________________

CREATE TABLE usertest (
name_field varchar(50) NOT NULL,
present_insert_form_field enum('0','1') DEFAULT '1' NOT NULL,
present_ext_update_form_field enum('0','1') DEFAULT '1' NOT NULL,
present_search_form_field enum('0','1') DEFAULT '1' NOT NULL,
required_field enum('0','1') DEFAULT '0' NOT NULL,
present_results_search_field enum('0','1') DEFAULT '1' NOT NULL,
check_duplicated_insert_field enum('0','1') DEFAULT '0' NOT NULL,
type_field enum('text','textarea','password','insert_date','update_date','date','select_single','select_multiple_menu','select_multiple_checkbox','ID_user','password_record') DEFAULT 'text' NOT NULL,
separator_field char(2) DEFAULT '~' NOT NULL,
content_field enum('alphabetic','alphanumeric','numeric','web','email','phone','city') DEFAULT 'alphanumeric' NOT NULL,
select_options_field text NOT NULL,
foreign_key_field text NOT NULL,
db_primary_key_field varchar(255) NOT NULL,
select_type_field varchar(50) DEFAULT 'exactly/like/>/<' NOT NULL,
prefix_field text NOT NULL,
default_value_field text NOT NULL,
label_field varchar(50) NOT NULL,
width_field char(3) NOT NULL,
height_field char(3) NOT NULL,
maxlength_field char(3) DEFAULT '100' NOT NULL,
hint_insert_field varchar(100) NOT NULL,
order_form_field smallint(6) DEFAULT '0' NOT NULL,
PRIMARY KEY (name_field)
);

# Dumping data for table usertest
#

INSERT INTO usertest VALUES ('ID', '0', '1', '0', '0', '1', '0', 'text', '~', 'numeric', '', '', 'ID', 'exactly/like/>/<', '', '', 'ID', '', '', '100', '', 13);
INSERT INTO usertest VALUES ('Uname', '1', '1', '1', '0', '1', '0', 'text', '~', 'alphanumeric', '', '', '', 'exactly/like/>/<', '', '', 'Uname', '', '', '100', '', 14);
INSERT INTO usertest VALUES ('real_name', '1', '1', '1', '0', '1', '0', 'text', '~', 'alphanumeric', '', '', '', 'exactly/like/>/<', '', '', 'real_name', '', '', '100', '', 15);

Any thoughts?
D-

Dave · Feb 8, 2002

Eugenio!

Interesting...phpMyAdmin 2.2.3 has no problem adding/deleting/or editing fields of the source (or internal) tables.

Even went as far as doing a fresh install from the download zip 1.5b. Same problem.

Is there a way we can see a more detailed explaination of the error on attempt to update?

http://www.dw-graphics.com/dbtest/program_files/index.php

Love the rest, just gotta figure out where it's snagging?!?

Thx,
D-

Eugenio · Feb 11, 2002

Dave wrote:
> Dump for USR table:
> # Table structure for table usr
> #
>
> CREATE TABLE usr (
> ID int(5) DEFAULT '0' NOT NULL auto_increment,
> Uname char(50) NOT NULL,
> real_name char(50) NOT NULL,
> PRIMARY KEY (ID, ID)
> );

Why do you have to ID in primary key?

> # Dumping data for table usertest
> #
>
> INSERT INTO usertest VALUES ('ID', '0', '1', '0', '0', '1',
> '0', 'text', '~', 'numeric', '', '', 'ID',
> 'exactly/like/>/<', '', '', 'ID', '', '', '100', '', 13);
> INSERT INTO usertest VALUES ('Uname', '1', '1', '1', '0',
> '1', '0', 'text', '~', 'alphanumeric', '', '', '',
> 'exactly/like/>/<', '', '', 'Uname', '', '', '100', '', 14);
> INSERT INTO usertest VALUES ('real_name', '1', '1', '1', '0',
> '1', '0', 'text', '~', 'alphanumeric', '', '', '',
> 'exactly/like/>/<', '', '', 'real_name', '', '', '100', '',
> 15);

This one can't be a fresh intallation, because you have 13,14,15 as order_form_field.

(Latest version of DaDaBIK when this message was posted: 1.5b)

Dave · Feb 11, 2002

Hi Eugenio!

>Why do you have to ID in primary key?
>This one can't be a fresh intallation,
> because you have 13,14,15 as order_form_field.

Just declared it that way, but lets' change & Fresh install!

Here's the new table dump:
CREATE TABLE usr (
Uname char(50) NOT NULL,
real_name char(50) NOT NULL,
UNIQUE Uname (Uname)
);
-----------------------------------------------------
Internal Dbase Dump:

# Table structure for table usr_setup
#

CREATE TABLE usr_setup (
name_field varchar(50) NOT NULL,
present_insert_form_field enum('0','1') DEFAULT '1' NOT NULL,
present_ext_update_form_field enum('0','1') DEFAULT '1' NOT NULL,
present_search_form_field enum('0','1') DEFAULT '1' NOT NULL,
required_field enum('0','1') DEFAULT '0' NOT NULL,
present_results_search_field enum('0','1') DEFAULT '1' NOT NULL,
check_duplicated_insert_field enum('0','1') DEFAULT '0' NOT NULL,
type_field enum('text','textarea','password','insert_date','update_date','date','select_single','select_multiple_menu','select_multiple_checkbox','ID_user','password_record') DEFAULT 'text' NOT NULL,
separator_field char(2) DEFAULT '~' NOT NULL,
content_field enum('alphabetic','alphanumeric','numeric','web','email','phone','city') DEFAULT 'alphanumeric' NOT NULL,
select_options_field text NOT NULL,
foreign_key_field text NOT NULL,
db_primary_key_field varchar(255) NOT NULL,
select_type_field varchar(50) DEFAULT 'exactly/like/>/<' NOT NULL,
prefix_field text NOT NULL,
default_value_field text NOT NULL,
label_field varchar(50) NOT NULL,
width_field char(3) NOT NULL,
height_field char(3) NOT NULL,
maxlength_field char(3) DEFAULT '100' NOT NULL,
hint_insert_field varchar(100) NOT NULL,
order_form_field smallint(6) DEFAULT '0' NOT NULL,
PRIMARY KEY (name_field)
);

#
# Dumping data for table usr_setup
#

INSERT INTO usr_setup VALUES ('Uname', '1', '1', '1', '0', '1', '0', 'text', '~', 'alphanumeric', '', '', '', 'exactly/like/>/<', '', '', 'Uname', '', '', '100', '', 1);
INSERT INTO usr_setup VALUES ('real_name', '1', '1', '1', '0', '1', '0', 'text', '~', 'alphanumeric', '', '', '', 'exactly/like/>/<', '', '', 'real_name', '', '', '100', '', 2);

Same Problem!
http://www.dw-graphics.com/dbtest/program_files/index.php

What do you suggest? (Doah!)
D-

Dave · Feb 13, 2002

Eugenio!

Just installed your version 1.6, same error while trying to update. Running phpMyAdmin will correctly update and delete fields!

Here's the sample area:
http://www.dw-graphics.com/dbtest/program_files/index.php

Dump for Table:
# Table structure for table usr
#

CREATE TABLE usr (
Uname char(50) NOT NULL,
real_name char(50) NOT NULL,
UNIQUE Uname (Uname)
);
-----------------------
Dump for Internal Table
# Host: localhost
# Generation Time: Feb 13, 2002 at 10:07 AM
# Server version: 3.22.32
# PHP Version: 4.0.3pl1
# Database : dwg2
# --------------------------------------------------------

#
# Table structure for table usr_select
#

CREATE TABLE usr_select (
name_field varchar(50) NOT NULL,
present_insert_form_field enum('0','1') DEFAULT '1' NOT NULL,
present_ext_update_form_field enum('0','1') DEFAULT '1' NOT NULL,
present_search_form_field enum('0','1') DEFAULT '1' NOT NULL,
required_field enum('0','1') DEFAULT '0' NOT NULL,
present_results_search_field enum('0','1') DEFAULT '1' NOT NULL,
check_duplicated_insert_field enum('0','1') DEFAULT '0' NOT NULL,
type_field enum('text','textarea','password','insert_date','update_date','date','select_single','select_multiple_menu','select_multiple_checkbox','ID_user','password_record') DEFAULT 'text' NOT NULL,
separator_field char(2) DEFAULT '~' NOT NULL,
content_field enum('alphabetic','alphanumeric','numeric','web','email','phone','city') DEFAULT 'alphanumeric' NOT NULL,
select_options_field text NOT NULL,
foreign_key_field text NOT NULL,
db_primary_key_field varchar(255) NOT NULL,
select_type_field varchar(50) DEFAULT 'exactly/like/>/<' NOT NULL,
prefix_field text NOT NULL,
default_value_field text NOT NULL,
label_field varchar(50) NOT NULL,
width_field char(3) NOT NULL,
height_field char(3) NOT NULL,
maxlength_field char(3) DEFAULT '100' NOT NULL,
hint_insert_field varchar(100) NOT NULL,
order_form_field smallint(6) DEFAULT '0' NOT NULL,
PRIMARY KEY (name_field)
);

#
# Dumping data for table usr_select
#

INSERT INTO usr_select VALUES ('Uname', '1', '1', '1', '0', '1', '0', 'text', '~', 'alphanumeric', '', '', '', 'exactly/like/>/<', '', '', 'Uname', '', '', '100', '', 1);
INSERT INTO usr_select VALUES ('real_name', '1', '1', '1', '0', '1', '0', 'text', '~', 'alphanumeric', '', '', '', 'exactly/like/>/<', '', '', 'real_name', '', '', '100', '', 2);

What is your recommedation to correct?

Thanks again!
Dave

bob · Feb 13, 2002

I think I have EXACTLY the same problem here....

I have just installed DaDaBIK v1.6 and are very pleased with it potential. Thank you for creating and making available such a valuable tool. It is very much appreciated.

I do have on error that precludes me from updating the database. I can add records and deleted records with out a problem. When I try to update (within form.php) the following error is returned:

"Error during query execution"

But the source of the error page includes the following javascript...

function fill_cap(city_field){
temp = 'document.contacts_form.'+city_field+'.value';
city = eval(temp);
cap=open("fill_cap.php?city="+escape(city),"schermo","toolbar=no,

I have no idea what this relates to as there is no city filed in my data of any file called fill_cap.php. I assume this is part of the demo files that came with the package but there is no reference as to how to modify them.

BTW: phpMyAdmin can modify the db without any problem.

My system includes MySQL 3.22.27 , PHP Version 4.0.6, phpMyAdmin 2.2.3, FreeBSD 4.4-STABLE

Any assistance or suggestions as to how I can get the Update function working would be most appreciated.

Thank you,

-Bob

Eugenio · Feb 16, 2002

bob wrote:
>
> I think I have EXACTLY the same problem here....

HI Dave, hi Bob,
please modify the line 57 of db_functions.php in this way:

$results = mysql_query("$sql_statm", $conn) or die ('Error during query execution'.$sql_statm.mysql_error());

in this way we can show exactly the query and the MySQL error message.

Let me know the results.

(Latest version of DaDaBIK when this message was posted: 1.6)

Eugenio · Feb 16, 2002

bob wrote:

> But the source of the error page includes the following
> javascript...
>
>
>
> function fill_cap(city_field){
> temp = 'document.contacts_form.'+city_field+'.value';
> city = eval(temp);
> cap=open("fill_cap.php?city="+escape(city),"schermo","toolbar=no,
>
>
>
> I have no idea what this relates to as there is no city filed
> in my data of any file called fill_cap.php. I assume this is
> part of the demo files that came with the package but there
> is no reference as to how to modify them.

Don't worry about it, you haven't to modify it and it doesn't create any problems.

(Latest version of DaDaBIK when this message was posted: 1.6)

bob · Feb 16, 2002

Hi Eugenio,

Thanks for looking into this.

Here is the new error that is displayed after making the change.

Error during query execution update coins set coinid = '1', owner = 'Bob=', address = 'test@test.ca', launched = '2002-02-02', active = '0' where coinid = '1' limit 1 You have an error in your SQL syntax near 'limit 1' at line 1

Dave · Feb 16, 2002

Hi Eugenio, Hi Bob!:

Similar for me, I get the error on Unique field:

Error during query executionupdate usr set Uname = 'Test 2b', real_name = '2 Test' where Uname = 'Test 2' limit 1You have an error in your SQL syntax near 'limit 1' at line 1

Error on std field:
Error during query executionupdate usr set Uname = 'Test 2', real_name = '2 Test-b' where Uname = 'Test 2' limit 1You have an error in your SQL syntax near 'limit 1' at line 1

Test Dbase is at:
http://www.dw-graphics.com/dbtest/program_files/index.php

Thanks for any help!
D-

Eugenio · Feb 17, 2002

Dave wrote:
>
> Hi Eugenio, Hi Bob!:
>
> Similar for me, I get the error on Unique field:
>
> Error during query executionupdate usr set Uname = 'Test 2b',
> real_name = '2 Test' where Uname = 'Test 2' limit 1You have
> an error in your SQL syntax near 'limit 1' at line 1

Hi,
I have replicated your tables on my system and everything works fine, also the query seems ok.
Dave,
send me:
- the zip version of the DaDaBIK that you are using (don't modify anything!!)
- the dumps of both the main and the internal database.
Ok?

(Latest version of DaDaBIK when this message was posted: 1.6)

Dug · Feb 17, 2002

Hi,

I too get the same error message.
When running exactly the same DB on my localhost everything is fine & the record updates correctly.
Once uploaded to my web server it brings up this error message.

Dave · Feb 18, 2002

Hi Eugenio!

Sent those files and copied the dumps for both the table and internal dbase table.

Dugs got an interesting point, I'm running on a unix box. No problem passing the same commands through MyPhpAdmin. Quickly updates/Deletes/Adds any record I should require....

...now if we can only get the dadabink interface to do the same, I'd be a happy camper!

Thanks for all the help! I'm anxiously awaiting yer reply!

D-

Eugenio · Feb 19, 2002

Dave wrote:
>
> Hi Eugenio!
>
> Sent those files and copied the dumps for both the table and
> internal dbase table.
>
> Dugs got an interesting point, I'm running on a unix box. No
> problem passing the same commands through MyPhpAdmin. Quickly
> updates/Deletes/Adds any record I should require....
>
> ...now if we can only get the dadabink interface to do the
> same, I'd be a happy camper!
>
> Thanks for all the help! I'm anxiously awaiting yer reply!

Hi,
I've just run your file/dumps both locally (Win2000/IIS) and on a Linux/Apache server, everything works fine.

It could be a permission problem, your user has the correct update rights?

(Latest version of DaDaBIK when this message was posted: 1.6)

Dave · Feb 19, 2002

Hi Eugenio!

Yes, the user has correct update/delete and Add rights... Only problem is on the update.

Any thoughts? the sample dbase is running on:
http://www.dw-graphics.com/dbtest/program_files/index.php

running phpMyAdmin has no problem adding/updating or deleting with the same set of permissions, can you think of any other snags?

Thx,
D-

bob · Feb 19, 2002

I have now tried this on every database on my system. The exact same error occurs with the update on every one. I have made numerous new test databases, the most minimal one only has one field. Same problem on the update. Add and delete work fine. MySql and phpMyAdmin work flawlessly an every database on my system.

error on Update Query

Dave

Guest

Eugenio

Guest

Dave

Guest

Eugenio

Guest

Dave

Guest

Dave

Guest

Eugenio

Guest

Dave

Guest

Dave

Guest

bob

Guest

Eugenio

Guest

Eugenio

Guest

bob

Guest

Dave

Guest

Eugenio

Guest

Dug

Guest

Dave

Guest

Eugenio

Guest

Dave

Guest

bob

Guest