What is the best way to: check if an entry exists, if it does not create it, if it updates it?

advertisements

This question already has an answer here:

  • mysqli_fetch_array()/mysqli_fetch_assoc()/mysqli_fetch_row() expects parameter 1 to be resource or mysqli_result, boolean given 33 answers

I'm implementing a tag system on my website. When inserting a new post, I want to check if a tag exists in the tags table. The purpose of this table is to keep track of how many times a tag is used.

If it exists, then update the tag count = count + 1.

If it doesn't exist then create it.

The tags table has two columns: tag and count.

The $tags_arr array would be like ['hello', 'tag', 'world']

I thought the following would do the trick:

foreach ($tags_arr as $tag) {

    $sql_check = "SELECT * FROM tags WHERE tag='" . $tag . "'";
    $result_check = mysqli_query($con,$sql);

    // If we have one result assume a row for the tag already exists
    if (mysqli_num_rows($result_check) == 1) {
        $sql_tag = "UPDATE tags SET count = count + 1 WHERE tag='" . $tag . "'";
        $result_tag = mysqli_query($con,$sql_tag);
        if (mysqli_num_rows($result_tag) == 1) echo "updated tag " . $tag . "<br />";
        else echo "failed update tag " . $tag . "<br />";
    }

    // If not create the tag
    else {
        $sql_tag = "INSERT INTO tags (tag, count) VALUES ('" . $tag . "', 1)";
        $result_tag = mysqli_query($con,$sql);
        if (mysqli_num_rows($result_tag) == 1) echo "created tag " . $tag . "<br />";
        else echo "failed create tag " . $tag . "<br />";
    }
}

I get this on every iteration of the loop:

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in /home/rlcoachi/public_html/hat/admin/insertPost.php on line 53

Is mysqli_num_rows not a good way to check if the query returned a result?

I tried just checking:

if ($result_check)

assuming that it would just be false if there was no record, but that create another set of problems.

What is the best way to: check if an entry exists, if it doesn't create it, if it does update it?


What is the best way to: check if an entry exists, if it doesn't create it, if it does update it?

Just set a unique key constraint and use INSERT INTO ... ON DUPLICATE KEY UPDATE....

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

But please note, that this will cause id-gaps, because every update will also increment the auto-increment value by one - even if there was no insert.