Get the Number of Affected Rows in a MySQL Update Statement

PHP/mysql get number of affected rows of UPDATE statement

if you're using PDO (wich i would recommend), for a direct query exec() returns the number of affected rows. for Prepared Statements theres a method called rowCount().

if you're using the mysql-functions, there's mysql_affected_rows().

EDIT:
seems like you're using the mysql-functions. mysql_num_rows, wich is what you're using, returns the length of your result set (for SELECT-Statements for example). what you need to use is mysql_affected_rows (as already said).

How to get affected rows after update statement in mysql?

mysql_info — Get information about the most recent query

$recent = mysql_info();

Useful implementation Example

Count number of affected rows over several queries passed to MySql in java code

1. Check what returns select * from Activity where ActivityName=?.

2. Check what returns select ... where ... (select ...).

After your explanation of your goal in the comments there are other solutions to add values that don't exists yet.

3. Define column ActivityName is a primary key (if not done yes). Then don't check anything, don't do any select, just do insert:

    try {
      // statement to insert like
      // insert into Activity (ActivityName, EmployeeeName, DepartmentName, roleId)
      //     values (?, ?, ?, ?)
    } catch ... {
      // If the exception is caused by primary key violation,
      // then such ActivityName already existed and we can ignore this exception
    }

Why not check first if such ActvityName already exists? Because if you have many requests in parallel from the same user or from many other users, then in the time between your check and between inserting new value some other request can insert this value, and you get primary key violation exception. That's why you need try/catch any way. That's why just do insert without any check and use proper try/catch.

Return number of rows affected by SQL UPDATE statement in Java

Calling executeUpdate() on your PreparedStatement should return an int, the number of updated records.

Getting the number of the rows affected by update or insert

When function/trigger is called, the new internal execution state structure is created (PLpgSQL_execstate) with ROW_COUNT value (eval_processed property) set to 0. So inside function you can get ROW_COUNT only for statements inside the same function. As a workaround you can pass sql statement as the text argument to this procedure and execute it inside.

MySQL after update trigger with number of affected rows condition

Pattern:

CREATE TRIGGER ...
...
BEGIN
INSERT INTO user_log (user_id,action,old_data,new_data)
SELECT NEW.user_id,
       CONCAT('Changed ', OLD.name, "'s ", columnname),
       oldvalue,
       newvalue
FROM ( SELECT 'name' columnname, OLD.name oldvalue, NEW.name newvalue
       UNION ALL
       SELECT 'address', OLD.address, NEW.address
       UNION ALL
       SELECT 'city', OLD.city, NEW.city
       UNION ALL
       SELECT 'phone', OLD.phone, NEW.phone
     ) data
WHERE NOT oldvalue <=> newvalue;       
END;

Also you may use ROW() constructor instead of SELECT .. UNION ALL.

https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=c63b122abedf9481d72129bee0d2d87d

---

Related Topics