CODEWITHSUNDEEP
mysqlsql
arvind
arvind

Reputation: 157

mysql update query check whether data updated or matches

UPDATE cab_details SET travel_company_person_name='$user_role', driver_name='$driver_name' , driver_phone_no='$driver_phone' , vehicle_name='$vehicle_name' , vehicle_no='$vehicle_no' , starting_kms = '$starting_kms' , region='$region', cd_comments='$comments' WHERE travel_id='$id' AND ((travel_id NOT IN (SELECT cancel_customer.ctravel_id FROM cancel_customer WHERE ctravel_id='$id'))   AND travel_id NOT IN (SELECT tcc_travel_id FROM travel_company_cancel WHERE tcc_travel_id='$id' AND tcc_travel_cancel_company='$travel_company'))

In the above sql query, the mysql_affected_rows() returns 0 when the data matches the existing one and also when the conditions doesn't satisfy. I want to alert the user as "No change in data" or "Condition not satisfied".

How can I do that?

Upvotes: 0

Views: 645

Answers (1)

mseifert
mseifert

Reputation: 5670

From the documentation:

When using UPDATE, MySQL will not update columns where the new value is the same as the old value. This creates the possibility 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.

Since you can't differentiate based on the return value of mysql_affected_rows(), you can run a query to test if the record exists first for the WHERE condition of the query. 

SELECT count(*) as count FROM cab_details
WHERE travel_id='$id' AND 
((travel_id NOT IN (SELECT cancel_customer.ctravel_id FROM cancel_customer WHERE ctravel_id='$id')) AND 
travel_id NOT IN (SELECT tcc_travel_id FROM travel_company_cancel WHERE tcc_travel_id='$id' AND tcc_travel_cancel_company='$travel_company'))

If the number of rows returned is 0, return "Condition not satisfied"

If the number of rows > 0, do the update and test for mysql_affected_rows() which if =0, then return "No change in data"

Upvotes: 3

Related Questions