Page 1 of 2

Update doesn't work

Posted: Sun Feb 25, 2007 7:44 am
by dude81
Hi,

This is very strange problem. I've defined two fields to be unique in a table and they can be null if no values are filled into those fields of the table for a particular row. Now after few queries I evaluate the values for those fields and update the fields of the table through an update query. The query results in zero afftected rows.

Is it because of unique key defined on two of them?? :?

Posted: Sun Feb 25, 2007 10:08 am
by feyd
dude81 wrote:Is it because of unique key defined on two of them?
I have no idea. It would help to know more. Particularly the table schema (SHOW CREATE TABLE), your query and some sample data too.

Posted: Sun Feb 25, 2007 9:57 pm
by dude81
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


The following is the table structure 
[syntax="sql"]
CREATE TABLE `info` (
  `id` bigint(8) NOT NULL auto_increment,
  `name` varchar(255) collate latin1_general_ci default NULL,
  `user_id1` decimal(10,2) default NULL,
  `user_id2` decimal(10,2) default NULL,
  `server_id1` decimal(10,2) default NULL,
  `server_id2` decimal(10,2) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`,`server_id1`,`server_id2`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1
Before the update query I insert user values of the numbers then I pass the same to a function which will identify corresponding server ids and then I update the same table with following query.

Code: Select all

UPDATE IGNORE info set server_id1='-512.67' and server_id2='121.33' where id='50'
It says zero affected rows


feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Sun Feb 25, 2007 10:53 pm
by volka
http://dev.mysql.com/doc/refman/5.1/en/mysql-affected-rows.html wrote:For UPDATE statements, if you specify the CLIENT_FOUND_ROWS flag when connecting to mysqld, mysql_affected_rows() returns the number of rows matched by the WHERE clause. Otherwise, the default behavior is to return the number of rows actually changed.
I guess you haven't set CLIENT_FOUND_ROWS and the query actually didn't change a value.

...or the query fails and you have no error handling in your script.

Posted: Sun Feb 25, 2007 10:55 pm
by Begby
Get rid of IGNORE and see if you get any key errors.

Posted: Sun Feb 25, 2007 10:59 pm
by dude81
Now, How and where I should do this??

Posted: Sun Feb 25, 2007 11:05 pm
by volka
Do what?
[] set CLIENT_FOUND_ROWS
[] add error handling
[] get rid of IGNORE
?

Posted: Sun Feb 25, 2007 11:31 pm
by dude81
But the software uses mysql_connect, when I change it to mysql_real_connect, everything goes blank.

Posted: Sun Feb 25, 2007 11:51 pm
by volka
a) What is mysql_real_connect? Why use it?
b) Please answer the previous question: Do what?

Posted: Mon Feb 26, 2007 8:26 am
by dude81
Oops there is no such function in php as mysql_real_connect. It is a function f or C :( I believe. I didn't know where to set the flag. I didn't set the flag still..... Sorry I was looking at my new problem(PHP5:SOAP) which is perhaps bigger than this.

Posted: Mon Feb 26, 2007 10:38 pm
by dude81
After setting this flag server works very slow. why is this
I gave it this way

Code: Select all

@mysql_connect($dbhost, $dbuser, $dbpassword,'',CLIENT_FOUND_ROWS);

Posted: Mon Feb 26, 2007 11:00 pm
by feyd
The manual doesn't list CLIENT_FOUND_ROWS as a valid option.

Remove the "@" operator.

Posted: Mon Feb 26, 2007 11:09 pm
by dude81
I'm afraid the query doesn't work directly on mysql through commandline also. Forget about php executing it.
It says affected rows zero. Is it becuase of the decimal structure :?:

Posted: Tue Feb 27, 2007 1:25 am
by dude81
This shows how much we put all our brains overlooking a silly issue :oops: . I should agree sometimes this happens with everybody.
UPDATE IGNORE info SET server_id1='-512.67' AND server_id2='121.33' WHERE id='50'
Fault is in using AND in the query. Instead we use comma(,)

Posted: Tue Feb 27, 2007 5:37 am
by volka
ah, that's the
volka wrote:...or the query fails and you have no error handling in your script.
part ;)