By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,962 Members | 1,776 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,962 IT Pros & Developers. It's quick & easy.

php & mysql update problem

P: n/a
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 WWW...: http://www.cepba.upc.es Tel: +34-93-401 74 23
o//o e-mail: su****@cepba.upc.es Fax: +34-93-401 25 77
o//o CEPBA c/Jordi Girona, 1-3, Ṃ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
Jul 17 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

On 21-Nov-2003, Harald Servat Gelabert <ha****@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
www.creative-light.com
tom (at) creative (dash) light (dot) com
do NOT send email to ja*********@willglen.net (it's reserved for spammers)
Jul 17 '05 #2

P: n/a
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.upc.es> wrote in message news:
<3F***************@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 WWW...: http://www.cepba.upc.es Tel: +34-93-401 74 23
o//o e-mail: su****@cepba.upc.es Fax: +34-93-401 25 77
o//o CEPBA c/Jordi Girona, 1-3, Ṃ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

Jul 17 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.