Get number of rows matched by UPDATE query with PHP mysqli
it's in the options of mysqli_real_connect
.
Also introduced in PDO::MySQL in PHP 5.3.
PHP, MySQL - can you distinguish between rows matched and rows affected?
From the MySQL documentation for mysql_affected_rows
:
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.
With mysqli, you can specify the CLIENT_FOUND_ROWS using mysqli::real_connect.
$db = mysqli_init();
$db->real_connect('host', 'username', 'password', 'dbname', '3306', null, MYSQLI_CLIENT_FOUND_ROWS);
In PDO, the constant is named PDO::MYSQL_ATTR_FOUND_ROWS
$db = new PDO('mysql:dbname=mydatabase;host=myhost', 'username', 'password', array(
PDO::MYSQL_ATTR_FOUND_ROWS => true
));
With the old and deprecated MySQL extension, you can specify the CLIENT_FOUND_ROWS passing the value 2 as the 5th parameter for mysql_connect (source).
How can I count the numbers of rows that a MySQL query returned?
Getting total rows in a query result...
You could just iterate the result and count them. You don't say what language or client library you are using, but the API does provide a mysql_num_rows function which can tell you the number of rows in a result.
This is exposed in PHP, for example, as the mysqli_num_rows function. As you've edited the question to mention you're using PHP, here's a simple example using mysqli functions:
$link = mysqli_connect("localhost", "user", "password", "database");
$result = mysqli_query($link, "SELECT * FROM table1");
$num_rows = mysqli_num_rows($result);
echo "$num_rows Rows\n";
Getting a count of rows matching some criteria...
Just use COUNT(*) - see Counting Rows in the MySQL manual. For example:
SELECT COUNT(*) FROM foo WHERE bar= 'value';
Get total rows when LIMIT is used...
If you'd used a LIMIT clause but want to know how many rows you'd get without it, use SQL_CALC_FOUND_ROWS in your query, followed by SELECT FOUND_ROWS();
SELECT SQL_CALC_FOUND_ROWS * FROM foo
WHERE bar="value"
LIMIT 10;
SELECT FOUND_ROWS();
For very large tables, this isn't going to be particularly efficient, and you're better off running a simpler query to obtain a count and caching it before running your queries to get pages of data.
The update query is successful but num_rows returns error
I think you are trying to get the number of records get affected. mysql_affected_rows returns the number of rows affected by the last INSERT
, UPDATE
, REPLACE
or DELETE
query.
To check the affected rows use
$sql = "UPDATE `tbl_override_marks` SET final_mark ='$mark', confirmed ='Y'
WHERE fk_class_sub_id ='$cls_sub'";
$result = $conn->query($sql);
echo $conn->affected_rows;
You can check the manual mysqli::$affected_rows
mysqli-query returns true on update, but no rows affected
Okay, so I found the problem.
I was starting a transaction within a method, and I never comitted.
My apologies for wasting your time, you could not have figured out the problem by looking at the code I provided. I was sure the issue was within that query method (but I was wrong).
I'll vote up each and everyone of you that tried to help out.
Related Topics
Group Duplicate Array Keys in a Multidimensional Array into Subarray
Utf-8 Not Working in HTML Forms
Opposite of Nl2Br? Is It Str_Replace
How to Avoid "Using Temporary" in Many-To-Many Queries
Getting the Difference Between Two Time/Dates Using PHP
Where Clause in Insert Statement Using MySQL/Php
Loop Through All Months in a Date Range
How to Convert Array of Bytes to a String in PHP
Distance Calculations in MySQL Queries
Mysql_Fetch_Array Returns Duplicate Data
Php, Merging Arrays with Common Keys
Data Transfer from JavaScript to PHP
MySQL & PHP Parameter 1 as Resource
How to Properly Create HTML Links in PHP