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: +34-93-401 74 23
o//o e-mail: suport@cepba.up c.es 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]

Re: php & mysql update problem


On 21-Nov-2003, Harald Servat Gelabert <harald@cepba.u pc.es> wrote:
[color=blue]
> 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?[/color]

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

--
Tom Thackrey

https://www.creative-light.com

tom (at) creative (dash) light (dot) com
do NOT send email to jamesbutler@wil lglen.net (it's reserved for spammers)

[Wes Bailey]

Re: php &amp; mysql update problem

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
[color=blue]
> Harald Servat Gelabert <harald@cepba.u pc.es> wrote in message news:
> <3FBDE2E4.952EB 49E@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: +34-93-401 74 23
> o//o e-mail: suport@cepba.up c.es 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[/color]