Mysql, Check if row exists, if it does grab value, if not insert

Question

I have two tables, one is 'tags' that stores "tid" (auto increment) and "name" (name of tag). The second table "tags_data" stores the data on each tag. This table has the fields "tid" (from the first table) and a few others.

This is so people can tag content on my website. When I tag content I want to first check if that tag already exists in the first table. If it doesnt exist then we insert the tag into the DB and use the tid to insert into the second table. I have this part working so far.

The problem is when the tag already exists in the DB, I want to grab the existing tid and use it in my second query.

This is the code I have so far:

    // check if tag already exists
$tagexists = "SELECT COUNT(*) as cnt FROM tags WHERE 'name' = '$usetag' LIMIT 1";
$results = mysql_query($tagexists) or die('Invalid query: ' . mysql_error());
if ($results['cnt'] == 0) {
    // tag is not yet in DB
    $tagquery1 = "INSERT INTO tags (name) VALUES ('$usetag')";
    $result = mysql_query($tagquery1) or die('Invalid query: ' . mysql_error());
    $lastid = mysql_insert_id();
    $tagquery2 = "INSERT INTO tags_data (tid, nid, uid) VALUES ($lastid, $nid2, $uid)";
    $result = mysql_query($tagquery2) or die('Invalid query: ' . mysql_error());
} else {
    // tag is already in DB, grab the tid
    $grabtid = "SELECT tid FROM tags WHERE 'name' = '$usetag' LIMIT 1";
    $results = mysql_query($grabtid) or die('Invalid query: ' . mysql_error());
    $row = mysql_fetch_array($results);
    $lastid = $row['tid'];
    $tagquery2 = "INSERT INTO tags_data (tid, nid, uid) VALUES ($lastid, $nid2, $uid)";
    $result = mysql_query($tagquery2) or die('Invalid query: ' . mysql_error());        
}

There is something wrong with the way I am checking if a tag exists already. I was following a guide online using COUNT but it doesnt seem to be working properly.

see at this post it will solve this problem Here is link

Sohail Ahmed
– Sohail Ahmed

2012-10-31 17:39:16 +00:00
Commented Oct 31, 2012 at 17:39 — Sohail Ahmed
– Sohail Ahmed, Commented Oct 31, 2012 at 17:39

AndrewR · Accepted Answer · 2012-10-31 17:36:17Z

1

$results['cnt'] == 0 is wrong. You are forgetting to get the result. ($results is a resource id.)

if(mysql_result($results) == 0){

Likewise, your second part of the if that gets the existing data is missing it too. You want to use $data = mysql_fetch_assoc($result); there.

answered Oct 31, 2012 at 17:36

AndrewR

6,7681 gold badge26 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jay Over a year ago

Something else seems to be wrong with my code. Even if a tag already exists it doesnt seem to care, it just inserts it again. Does anything else stand out to you? EDIT: seems the single quotes around 'name' are making the count 0. Removing them fixes it, thanks

AndrewR Over a year ago

There is the single quote around name again in the first query in the second part. Is there any other errors thrown?

Albert Oclarit · Accepted Answer · 2012-10-31 17:38:29Z

1

I think your code is wrong

$tagexists = "SELECT COUNT(*) as cnt FROM tags WHERE 'name' = '$usetag' ";
$res = mysql_query($tagexists) or die('Invalid query: ' . mysql_error());
$results = mysql_fetch_assoc($res);
if ($results['cnt'] == 0) {
    // tag is not yet in DB
    $tagquery1 = "INSERT INTO tags (name) VALUES ('$usetag')";
    $result = mysql_query($tagquery1) or die('Invalid query: ' . mysql_error());
    $lastid = mysql_insert_id();
    $tagquery2 = "INSERT INTO tags_data (tid, nid, uid) VALUES ($lastid, $nid2, $uid)";
    $result = mysql_query($tagquery2) or die('Invalid query: ' . mysql_error());
} else {
    // tag is already in DB, grab the tid
    $grabtid = "SELECT tid FROM tags WHERE 'name' = '$usetag' LIMIT 1";
    $results = mysql_query($grabtid) or die('Invalid query: ' . mysql_error());
    $row = mysql_fetch_array($results);
    $lastid = $row['tid'];
    $tagquery2 = "INSERT INTO tags_data (tid, nid, uid) VALUES ($lastid, $nid2, $uid)";
    $result = mysql_query($tagquery2) or die('Invalid query: ' . mysql_error());        
}

answered Oct 31, 2012 at 17:38

Albert Oclarit

1437 bronze badges

1 Comment

Albert Oclarit Over a year ago

it is recommended to use mysql_fetch_assoc (and its variant)

Collectives™ on Stack Overflow

Mysql, Check if row exists, if it does grab value, if not insert

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related