Making a MySQL database relational and updating existing PHP code

Question

I have a MySQL database called csftdb and in that database there exists 1 table: articles.

I also have a series of PHP forms that can add, edit, and delete entries to/from the table within the database.

Now, however, I'm attempting to add search functionality and have found that the single-table method is insufficient.

The table is laid out thus:

id - articletitle - articleorganization - articledate - articleurl - articletags

One of the PHP forms is a search form and it works except when the tags come into play, for example:

Say there are 3 articles in the database:

01 - My first article - Article Corp. - 05/28/2010 - www.articlecorp.com/article1 - php, html
02 - My second article - Article Corp. - 08/11/2012 - www.articlecorp.com/article2 - asp, html
03 - My third article - Article Corp. - 12/22/2011 - www.articlecorp.com/article3 - c++, html

And then I have a search form with fields for Title, Organization, Date, URL, and Tag(s). I am able to search "My first article" in the Title field and only retrieve the 1st entry, but I can also search "article" in the Title field and it would retrieve all 3 entries. This is the desired behavior. Further, I can search "article" in the Title field and "Article Corp." in the Organization field and it would only show entries that match that set (so, if there were other organizations that also had the word "article" in their article titles, those organizations would not show up in the results.) This is also desired behavior.

However, if I were to search "Article" in the title field, and then "php, asp, c++" in the Tag(s) field, my search would return no results. This is not the desired behavior. In this case, the "tags" are supposed to allow a more fine level of granularity with the search results.

So I could conceivably have a thousand articles, all with the exact same Title etc. information but with different combinations of tags and I could search for the tags and retrieve all of the articles with just those tags applied.

To that end, I have created 2 new tables in my database and altered the original one such that my tables are now:

articles:
id | articletitle | articleorganization | articledate | articleurl


tags:
id | tags

and

articles_tags:
article_id | tag_id

I also have the following PHP form which is modified from the original form:

        <?php
     function renderForm($articletitle, $articleorganization, $articledate, $articleurl, $articletags )
     {
     ?>
    . . .
      <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="articles.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 $articles.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="articles.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 $articles.articleurl; ?>" size="50"/>
                </span></td>
              </tr>
              <tr>
                <td align="right"><span class="field">Article Tags:</span></td>
                <td align="left"><span class="field">
                <input name="articletags" type="text" value="<?php echo $tags.articletags; ?>" size="50"/>
                </span></td>
              </tr>
            </table>
            <footer><input type="submit" name="submit" value="Add this Article"></footer>
            </form>
        </div>
        . . .
    </body>
    </html>
    <?php 
     }

     . . .
      if($_SERVER['REQUEST_METHOD'] == 'POST')
      { 
        // get form data, making sure it is valid
        $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']));

     . . .

     mysql_query("INSERT articles SET articletitle='$articletitle', articleorganization='$articleorganization', articledate='$articledate', articleurl='$articleurl', articletags='$tags.articletags' ")
     or die(mysql_error()); 


       header("Location:addsuccess.php"); 
      }
     }
     else

     {
      renderForm('','','','','');
     }
    ?>

However I can't seem to get the information to store into my 2nd table and, thus, I can't get my relations to work.

I've been looking at this all day and can not figure it out.

Answer 1

You'll need to add a couple more INSERT queries to get this to work properly.

if (mysql_query ("INSERT INTO `articles` SET
  `articletitle` = '$articletitle',
  `articleorganization` = '$articleorganization',
  `articledate` = '$articledate',
  `articleurl` = '$articleurl'")) {

  $article_id = mysql_insert_id();
}

$tags = split (' ', $_POST['articletags']);
foreach ($tags as $tag) {
  // check if tag exists in tag table and get id, if not, insert it and get mysql_insert_id()
  // then insert article_id with tag_ids into your articles_tags table
}

This is rough but its the idea. Also, if I recall correctly "split" is depreciated so you'll want to find another option.

Answer 2

For each submitted tag you need to look if it exists or not, if it does not exists, you need to add it and then you store all the relations.

You should also compare with the original values if one or more tags have been removed and then check each removed tag if it was the only used variant of that tag so you can delete it from the tags and relations table.