[SOLVED] $mysqli->affected_rows help needed

[B34ST]

Hi I am trying to update a table if an entry already exists or insert a new entry if it does not. I have the following:

 

$sql = $conn->query("UPDATE rs_stats 
	     SET rs_rank='$stats[0]', level='$stats[1]', experience='$stats[2]'
	     WHERE username='$user'
	     AND skill='$skill[$i]'"); 
if ($conn->affected_rows == 0) {
$sql = $conn->query("INSERT INTO rs_stats (username, skill, rs_rank, level, experience)
  			     VALUES('$user', '$skill[$i]', '$stats[0]', '$stats[1]', '$stats[2]')"); 
}

 

The above works fine unless the data stays the same. I think this is because $mysqli->affected_rows only returns a 1 if the row was actually changed but if the row exists and all data is the same it returns a 0.

 

I would like to know how to find out how many matches there where instead?

 

If i use mysqli->info it returns this and i could use preg_match to see if it a 1 or a 0 but this seems to be a long way of doing it. any ideas?

 

any help appreciated

 

Thanks

[soak]

You could use INSERT / ON DUPLICATE KEY UPDATE

 

 

[B34ST]

I tried using on duplicate key but again it does not achieve what i need as it only works on a primary or unique key. I cannot use the primary key as this an auto_increment value. and I cannot set the field to be unique because there will be multiple entries the same for example for each user there will be 29 different skills so i must use both username and skill in the mysql WHERE statement. here is an example of the table:

 

id | username | skill | rank | level |experience

1 | user1 | skill1 | 1 | 99 | 200000

2 | user1 | skill2 | 3 | 93 | 100000

3 | user2 | skill1 | 5 | 94 | 50000

4 | user2 | skill2 | 7 | 98 | 20000

[soak]

Just put a unique index on username AND skill. That way your data is protected at the DB level too.

 

Or you could cheat by adding a lastupdated column to the table so that the table is always updated and your original example works.

[B34ST]

The table will not let me put a unique key on username and skill because the username should be inserted on 29 rows. however adding a last_updated column isnt a bad idea, this could help with identifying non active members. thanks for the help.

 

I will leave this topic as unsolved for now although this "cheat" will solve my problem it would still be nice to know if there is another way around this.

[soak]

Not sure if you've tried but you can add one unique index to two columns at once, not username and skill separately:

 

ALTER TABLE `rs_stats` ADD UNIQUE (

`username` ,

`skill`

)

[B34ST]

Ah now I see, No I did not know that I could add two unique keys at the same time. When I tried to do them seperately I was given an error but now trying it your way has worked and now so does the on duplicate key statement.

 

Thanks for all the help.