0

I'm going to update a lot of database lines and i want to know how many lines i've updated.

Example : if i've database with the following 4 lines

INSERT INTO `drink` VALUES (1, 'Non-Alcoholic', 'tea');
INSERT INTO `drink` VALUES (2, 'Non-Alcoholic', 'tea');
INSERT INTO `drink` VALUES (3, 'Non-Alcoholic', 'coffee');
INSERT INTO `drink` VALUES (4, 'Non-Alcoholic', 'pepsi');

and i'm going to make updates using the following

$sql= "update drink set cat='tea' WHERE cat= 'Non-Alcoholic' AND subcat = 'tea'";

it would be clear that it would update only 2 lines

INSERT INTO `drink` VALUES (1, 'tea', 'tea'); 
INSERT INTO `drink` VALUES (2, 'tea', 'tea'); 
INSERT INTO `drink` VALUES (3,'Non-Alcoholic', 'coffee'); 
INSERT INTO `drink` VALUES (4,'Non-Alcoholic', 'pepsi');

Now my question how i know how many lines it have updated, i want it to be shown as message or whatever but i must know it.

so any idea or how to do it thank you for help

Reham Fahmy
  • 4,937
  • 15
  • 50
  • 71
  • how are you calling this in your php? – Daniel A. White Oct 13 '11 at 14:41
  • Bear in mind that in mysql the number of rows matched is not always the number of rows updated. For instance in your update statement above, if you used "... set cat='Non-Alcoholic' ...", rows matched would be 2 but rows affected would be 0. – JohnC Oct 13 '11 at 14:58

6 Answers6

2

You can either use the php function mysql_affected_rows() or mysqli_affected_rows()

Or follow up the update statement with a sql statement.

SELECT row_count() as affected_rows

Mysql: http://www.php.net/manual/en/function.mysql-affected-rows.php
Mysqli: http://php.net/manual/en/mysqli.affected-rows.php http://dev.mysql.com/doc/refman/5.0/en/information-functions.html

Johan
  • 74,508
  • 24
  • 191
  • 319
2

mysql_affected_rows : Get number of affected rows in previous MySQL operation :

...
$updated_count = mysql_affected_rows();
...

Put above statement after your queries to count the affected rows.

masoud
  • 55,379
  • 16
  • 141
  • 208
2

You need mysqli::affected_rows();

Mob
  • 10,958
  • 6
  • 41
  • 58
1

Use mysql_affected_rows http://php.net/manual/ro/function.mysql-affected-rows.php

Bogdan
  • 43,166
  • 12
  • 128
  • 129
1

You can do it with PHP : mysql_affected_rows() or in C : mysql_info()

Nanocom
  • 3,696
  • 4
  • 31
  • 46
-1

This code will count the number of rows (entries / records) in a MySQL database table and then display it using echo on the screen

<?php 



// Connect to the database 
db_connect(); 

// Query the database and get the count 
$result = mysql_query("SELECT * FROM drink"); 
$num_rows = mysql_affected_rows($result); 

// Display the results 
echo $num_rows; 

?>
Pyadav
  • 135
  • 7