mysqli: select rows, update if >0 else insert

[wright67uk]

I'm trying to update a record if it exists and insert one if it doesn't.

 

My select returns -1 results.

And my database is inserting new records opposed to updating the current ones.

 

Can anyone tell me where I'm going wrong please? 

<?php

$id = ''; $name = "ed"; $year = 2013; $month = "may";  $wins = 48;

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());exit();}

$stmt = $mysqli->prepare("SELECT * FROM compare WHERE name = ? AND month = ? AND year = ?");
$stmt->bind_param('ssi', $name, $month, $year);
printf("Affected rows (SELECT): %d\n", $mysqli->affected_rows);

if ($mysqli->affected_rows > 0) {	
$stmt = $mysqli->prepare("UPDATE compare SET wins = ? WHERE name = ? AND month = ? AND year = ?"); 
$stmt->bind_param('isii', $wins, $name, $month, $year);
$stmt->execute();
$stmt->close();  }

else  {
$stmt = $mysqli->prepare("INSERT INTO compare VALUES (?, ?, ?, ?, ?)"); 
$stmt->bind_param('isisi', $id, $name, $year, $month, $wins);
$stmt->execute();
$stmt->close();  }
?>

[PravinS]

Use

 

"$stmt->affected_rows" or "$stmt->num_rows"

 

instead of

 

"$mysqli->affected_rows"

[awjudd]

Change your original query to grab a COUNT of the rows instead of the actual rows. Then if that count is > 0, then you do your update.

<?php

$id = ''; $name = "ed"; $year = 2013; $month = "may"; $wins = 48;

if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());exit();}

$stmt = $mysqli->prepare("SELECT COUNT(name) AS rowcount FROM compare WHERE name = ? AND month = ? AND year = ?");
$stmt->bind_param('ssi', $name, $month, $year);
$stmt->execute();

$count=$stmt->get_result();
printf("Affected rows (SELECT): %d\n", $count['rowcount']);

if ($count['rowcount'] > 0) {    
$stmt = $mysqli->prepare("UPDATE compare SET wins = ? WHERE name = ? AND month = ? AND year = ?");
$stmt->bind_param('isii', $wins, $name, $month, $year);
$stmt->execute();
$stmt->close(); }

else {
$stmt = $mysqli->prepare("INSERT INTO compare VALUES (?, ?, ?, ?, ?)");
$stmt->bind_param('isisi', $id, $name, $year, $month, $wins);
$stmt->execute();
$stmt->close(); }
?>

Or you could use MySQL's built-in support for doing just that:

 

http://www.kavoir.com/2009/05/mysql-insert-if-doesnt-exist-otherwise-update-the-existing-row.html

 

~awjudd

Edited April 12, 2013 by awjudd

[wright67uk]

Thank you for the reply. 

 

I now have:   Affected rows (SELECT): 0

 

However in my database I have duplicate rows with the data -> $name = "ed"; $year = 2013; $month = "may"; $wins = 48;

 

Im expecting affected rows to read : 4

 

 

 

 

 

[mac_gyver]

 

I'm trying to update a record if it exists and insert one if it doesn't.

 

 

there's a query that does that - http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html

[mac_gyver]

as a continuation of the above reply, the link i posted is the same information as the link awjudd posted in his last reply. using this method will shorted your code from about 13 lines to 3 lines.

 

your original code needed to do what pbs posted, but in order to use $stmt->num_rows you need to store the result using $stmt->store_result() first.

 

the $stmt->get_result() in the code posted by awjudd returns a mysqli_result that you need to fetch the row from before you can access the count value.