MySQL Functions
Here's a little function I've been using for a while now, pass it two parameters (action command (1 or 0 see notes)) and a sql statement.
It returns a simple line which shows the length of time taken to action the query, the status of the query (0= query not actioned, you can set this value for testing, 1=success qry executed successfully, -1= failed, there was a problem with the sql statement) the number of lines affected by that query and the sql statement itself.
I've found this invaluable when trying to tie down large amounts of updates to a table, using this you can easily see where a query was successfully executed and the number of rows are affected, or where there are problems and a statement has failed for example.
function dosql($action,$sql){
# assuming you have setup a link to your database entitled $link
# action = 1 run this query
# action = 0 don't run, just return sql statement
$start = getmtime();
if($action==1){
$result = mysql_query($sql);
$affectedrows = "[".mysql_affected_rows($link)."]";
}
return "[".number_format((getmtime()-$start),3)."][$action]: $sql\n";
mysql_free_result($result);
}
Example output:
[0.072][1][80]: UPDATE MYTABLE SET FIELD = 1;
[0.106][1][758]: UPDATE ANOTHERTABLE SET FIELD = 2;
[0.006][-1][0]: UPDATER ANOTHERTABLE SET FIELD = 2;
The output shows:
[Timetaken][result]][lines affected]
The result will be either -1, 0 or 1, -1 means there's a problem with the sql statement, 1 means it executed correctly, 0 means it wasn't executed.
mysql_affected_rows
(PHP 4, PHP 5, PECL mysql:1.0)
mysql_affected_rows — Devuelve el número de filas afectadas de la última operación MySQL
Descripción
int mysql_affected_rows
([ int $
identificador_de_enlace
] )
mysql_affected_rows() devuelve el número de filas afectadas en la ultima sentencia INSERT, UPDATE o DELETE sobre el servidor asociado con el identificador_de_enlace especificado. Si el identificador de enlace no ha sido especificado, se asume por defecto el último enlace.
Note: Si está usando transacciones, necesitará llamar mysql_affected_rows() después del INSERT, UPDATE, o DELETE, no después del commit.
Si la última sentencia fue un DELETE sin clausula WHERE, todos los registros han sido borrados de la tabla pero esta función devolvera cero.
Note: Cuando se usa UPDATE, MySQL no actualizará las columnas donde el nuevo valor es el mismo al actual. Esto crea la posibilidad de que mysql_affected_rows() pueda no ser igual al número de filas encontradas, solo el número de filas que fueron literalmente afectadas por la sentencia.
La sentencia REPLACE primero borra el registro con la misma llave principal y entonces inserta el nuevo registro. Esta función regresa el número de registros borrados más el número de registros insertados.
Para obtener el número de fils regresadas por un SELECT, es posible usar también mysql_num_rows().
Si la última consulta falló, esta función regresará -1.
Example #1 Delete-Query
<?php
/* connect to database */
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db('mydb');
/* this should return the correct numbers of deleted records */
mysql_query('DELETE FROM mytable WHERE id < 10');
printf("Records deleted: %d\n", mysql_affected_rows());
/* with a where clause that is never true, it should return 0 */
mysql_query('DELETE FROM mytable WHERE 0');
printf("Records deleted: %d\n", mysql_affected_rows());
?>
El ejemplo anterior prducirá la siguiente salida:
Records deleted: 10 Records deleted: 0
Example #2 Update-Query
<?php
/* connect to database */
mysql_connect("localhost", "mysql_user", "mysql_password") or
die("Could not connect: " . mysql_error());
mysql_select_db("mydb");
/* Update records */
mysql_query("UPDATE mytable SET used=1 WHERE id < 10");
printf ("Updated records: %d\n", mysql_affected_rows());
mysql_query("COMMIT");
?>
El ejemplo anterior prducirá la siguiente salida:
Updated Records: 10
Vea también mysql_num_rows(), mysql_info().
add a note
User Contributed Notesmysql_affected_rows
sean at adtools dot co dot uk
09-Sep-2008 01:48
09-Sep-2008 01:48
root at it dot dk
11-Apr-2008 09:06
11-Apr-2008 09:06
I liked the idea of using transactions as written by EToS, however the table/database must support transactions.
I tried this on a MyISAM table, which didn't yield any errors, but just inserted my query.
Works great on InnoDB tables.
EToS
09-Aug-2007 01:57
09-Aug-2007 01:57
i found a pretty nice way, this db class/function will accept an array of arrays of querys, it will auto check every line for affected rows in db, if one is 0 it will rollback and return false, else it will commit and return true, the call to the function is simple and is easy to read etc
----------
class MySQLDB
{
private $connection; // The MySQL database connection
/* Class constructor */
function MySQLDB(){
/* Make connection to database */
$this->connection = mysql_connect(DB_SERVER, DB_USER, DB_PASS) or die(mysql_error());
mysql_select_db(DB_NAME, $this->connection) or die(mysql_error());
}
/* Transactions functions */
function begin(){
$null = mysql_query("START TRANSACTION", $this->connection);
return mysql_query("BEGIN", $this->connection);
}
function commit(){
return mysql_query("COMMIT", $this->connection);
}
function rollback(){
return mysql_query("ROLLBACK", $this->connection);
}
function transaction($q_array){
$retval = 1;
$this->begin();
foreach($q_array as $qa){
$result = mysql_query($qa['query'], $this->connection);
if(mysql_affected_rows() == 0){ $retval = 0; }
}
if($retval == 0){
$this->rollback();
return false;
}else{
$this->commit();
return true;
}
}
};
/* Create database connection object */
$database = new MySQLDB;
// then from anywhere else simply put the transaction queries in an array or arrays like this:
function function(){
global $database;
$q = array (
array("query" => "UPDATE table WHERE something = 'something'"),
array("query" => "UPDATE table WHERE something_else = 'something_else'"),
array("query" => "DELETE FROM table WHERE something_else2 = 'something_else2'"),
);
$database->transaction($q);
}
HMax
02-Jul-2007 03:21
02-Jul-2007 03:21
If you use "INSERT INTO ... ON DUPLICATE KEY UPDATE" syntax, mysql_affected_rows() will return you 2 if the UPDATE was made (just as it does with the "REPLACE INTO" syntax) and 1 if the INSERT was.
So if you use one SQL request to insert several rows at a time, and some are inserted, some are just updated, you won't get the real count.
dobrys at abv dot bg
28-May-2007 02:35
28-May-2007 02:35
I see that when try to use mysql_affected_rows() with "mysql_pconnect(...)" without link indetifier as param in "mysql_affected_rows()" the result is allways -1.
When use link identifier "mysql_affected_rows($this_sql_connection)" - everything is Fine. This is is on PHP Version 5.2.0
Hope that this was helpfull for somebody
mlugassy at 2find dot co dot il
07-Oct-2005 06:22
07-Oct-2005 06:22
To solve the affectedRows() issue on MySQL using PEAR::DB, simply add a 'client_flags' key with a value of 2 to your $dsn options:
$dsn = array(
'phptype' => 'mysql',
'client_flags' => 2,
'username' => 'someuser',
'password' => 'apasswd',
'hostspec' => 'localhost',
'database' => 'thedb',
);
temp02 at flexis dot com dot br
29-Jun-2005 05:39
29-Jun-2005 05:39
SCENARIO
1. You're using MySQL 4.1x with foreign keys.
2. You have table t2 linked to table t1 by a CASCADE ON DELETE foreign key.
3. t2 has a UNIQUE key so that duplicate records are unacceptable.
3. You have a REPLACE query on t1 followed by an INSERT query on t2 and expect the second query to fail if there's an attempted insert of a duplicate record.
PROBLEM
You notice that the second query is not failing as you had expected even though the record being inserted is an exact duplicate of a record previously inserted.
CAUSE
When the first query (the REPLACE query) deletes a record from t1 in the first stage of the REPLACE operation, it cascades the delete to the record that would be duplicated in t2. The second query then does not fail because the "duplicate" record is no longer a duplicate, as the original one has just been deleted.
jeroen_vde at hotmail dot com
12-Mar-2005 05:22
12-Mar-2005 05:22
If you want to delete all in table, and get number of affected rows back use a delete statment of this form:
mysql> DELETE FROM table_name WHERE 1>0;
Note that this is much slower than DELETE FROM table_name with no WHERE clause, because it deletes rows one at a time.
Source: MySQL Reference Manual "Delete Syntax"
steffen at showsource dot dk
28-Sep-2004 03:20
28-Sep-2004 03:20
Using OPTIMIZE TABLE will also return true.
So, if you want to check the numbers of deleted records, use mysql_affected_rows() before OPTIMIZE TABLE
sam_dot_fullman_at_verizon.nt
19-May-2004 12:12
19-May-2004 12:12
Just a MySQL note, using REPLACE INTO on a record could return either ONE or TWO affected rows with this function. Basically, REPLACE INTO will insert a record if no combination of unique keys is matched, or delete the existing record if the new record matches one or more unique keys, then insert the new record over it.
This is actually helpful if you want to know if a record was already in there. (Affected rows=1 means there wasn't already a record there, Affected rows=2 means there was), but if you don't know how REPLACE INTO works it could confuse you. I didn't see this in the notes above and hope it helps someone out.
deponti A_T tiscalinet D0T it
07-Nov-2003 04:52
07-Nov-2003 04:52
It works also for REPLACE query,returning:
0 if the record it's already updated (0 record modified),
1 if the record it's new (1 record inserted),
2 if the record it's updated (2 operations: 1 deletion+ 1 insertion)
Richard dot Johnson2 at student dot gu dot edu dot au
25-Oct-2003 03:56
25-Oct-2003 03:56
RE: sng2nara's comment
I think the expression should read:
"/^[^0-9]+([0-9]+)[^0-9]+([0-9]+)[^0-9]+([0-9]+).*$/"
as there is nothing usually returned after the number of warnings. The previous expression required at least 1 non-digit character after the number of warnings.
sng2nara
02-Oct-2003 12:41
02-Oct-2003 12:41
You can use following code for choosing update or insert.
mysql_query($update_sql);
preg_match(
"/^[^0-9]+([0-9]+)[^0-9]+([0-9]+)[^0-9]+([0-9]+)[^0-9]+$/",
mysql_info(),
$arr);
if( $arr[1] == 0 ) // a number of matched rows is 0
{...do insert query...}
else
{...nothing...}
raistlin at oracolo dot com
03-Jul-2003 09:38
03-Jul-2003 09:38
| If you need to know the actual count of rows,
| even if some update changed nothing add a field
| count int(11) and add | one on every update, like in:
|
| mysql_query("update table set count=count+1, ...");
| if (mysql_affected_rows()==0) {
| mysql_query("insert into table ...");
| }
Better: add a field `flag` tinyint(1) unsigned default '0'
on every update: "update table set flag=1-flag, ..."
anthonyd at rhsonline dot net
02-Jul-2003 04:08
02-Jul-2003 04:08
My little work around for the update problem (that I ran into):
if (mysql_affected_rows()==0) {
$q = "select .. WHERE ...<search for what you updated>'";
if (mysql_num_rows(mysql_query($q))==0)
die("0 rows affected");
else
echo "Record Alredy Existed";
}
It is probably not the best way, as I sometimes run 2 queries for the same thing, but it gets the job done.
phpweb at eden2 dot com
27-Jun-2003 11:47
27-Jun-2003 11:47
"Note: When using UPDATE, MySQL will not update columns where the new value is the same as the old value. This creates the possiblity that mysql_affected_rows() may not actually equal the number of rows matched, only the number of rows that were literally affected by the query."
As of PHP 4.3.0 (I assume, I only tried with 4.3.2), you can make mysql_affected_rows() return the number of rows matched, even if none are updated.
You do this by setting the CLIENT_FOUND_ROWS flag in mysql_connect(). For some reason, not all the flags are defined in PHP, but you can use the decimal equivalent, which for CLIENT_FOUND_ROWS is 2.
So, for example:
$db= mysql_connect("localhost", "user", "pass", false, 2);
mysql_select_db("mydb", $db);
$query= "UPDATE ...";
mysql_query($query);
print mysql_affected_rows(); // more than 0
mysql_query($query); // same query twice
print mysql_affected_rows(); // still more than 0
gabriel dot preda at amr dot ro
18-Jun-2003 08:35
18-Jun-2003 08:35
-1-----------------------------
$w = "insert into...";
$q = mysql_query($w) or die('mortua est'); // 1433 times
print mysql_affected_rows($cnex); // prints 1433
-2-----------------------------
$w = "insert into...";
$q = mysql_query($w) or die('mortua est'); // 1433 times
@mysql_query("SELECT a FROM b LIMIT x"); //
print mysql_affected_rows($cnex); // prints x
It appears that php's "mysql_affected_rows" calls mysql's "mysql_affected_rows"... witch says:
"For SELECT statements, mysql_affected_rows() works like mysql_num_rows().
mysql_affected_rows() is currently implemented as a macro."
adam at NOSPAM dot example dot com
17-Jan-2003 11:34
17-Jan-2003 11:34
using PHP/MySql -There is an alternate way to get the rows returned -
for non-critical use i suppose--due to its oversimplified approach.
You can simply intialize a variable to the integer value of 0, and then, inside of the "while" loop that returns the rows
of your query, you increment that variable and then print out the final value of that variable outside of that "while" loop.
Or, you could print the variable within each iteration if you want to give each row returned [ as in a chart where you
need to provide line numbers ]
//code:
$sql = "SELECT * FROM YourTable
ORDER BY Last_Name";
$result = @mysql_query($sql, $connection) or die("Could not execute query.");
$i = 0; // we will use variable $i as a returned row counter.
while ($row = mysql_fetch_array($result)) {
$First_Name = $row['First_Name'];
$Last_Name = $row['Last_Name'];
$num_rows = mysql_num_rows($result);
$i++;
//To show the rows number by number
echo "Member no. $i $First_Name $Last_Name < br >";
// the < br > is an actual HTML line break
// which is not allowed on posting these notes,
// to use it, close up the spaces between the < and the >
// above echo statement will produce the results below if 3 rows were returned:
// Member no. 1 John Jones
// Member no. 2 Mary Smith
// Member no. 3 Nancy Steffan
}
echo "$i Rows Returned from query.";
// above statement will produce:
// 3 Rows Returned from query.
Hope this helps-- again,
this is a simplified approach.
take care;
<?adam?>
spam123 at cobsen dot biz
08-Dec-2002 05:04
08-Dec-2002 05:04
If you need to know the actual count of rows, even if some update changed nothing add a field count int(11) and add one on every update, like in:
mysql_query("update table set count=count+1, ...");
if (mysql_affected_rows()==0) {
mysql_query("insert into table ...");
}
ben-xo at NOSPAMdubplatesNOSPAM dot org
21-Apr-2002 07:30
21-Apr-2002 07:30
mysql_affected_rows() reports on the number of rows affected by an in-place operation on the database, but mysql_num_rows() returns the number of rows in a MySQL record set (which is held by PHP after MySQL has generated it). This means that if you can do
$a = mysql_query("SELECT ...");
$b = mysql_query("SELECT ...");
if (mysql_unm_rows($a) > mysql_num_rows($b)) print "a is larger";
else print "b is larger";
... but this does not make sense for the operations supported by mysql_affected_rows(), which reports on the status of the database connection as a whole.
Particularly note this:
$query = "UPDATE ...";
mysql_query($query);
print mysql_affected_rows(); // more than 0
mysql_query($query); // same query twice
print mysql_affected_rows(); // 0.
.. this is because the 2nd time you execute the identical query, all the rows are already updated so no rows are affected the 2nd time.
I hope this clears up why mysql_num_rows() and mysql_affected_rows() are fundamentally different
dfylstra at frontsys dot com
13-Aug-2001 12:06
13-Aug-2001 12:06
mysql_affected_rows() also reports the number of rows changed by the LOAD DATA command. If you use the IGNORE option in LOAD DATA and you know the number of rows in the input file, you can use mysql_affected_rows() to determine the number of rows that were ignored.
peter at petermoulding dot NO_SPAM dot com
05-Aug-2001 11:34
05-Aug-2001 11:34
mysql_affected_rows() fails with some automatic updates. An example from the PHP Black Book is that of session records updated by automatic timestamps. When you maintain the session valid time via a timestamp and use some databases including MySQL, then update the session record, the automatic timestamp update will not count as an update in mysql_affected_rows(). You have to manually update the timestamp field. In MySQL that is achieved by setting the field to nulls. If your time field is named updated, you have to include
set updated = null
Other databases require appropriate tricks and you will have to test stored procedures in your database to see if their updates count in mysql_affected_rows().