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_connexio are
the outputs of the SELECT).

** UPDATE usuaris SET data_ultima_fallada = 'NULL' WHERE userid = 'test'
** SELECT * FROM usuaris WHERE userid = 'test'
--> data_registre 20031120231019
--> data_ultima_connexio 20031120231019

** UPDATE usuaris SET data_ultima_connexio = '20031120231112' WHERE userid =
'test'
** SELECT * FROM usuaris WHERE userid = 'test'
--> data_registre 20031120231112
--> data_ultima_connexio 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_connexio. 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_connexio TIMESTAMP(14),
data_ultima_fallada 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 [url]WWW...:[/url] [url]http://www.cepba.upc.es[/url] Tel: +34-93-401 74 23
o//o e-mail: [email]suport@cepba.upc.es[/email] Fax: +34-93-401 25 77
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 <harald@cepba.upc.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_connexio
> are
> the outputs of the SELECT).
>
> ** UPDATE usuaris SET data_ultima_fallada = 'NULL' WHERE userid = 'test'
> ** SELECT * FROM usuaris WHERE userid = 'test'
> --> data_registre 20031120231019
> --> data_ultima_connexio 20031120231019
>
> ** UPDATE usuaris SET data_ultima_connexio = '20031120231112' WHERE
> userid =
> 'test'
> ** SELECT * FROM usuaris WHERE userid = 'test'
> --> data_registre 20031120231112
> --> data_ultima_connexio 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_connexio. 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_connexio TIMESTAMP(14),
> data_ultima_fallada 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
[url]www.creative-light.com[/url]
tom (at) creative (dash) light (dot) com
do NOT send email to [email]jamesbutler@willglen.net[/email] (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 <harald@cepba.upc.es> wrote in message news:
> <3FBDE2E4.952EB49E@cepba.upc.es>...
> 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_connexio are
> the outputs of the SELECT).
>
> ** UPDATE usuaris SET data_ultima_fallada = 'NULL' WHERE userid = 'test'
> ** SELECT * FROM usuaris WHERE userid = 'test'
> --> data_registre 20031120231019
> --> data_ultima_connexio 20031120231019
>
> ** UPDATE usuaris SET data_ultima_connexio = '20031120231112' WHERE userid =
> 'test'
> ** SELECT * FROM usuaris WHERE userid = 'test'
> --> data_registre 20031120231112
> --> data_ultima_connexio 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_connexio. 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_connexio TIMESTAMP(14),
> data_ultima_fallada 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 [url]WWW...:[/url] [url]http://www.cepba.upc.es[/url] Tel: +34-93-401 74 23
> o//o e-mail: [email]suport@cepba.upc.es[/email] Fax: +34-93-401 25 77
> 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