php & mysql update problem

[Harald Servat Gelabert]

Dear news-team

I'm using MySQL 4.0.15a (with PHP 4.3.4rc1 and Apache 2.0.47 under FreeBSD).

I'm having problems when updating a column of a table (it updates an extra
column).

As an example, I provide this sample (data_registre, data_ultima_con nexio are
the outputs of the SELECT).

** UPDATE usuaris SET data_ultima_fal lada = 'NULL' WHERE userid = 'test'
** SELECT * FROM usuaris WHERE userid = 'test'
--> data_registre 20031120231019
--> data_ultima_con nexio 20031120231019

** UPDATE usuaris SET data_ultima_con nexio = '20031120231112 ' WHERE userid =
'test'
** SELECT * FROM usuaris WHERE userid = 'test'
--> data_registre 20031120231112
--> data_ultima_con nexio 20031120231112

The PHP statements are exactly those that begin with **, and --> shows a
subset of a results received.
As you can see, the last update modifies both data_registre and
data_ultima_con nexio. These updates and selects run alone and in sequential
mode, so data_registre should be always the same.

I really don't figure where is the problem. Moreover I add the table definition.

CREATE TABLE usuaris (userid VARCHAR(16),
nom_complet VARCHAR(64) NOT NULL,
correu VARCHAR(64) NOT NULL,
data_registre TIMESTAMP(14) NOT NULL,
data_ultima_con nexio TIMESTAMP(14),
data_ultima_fal lada TIMESTAMP(14),
paraula_de_pas VARCHAR(32) NOT NULL,
privilegis VARCHAR(255),
fallades INTEGER NOT NULL,
PRIMARY KEY (userid));

Has anyone any idea that could solve this problem?

Thanks in advance.

--
_______________ _______________ _______________ _______________ ____________
Harald Servat Gelabert (harald at cepba dot upc dot es)
o//o Centre Europeu de Paral.lelisme de Barcelona CEPBA
o//o WWW...: http://www.cepba.upc.es Tel: [phone]
o//o e-mail: su****@cepba.up c.es Fax: [phone]
o//o CEPBA c/Jordi Girona, 1-3, Mòdul D6. E-08034 Barcelona, Catalunya
_______________ _______________ _______________ _______________ ____________

The fundamental difference between Unix and Macintosh operating system
is that Unix was designed to please programmers, whereas the Mac was
designed to please users. (Windows, on the other hand, was designed to
please accountants, but that's another story)
-- from The UNIX haters handbook, page 163

[Tom Thackrey]

On 21-Nov-2003, Harald Servat Gelabert <ha****@cepba.u pc.es> wrote:

I'm using MySQL 4.0.15a (with PHP 4.3.4rc1 and Apache 2.0.47 under
FreeBSD).

I'm having problems when updating a column of a table (it updates an extra
column).

As an example, I provide this sample (data_registre, data_ultima_con nexio
are
the outputs of the SELECT).

** UPDATE usuaris SET data_ultima_fal lada = 'NULL' WHERE userid = 'test'
** SELECT * FROM usuaris WHERE userid = 'test'
--> data_registre 20031120231019
--> data_ultima_con nexio 20031120231019

** UPDATE usuaris SET data_ultima_con nexio = '20031120231112 ' WHERE
userid =
'test'
** SELECT * FROM usuaris WHERE userid = 'test'
--> data_registre 20031120231112
--> data_ultima_con nexio 20031120231112

The PHP statements are exactly those that begin with **, and --> shows a
subset of a results received.
As you can see, the last update modifies both data_registre and
data_ultima_con nexio. These updates and selects run alone and in
sequential
mode, so data_registre should be always the same.

I really don't figure where is the problem. Moreover I add the table
definition.

CREATE TABLE usuaris (userid VARCHAR(16),
nom_complet VARCHAR(64) NOT NULL,
correu VARCHAR(64) NOT NULL,
data_registre TIMESTAMP(14) NOT NULL,
data_ultima_con nexio TIMESTAMP(14),
data_ultima_fal lada TIMESTAMP(14),
paraula_de_pas VARCHAR(32) NOT NULL,
privilegis VARCHAR(255),
fallades INTEGER NOT NULL,
PRIMARY KEY (userid));

Has anyone any idea that could solve this problem?

TIMESTAMP columns are automatically updated whenever you modify the row.
Change the TIMESTAMP columns to DATETIME.

--
Tom Thackrey
www.creative-light.com
tom (at) creative (dash) light (dot) com
do NOT send email to ja*********@wil lglen.net (it's reserved for spammers)

[Wes Bailey]

Your problem is in the design of your database table. MySQL has the
special property that the first column of TIMESTAMP datatype will
automatically be updated when a record is changed with the UPDATE DML
statement. My suggestion is to add a date_modified column before the
data_registre column and you can choose to ignore but it will solve
your problem.

Wes

Harald Servat Gelabert <ha****@cepba.u pc.es> wrote in message news:
<3F************ ***@cepba.upc.e s>...
Dear news-team

I'm using MySQL 4.0.15a (with PHP 4.3.4rc1 and Apache 2.0.47 under FreeBSD).

I'm having problems when updating a column of a table (it updates an extra
column).

As an example, I provide this sample (data_registre, data_ultima_con nexio are
the outputs of the SELECT).

** UPDATE usuaris SET data_ultima_fal lada = 'NULL' WHERE userid = 'test'
** SELECT * FROM usuaris WHERE userid = 'test'
--> data_registre 20031120231019
--> data_ultima_con nexio 20031120231019

** UPDATE usuaris SET data_ultima_con nexio = '20031120231112 ' WHERE userid =
'test'
** SELECT * FROM usuaris WHERE userid = 'test'
--> data_registre 20031120231112
--> data_ultima_con nexio 20031120231112

The PHP statements are exactly those that begin with **, and --> shows a
subset of a results received.
As you can see, the last update modifies both data_registre and
data_ultima_con nexio. These updates and selects run alone and in sequential
mode, so data_registre should be always the same.

I really don't figure where is the problem. Moreover I add the table definition.

CREATE TABLE usuaris (userid VARCHAR(16),
nom_complet VARCHAR(64) NOT NULL,
correu VARCHAR(64) NOT NULL,
data_registre TIMESTAMP(14) NOT NULL,
data_ultima_con nexio TIMESTAMP(14),
data_ultima_fal lada TIMESTAMP(14),
paraula_de_pas VARCHAR(32) NOT NULL,
privilegis VARCHAR(255),
fallades INTEGER NOT NULL,
PRIMARY KEY (userid));

Has anyone any idea that could solve this problem?

Thanks in advance.

--
_______________ _______________ _______________ _______________ ____________
Harald Servat Gelabert (harald at cepba dot upc dot es)
o//o Centre Europeu de Paral.lelisme de Barcelona CEPBA
o//o WWW...: http://www.cepba.upc.es Tel: [phone]
o//o e-mail: su****@cepba.up c.es Fax: [phone]
o//o CEPBA c/Jordi Girona, 1-3, Mòdul D6. E-08034 Barcelona, Catalunya
_______________ _______________ _______________ _______________ ____________

The fundamental difference between Unix and Macintosh operating system
is that Unix was designed to please programmers, whereas the Mac was
designed to please users. (Windows, on the other hand, was designed to
please accountants, but that's another story)
-- from The UNIX haters handbook, page 163