PHP/MySQL - simple tagging system - updating article-tag links?

Question

I want to build a simple tagging system for my news section

DB setup (img borrowed from a previous post)

DB Setup something like

On my "Edit Article" screen, there are now tags sitting in an input field and the user can delete or type new tags. All good.

But when the page is submitted and I get this array of tag values, I'm thinking that I have to check each one against the 'Tag' table to see if it exists or not.

Is there any shortcut way of doing this or is it somehow possible to pass through tag_id=>tag_value? So far none of the preferred jQuery libraries seem to offer that: one, two

ANisus ANisus · Accepted Answer · 2011-05-18T07:12:31

You should always check the submitted data on the server side as well. If you are using MYSQL, you can try this:

$sql = "SELECT a.* FROM (";
$first = true;
foreach( $submitted_tag_array as $v ) {
    $v = mysql_real_escape_string($v);
    $sql .= $first
        ? "\n\tSELECT '$v' AS tag"
        : "\n\tUNION ALL SELECT '$v'";
    $first = false;
}

$sql .= "\n) AS a"
    ."\nLEFT JOIN [tag] t ON t.tag_id = a.tag"
    ."\nWHERE t.tag_id IS NULL";

$result = mysql_query($sql);

if( $row = mysql_fetch_assoc($result) ) {
    // We found atleast one tag that doesn't exist in the database! Do something about it!
}

The above code assumes you get an array of tag_id's in the submit. If you instead get tag names, you just have to change

."\nLEFT JOIN [tag] t ON t.tag_name = a.tag"

PHP/MySQL - simple tagging system - updating article-tag links?

1 Answers