Get the number of Updated rows

[josoroma]

Hi!

Im trying to get the best approach from joomla database class, for example:

----------------------------------------------------------------------------------------------------------------

$database->setQuery( "UPDATE #__some_table SET"
. "\n column_a='1',"
. "\n column_b='1',"
. "\n column_c='1',"
. "\n column_d='1'"
. "\n WHERE id = '" . intval($row->some_id) . "'"
);

----------------------------------------------------------------------------------------------------------------

I know that "if (!$database->query()) {" tells me if something goes wrong with the
database. But how i get the count or quantity of the updated rows?

----------------------------------------------------------------------------------------------------------------

Thanx in advanced.

[josoroma]

Ok, i did my homework!

Which of these functions i will have to use after an delete or update?
  $database->getAffectedRows()
  $database->getNumRows()

an usage example please.

Thanx.

[josoroma]

Is this a correct usage?

if( !$result = $database->query() ) {
die( $database->stderr( true ) );
}
if ( $database->getAffectedRows($result) <= 0 ) {
break;
}

[josoroma]

Ok!

After some debugging and some testing i can answer my own question:

-------------------------------------------------------------------------------------------------------------------------------

$database->setQuery( "UPDATE #__some_table SET"
. "\n some_column_a='-5',"
. "\n some_column_b='1',"
. "\n some_column_c='1',"
. "\n some_column_d='1'"
. "\n WHERE id = '" . intval($id) . "'"
);

if( !$result = $database->query() ) {

/* Do something with $database->stderr() */

}

$affectedRows = $database->getAffectedRows($result);
if ( intval($affectedRows) <= 0 ) {

/* Not successful case */

}
else {

/* Successful case */

}

-------------------------------------------------------------------------------------------------------------------------------

TIP:
If table has a row whith such ID and the values UPDATED are equal to each row value
then the UPDATE is not commited (Not successful case).

-------------------------------------------------------------------------------------------------------------------------------

Enjoy!

[stAn99]

if you'd like to use insert if the affected rows is uqual to 0 you have to take into consideration this example:

product_price_id (primary/unique) product_id (int) product_price
1 1 2

update table set product_price = '2' where product_id = '1'

this returns number of affected rows = 0 because no row is actually updated as it has the same values BUT to find out if the row really DOES NOT EXISTS you can do the following:

update table set product_price = '2' where product_id = '1' and @product_id := product_id

// joomla:
$db->setQuery('select @product_id');
$product_id_updated = $db->loadResult();
if (empty($product_id_updated))
{
// do the insert....
}

i am running mysql 5.0.x and joomla 1.5.x