Jump to content

mysqli: select rows, update if >0 else insert


wright67uk
 Share

Recommended Posts

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();  }
?>
Link to comment
Share on other sites

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 by awjudd
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.