0

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?

Juicy
  • 10,990
  • 33
  • 107
  • 196

5 Answers5

1

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.

dognose
  • 19,568
  • 9
  • 58
  • 104
1

Assuming you have access to the ALTER permission in the db, first create a unique key on the tag column if you don't already have one.

ALTER TABLE tags ADD UNIQUE INDEX `tagname` (`tag`)

Then you can combine all of this into one query:

INSERT INTO tags (tag, count) VALUES ($tag, 1) ON DUPLICATE KEY UPDATE count=count+1

This will insert the row if it the $tag is not in the table, otherwise it will update the count column.

dognose
  • 19,568
  • 9
  • 58
  • 104
islanddave
  • 351
  • 2
  • 10
0

There is more than one issue in your code. First, mysqli_query returns a result set only if there is a successful SELECT, SHOW, DESCRIBE or EXPLAIN; for other successful queries it will return true and for failed queries it will return false.

Your update or insert query failed, and either there is an issue with the query itself, or it's the way you construct the query - use mysqli_real_escape_string to avoid surprises.

ksiimson
  • 583
  • 3
  • 8
0

INSERT INTO tags (tag, count) VALUES ('" . $tag . "', 1) ON DUPLICATE KEY UPDATE count=count + 1

Alex
  • 1,535
  • 2
  • 9
  • 13
0

This is the way I would have done it, though I don't know anything aside from the mysql_* commands so they could probably do with being updated

foreach($tags_arr as $tags) {

    $sql = "SELECT * FROM tags Where tag = '".$tags."'";
    $result = mysql_query($sql);
    $num = mysql_numrows($result);
    if($num<1){
        $query = "INSERT INTO tags (tag, count) VALUES('".$tags."','1')";
        $result = mysql_query($query);
        if (!$result) {
            echo "Failed to insert ".$tag.": ".mysql_error()."<br/>";
        }
    }
    else{
        #use $result to get the count and update database
    }
}
Peter
  • 2,650
  • 2
  • 24
  • 43