CODEWITHSUNDEEP
phpmysql
Zrb0529
Zrb0529

Reputation: 800

Setting up a "tag" system for entries in a MySQL database

I have the following PHP form for creating entries in a MySQL database:

<?php
    function renderForm($articletitle, $articleorganization, $articledate, $articleurl, $articletags )
    {
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    . . .
    </head>

    <body>
        <div class="container">
            <div class="header">
            . . .
            </div>
            <div class="sidebar1">
            . . .
            </div>
            <div class="content">
                <div id="stylized" class="myform">
                    <form id="form" name="form" action="" method="post">
                        <h1>Create a new entry in the database</h1>
                        <table width="100%" border="0" cellpadding="6">
                            <tr>
                                <td colspan="2"><legend>Article details</legend></td>
                            </tr>
                            <tr>
                                <td width="20%" align="right"><span class="field">Article Title:</span></td>
                                <td width="80%" align="left"><span class="field">
                                    <input name="articletitle" type="text" value="<?php echo $articletitle; ?>" size="50"/>
                                </span></td>
                            </tr>
                            <tr>
                                <td align="right"><span class="field">Article Author:</span></td>
                                <td align="left"><span class="field">
                                    <input name="articleorganization" type="text" value="<?php echo $articleorganization; ?>" size="50"/>
                                </span></td>
                            </tr>
                            <tr>
                                <td align="right"><span class="field">Access Date:</span></td>
                                <td align="left"><span class="field">
                                    <input name="articledate" type="text" value="MM/DD/YYYY" size="50"/>
                                </span></td>
                            </tr>
                            <tr>
                                <td align="right"><span class="field">Article URL:</span></td>
                                <td align="left"><span class="field">
                                <input name="articleurl" type="text" value="<?php echo $articleurl; ?>" size="50"/>
                                </span></td>
                            </tr>
                            <tr>
                                <td align="right"><span class="field">Article Tags:</span></td>
                                <td align="left"><span class="field">
                                    <input type="checkbox" name="articletags[]" value="geology" id="articletags_0" />
                                    <input type="checkbox" name="articletags[]" value="astronomy" id="articletags_1" />
                                </span></td>
                            </tr>
                        </table>
                        <footer><input type="submit" name="submit" value="Add this Article"></footer>
                    </form>
                </div>
            <div class="footer">
            . . .
            </div>
    </body>
</html>
<?php
    }
    include('settings.php');

    if (count($articletags) > 0)
    {
        $articletags_string = implode(",", $articletags);
    }

    if ($_SERVER['REQUEST_METHOD'] == 'POST')
    {
        $articletitle = mysql_real_escape_string(htmlspecialchars($_POST['articletitle']));
        $articleorganization = mysql_real_escape_string(htmlspecialchars($_POST['articleorganization']));
        $articledate = mysql_real_escape_string(htmlspecialchars($_POST['articledate']));
        $articleurl = mysql_real_escape_string(htmlspecialchars($_POST['articleurl']));
        $articletags = implode(',', $_POST['articletags']);

        if ($articletitle == '' || $articleorganization == '')
        {
            $error = 'ERROR: Please fill in all required fields!';
            renderForm($articletitle, $articleorganization);
        }
        else
        {
            mysql_query("INSERT INTO articles SET articletitle='$articletitle', articleorganization='$articleorganization', articledate='$articledate', articleurl='$articleurl' ");
            mysql_query("INSERT INTO articles_tags SET articletags='$articletags' ")

            or die(mysql_error());

            header("Location:addsuccess.php");
        }
    }
    else
    {
        renderForm('','','','','');
    }
?>

Currently the form will submit the correct information to two tables within the database, articles and articles_tags, but I can't figure out how to make a relation.

The end result is that the checkboxes on a separate form will be editable (that is, if I check a box on the entry page and then go to the edit page, that box will already be checked, and it can then be unchecked and that entry can be updated).

Right now, though, I'm trying to make the relation appear in the second table. The layout of the tables is:

articles:

id | articletitle | articleorganization | articledate | articleurl

articles_tags:

id | article_id | tag_id | articlestags

I think I need to somehow get the article ID and the tag ID and insert them into the second table(?).

Does this require a third table?

Am I even on the right track here?

Upvotes: 0

Views: 398

Answers (1)

Ja͢ck
Ja͢ck

Reputation: 173532

Inline tag storage

Depending on the size of your tags you could store the them inside the article_tags table:

article_id | tag_contents

Add UNIQUE(article_id, tag_contents) to avoid storing duplicate tags per article

Important

This approach has a downside; the tags are not connected to each other, so if changes are made to a tag you have to update the whole article_tags table. Also, if the tags are considerably longer than 4 bytes you're better off with the next solution.

External tag storage

Create another table tags:

id | tag_contents

Add UNIQUE(tag_contents) to avoid storing duplicate tags

Modify `article_tags:

article_id | tag_id

Add UNIQUE(article_id, tag_id) to avoid storing duplicate tags per article

Upvotes: 1

Related Questions