Ad:

MySQL Discussion Thread
Unsolved
Views: 5417

Permalink

Jun 16th, 2009

Rows affected in mysql

Expand Post »

Hi,

How to find number of rows affected in update statement using mysql (not using php mysql).

Thanks,
Pradeep

Similar Threads

pradeepktg

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

29 posts
since Jun 2009

Permalink

Jun 18th, 2009

Re: Rows affected in mysql

brief explanation please....Mr Pradeep

bhanu1225

Reputation Points: 10

Solved Threads: 1

Junior Poster in Training

Offline

59 posts
since Dec 2008

Permalink

Jun 18th, 2009

Re: Rows affected in mysql

Hi bhanu1225,

Thanks! Actually, am trying to update a record say: update tbl_name set name='bhanu1225' where name='pradeep' and id='1';

If the update is success, i want to print a messgae 'successfully updated' in frontend. For this i want to know if a record is updated in database or not. Am using mysql and jsp.

Thanks,

pradeepktg

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

29 posts
since Jun 2009

Permalink

Jun 18th, 2009

Re: Rows affected in mysql

Hi.

The ROW_COUNT function will return the number of rows in a previous INSERT, UPDATE or DELETE statement.

Atli

Reputation Points: 93

Solved Threads: 70

Posting Pro

Offline

526 posts
since May 2007

Permalink

Jun 19th, 2009

Re: Rows affected in mysql

Hi Alti,
row_count() is working properly in command prompt, but whereas in query browser,it always shows -1 value even after records are updated successfully.

I would be better if u can give sample code on how to get row_count value in front end.

Thanks,

pradeepktg

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

29 posts
since Jun 2009

Permalink

Jun 19th, 2009

Re: Rows affected in mysql

Ok.

It seems query browser executes queries on separate connections, or something like that, so when you execute a INSERT/UPDATE/DELETE and then a SELECT ROW_COUNT(); query, the data from the latter will no longer be available to the function.

An easy fix is to just create a wrapper procedure.
For example:

mysql Syntax (Toggle Plain Text)
DELIMITER $$
 
DROP PROCEDURE IF EXISTS `test`.`rc_exec`$$
CREATE PROCEDURE `test`.`rc_exec` (IN command VARCHAR(60000))
BEGIN
	SET @query = command;
	PREPARE stmt FROM @query;
	EXECUTE stmt;
 
	SELECT ROW_COUNT() AS 'Affected rows';
END$$
 
DELIMITER ;
DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`rc_exec`$$
CREATE PROCEDURE `test`.`rc_exec` (IN command VarChar(60000))
BEGIN
	SET @query = command;
	PREPARE stmt FROM @query;
	EXECUTE stmt;

	SELECT ROW_COUNT() AS 'Affected rows';
END$$

DELIMITER ;

Which you could then call like:

mysql Syntax (Toggle Plain Text)
CALL rc_exec("
	INSERT INTO myTable(First, Second)
	VALUES  ('Testing First', 'Testing Second'),
                ('Testing First again', 'Teststing Second again');");
CALL rc_exec("
	INSERT INTO myTable(First, Second)
	VALUES  ('Testing First', 'Testing Second'),
                ('Testing First again', 'Teststing Second again');");

Which returns a single field for 'Affected rows' with the value 2.

Last edited by Atli; Jun 19th, 2009 at 5:00 am.

Atli

Reputation Points: 93

Solved Threads: 70

Posting Pro

Offline

526 posts
since May 2007

Permalink

Jun 19th, 2009

Re: Rows affected in mysql

Ok. Thanks Atli ! Is there any other options like mysql_affected_rows() as in php???

pradeepktg

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

29 posts
since Jun 2009

Permalink

Jun 19th, 2009

Re: Rows affected in mysql

The ROW_COUNT function is the mysql_affected_rows equivalent in MySQL.

Keep in mind that the query browser is just a development tool. It doesn't execute queries the same way a PHP or JSP script would. They wouldn't need the procedure to use the ROW_COUNT function.

For example, this would print "3":

php Syntax (Toggle Plain Text)
<?php
$inResult = mysql_query("INSERT INTO tbl VALUES()()()");
$rcResult = mysql_query("SELECT ROW_COUNT()");
$count = mysql_result($rcResult, 0, 0);
echo $count;
?>
<?php
$inResult = mysql_query("INSERT INTO tbl VALUES()()()");
$rcResult = mysql_query("SELECT ROW_COUNT()");
$count = mysql_result($rcResult, 0, 0);
echo $count;
?>

And I'm sure the JSP equivalent for that would to.
And in languages like those, you don't actually have to use the ROW_COUNT function. They have functions like the mysql_num_rows function.

The ROW_COUNT function isn't really mean to be used like this.
It's more aimed at procedures and such.

Atli

Reputation Points: 93

Solved Threads: 70

Posting Pro

Offline

526 posts
since May 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: Return zero if query returns nothing?

Next Thread in MySQL Forum Timeline: help with (hopefully) simple nested query

DaniWeb Message

Cancel Changes

User Name
Password