CODEWITHSUNDEEP
phpmysqlpdo
Sebastien
Sebastien

Reputation: 1328

PHP PDO finding found rows of a UPDATE

I have a small problem that I need help solving. Its more a lack in my MySQL knowledge then a problem really...

I have a table of employes something like this:

CREATE TABLE employes (CoNo int, EmplCode int, Name varchar(250), Dep varchar(4))

It contains some data as such:

3 123 'John Jackson' 'INFO'
3 124 'Simon Says'   'SELL'
...

I am trying to update Simons row using PHP PDO (something like):

$sql = "UPDATE employes SET CoNo = 3, Name = 'Simon Says', Dep = 'SELL' WHERE EmplCode = 124";
$stmt = $PDOObject->prepare($sql);
$status = $stmt->execute();
echo $stmt->rowCount();

Now lets say everything is connecting and everything is working. If you paid attention during your MySQL classes you will soon see that this will echo 0 rows affected.

Which apparently is a normal behavior for MySQL because the UPDATE didn't change anything to the actual data in the table.

But what I need is a function just like rowCount() that will give me the FOUND rows not the AFFECTED rows or something that will let me force the update on the MySQL table.

I have looked and googled the thing but I can't find anything and I am worried that it might be because it doesn't exist.

I have heard of something called FLAG_FOUND_ROWS but I am not sure it is a PDO flag or how to use it (and I cant find anything on it).

What I was hoping to achieve with this is something like a REPLACE statement or an UPSERT without the need of a primary key.

If UPDATE return 0 rows then INSERT the row in the table.

EDIT

Sorry for the mistake I forgot the WHERE clause in the query. As pointed out in the comments.

EDIT

I am still doing research on google for this and found a MySQL function called FOUND_ROWS() but It seems it need to be use in a separate query which doesn't really help since I could just do a SELECT beforehand. But this might give you guys an idea that I haven't think of yet.

Thank you for all the help guys (and girls).

Upvotes: 2

Views: 1568

Answers (1)

Thiago França
Thiago França

Reputation: 1847

It seems that PDO::MYSQL_ATTR_FOUND_ROWS is a mysql connection option. It works only as PDO connection option as well, like

$PDOObject = new PDO($dsn,DB_USER,DB_PASS, array(PDO::MYSQL_ATTR_FOUND_ROWS => TRUE));

after this, rowCount returns the number of found rows, not affected.

echo $stmt->rowCount();

Upvotes: 4

Related Questions