Minimal MySQL queries for Blog Tags

Coding Critique is the place to post source code for peer review by other members of DevNetwork. Any kind of code can be posted. Code posted does not have to be limited to PHP. All members are invited to contribute constructive criticism with the goal of improving the code. Posted code should include some background information about it and what areas you specifically would like help with.

Popular code excerpts may be moved to "Code Snippets" by the moderators.

Moderator: General Moderators

Post Reply
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Minimal MySQL queries for Blog Tags

Post by JAB Creations »

My goal with this PHP/MySQL function is to minimize MySQL queries when adding, deleting, and or otherwise updating tags for a blog. At most the function executes seven queries. The script I've posted here attempts to visualize what is going on to make debugging a bit easier. I've added some comments as well. I've done a bit of testing with this though I am not entirely certain that it is still bug free. I'm pretty certain there are some parts that can be criticized though I'm not declaring I'm even close to mastering PHP so please be gentle. :lol:

I worked hard on this for the past week and I think it's pretty much one of the if not the most sophisticated pieces I've worked on yet. I didn't really have the stamina to continue working on it I think back in December. However since then my understanding of PHP and MySQL has grown a bit more where I became comfortable with tackling the problem again. This is the third revision and I'm also providing the MySQL tables for testing purposes. I highly appreciate and welcome all constructive feedback as far as the functionality of the function goes. Thanks to all those who consider looking at and giving me input about what I could do to improve it. :)

tags4.php

Code: Select all

<?php
include("mysql.php");
?>
<style type="text/css">
label {border: #000 dotted 1px; margin: 0px 2px 0px 0px; padding: 0px 2px 0px 2px;}
b.error {color: #f00;}
b.mysql {color: #00f;}
</style>
<div><a href="tags4.php">tags1</a></div>
<?php
 
function mysql_insert($c1, $c2)
{
 $count = count($c1);
 $i = 1;
 foreach($c1 as $key => $value)
 {
  $final .= " ('".$value."', '".name_base($value)."')";
  if ($i < $count) {$final .=  ", ";}
  $i++;
 }
 return $final;
}
 
function name_base($key)
{
 $name2 = str_replace(" ", "_", $key);
 $name3 = str_replace("&", "and", $name2);
 $name4 = strtolower($name3);
 return $name4;
}
 
blog_tags();
 
function blog_tags()
{
 $thread_id = mysql_real_escape_string($_POST['post_id']);
 $thread_tags_new = mysql_real_escape_string($_POST['tags_new']);
 $thread_tags_old = mysql_real_escape_string($_POST['tags_old']);
 
 echo '<div><b>Blog Thread ID: </b>'.$thread_id.'</div>'."\n";
 echo '<div><b>$_POST New: </b>'.$thread_tags_new.'</div>'."\n";
 echo '<div><b>$_POST Old: </b>'.$thread_tags_old.'</div>'."\n";
 
 // Compare old/new tags, new tags must have ID's added to DB.
 $tags_array_new = explode(', ',$thread_tags_new);
 $tags_array_old = explode(', ',$thread_tags_old);
 $tags_post = array_diff($tags_array_new, $tags_array_old);
 
 //$query1 = "SELECT tag_name FROM blog_tags WHERE tag_name='".implode("' OR tag_name='",$tags_post)."'";
 $query1 = "SELECT tag_name FROM blog_tags WHERE tag_name='".implode("' OR tag_name='",$tags_array_new)."'";
 $result1 = mysql_query($query1);
 
 if ($result1)
 {
  // We have existing MySQL tag_names...
  echo '<div><b class="mysql">$query1:</b> '.$query1.'</div>'."\n";
 
  $count1 = mysql_num_rows($result1);
  $count1_post = count($tags_array_new);
 
  echo '<div><b>Tags in DB ('.$count1.'): </b>';
 
  //Tags in DB loop
  $i = 1;
  while ($row1 = mysql_fetch_array($result1))
  {
   echo $row1['tag_name'];
   $tags_exist .= $row1['tag_name'];
   if ($i < $count1) {$tags_exist .= ", "; echo ', ';}
   $i++;
  }
 
  if ($count1=='0')
  {
   // All tags INSERT
   echo '<div><b>Tags to add (0):</b></div>'."\n";
   $count_tags = count($tags_array_new);
   $query2 = "INSERT INTO blog_tags (tag_name, tag_name_base) VALUES ".mysql_insert($tags_post, $count_tags);
  }
  else if ($count1 != $count1_post)
  {
   // Some tags INSERT
   $tags_exist = explode(', ',$tags_exist);
   $tags_new = array_diff($tags_post,$tags_exist);
   $count_new = count($tags_new);
 
   echo '<div><b>Tags to add ('.$count_new.'):</b> ';
 
   // Tags to add loop
   $i = 1;
   while ($i <= $count_new)
   {
    echo $tags_new[$i];
    if ($i < $count_new) {echo ', ';}
    $i++;
   }
   echo '</div>'."\n";
 
   $count_tags = count($tags_new);
   $query2 = "INSERT INTO blog_tags (tag_name, tag_name_base) VALUES ".mysql_insert($tags_new, $count_tags);
  }
  else
  {
   echo '<div><b>Tags to add (0):</b></div>'."\n";
  }
  // Leave commented for testing purposes; consistency for add all tags, some, and none scenarios.
  $result2 = mysql_query($query2);
  //$result2 = true;
 
  // COLLECT ALL TAG ID'S
  if ($result2 || $count1==$count1_post)
  {
   echo '<div><b class="mysql">$query2:</b> '.$query2.'</div>'."\n";
 
   //$tags_array_new = explode(', ',$tags_post);
 
   //$query3 = "SELECT tag_id FROM blog_tags WHERE tag_name='".implode("' OR tag_name='",$tags_post)."'";
   $query3 = "SELECT tag_id FROM blog_tags WHERE tag_name='".implode("' OR tag_name='",$tags_array_new)."'";
   $result3 = mysql_query($query3);
 
   // START RELATIONAL TAGS TABLE
   if ($result3)
   {
    echo '<div><b class="mysql">$query3:</b> '.$query3.'</div>'."\n";
 
    $count3 = mysql_num_rows($result3);
    echo '<div><b>SELECT Tag ID\'s ('.$count3.'):</b> ';
 
    $tags3 = array();
    $i = 1;
    while ($i <= $count3 && $row3 = mysql_fetch_assoc($result3))
    {
     array_push($tags3, $row3['tag_id']);
     echo $row3['tag_id'];
     $tags_ids_exist .= "xhref_tag_id='".$row3['tag_id']."' AND xhref_thread_id='".$thread_id."'";
     if ($i < $count3) {$tags_ids_exist .=  " OR "; echo ', ';}
     $i++;
    }
    echo '</div>';
 
    $query4 = "SELECT xhref_tag_id AS tag_id FROM blog_xhref_tags WHERE ".$tags_ids_exist;
    if ($count3 != '0') {$result4 = mysql_query($query4);}
    else {$result4 = true;}
 
    if ($result4)
    {
     echo '<div><b class="mysql">$query4:</b> ';
     if ($count3 != '0') {echo $query4;}
     echo '</div>'."\n";
 
     $count4 = mysql_num_rows($result4);
     echo '<div><b>Tag id\'s in relational table ('.$count4.'):</b> ';
     //Tags in relational table loop
     $i = 1;
     while ($i <= $count4 && $row4 = mysql_fetch_assoc($result4))
     {
      echo $row4['tag_id'];
      $tags4 .= $row4['tag_id'];
      if ($i < $count4) {$tags4 .= ", "; echo ', ';}
      $i++;
     }
     echo '</div>';
 
     $query5 = "SELECT xhref_tag_id AS tag_id FROM blog_xhref_tags WHERE xhref_thread_id='".$thread_id."'";
     $result5 = mysql_query($query5);
 
     if ($result5)
     {
      echo '<div><b class="mysql">$query5:</b> '.$query5."</div>\n";
 
      //ALL Tags in relational table loop for thread
      $count5 = mysql_num_rows($result5);
      $i = 1;
      //$tags5 = array();
      echo '<div><b>All Tag id\'s in relational table ('.$count5.'):</b> ';
 
      while ($row5 = mysql_fetch_array($result5))
      {
       echo $row5['tag_id'];
       $tags5 .= $row5['tag_id'];
       if ($i < $count5) {$tags5 .= ", "; echo ', ';}
       $i++;
      }
 
      $tags5 = explode(', ',$tags5);
      $tags6 = array_diff($tags5,$tags3);
      $count6 = count($tags6);
      $i = 1;
      echo '<div><b>Relational tag id\'s to DELETE ('.$count6.'):</b> ';
      foreach ($tags6 as $value)
      {
       $tags6_del .= "xhref_tag_id='".$value."' AND xhref_thread_id='".$thread_id."'";
       echo $value;
       if ($i < $count6) {$tags6_del .= ' OR '; echo ', ';}
       $i++;
      }
      echo '</div>';
 
      $query6 = "DELETE FROM blog_xhref_tags WHERE ".$tags6_del;
      $result6 = mysql_query($query6);
 
      if ($result6 || $count6 =='0')
      {
       echo '<div><b class="mysql">$query6:</b> ';
       if ($count6 !='0') {echo $query6;}
       echo "</div>\n";
 
       $tags4 = explode(', ',$tags4);
       $tags7 = array_diff($tags3,$tags4);
       $count7 = count($tags7);
       $i = 1;
       echo '<div><b>Relational tag id\'s to add ('.$count7.'):</b> ';
       foreach ($tags7 as $value)
       {
        $tags7_add .= "('".$value."','".$thread_id."')";
        echo $value;
        if ($i < $count7) {$tags7_add .= ', '; echo ', ';}
        $i++;
       }
       echo '</div>';
 
       $query7 = "INSERT INTO blog_xhref_tags (xhref_tag_id, xhref_thread_id) VALUES".$tags7_add;
       $result7 = mysql_query($query7);
 
       if ($result7 || $count7 =='0')
       {
        echo '<div><b class="mysql">$query7:</b> ';
        if ($count7 !='0') {echo $query7;}
        echo "</div>\n";
       }
       else {echo '<div><b class="error">!query7:</b> '.$query7.'</div>';}
      }
      else {echo '<div><b class="error">!query6:</b> '.$query6.'</div>';}
     }
     else {echo '<div><b class="error">!query5:</b> '.$query5.'</div>';}
    }
    else {echo '<div><b class="error">!query4:</b> '.$query4.'</div>';}
   }
   else {echo '<div><b class="error">!query3:</b> '.$query3.'</div>';}
  }
  else {echo '<div><b class="error">!query2:</b> '.$query2.'</div>';}
 }
 else {echo '<div><b class="error">!query1:</b> '.$query1.'</div>';}
}
?>
<form action="<?php
if ($_GET['tags'] == "1") {echo 'tags4.php?tags=2';}
else if ($_GET['tags'] == "2") {echo 'tags4.php?tags=3';}
else if ($_GET['tags'] == "3" || !isset($_GET['tags'])) {echo 'tags4.php?tags=1';}
?>" method="post">
<fieldset>
<div><label for="tags_new">Tags:</label><input id="tags_new" name="tags_new" style="width: 512px;" value="<?php
$tags1 = 'XHTML, CSS, JavaScript';
$tags2 = 'Linux, Apach, MySQL, PHP';
$tags3 = 'Windows, Apach, MySQL, PHP';
if ($_GET['tags'] == "3") {echo $tags3;}
else if ($_GET['tags'] == "2") {echo $tags2;}
else if ($_GET['tags'] == "1" || !isset($_GET['tags'])) {echo $tags1;}
?>" /></div>
 
<input name="tags_old" type="hidden" value="<?php
$tags1 = 'XHTML, CSS, JavaScript';
$tags2 = 'Linux, Apach, MySQL, PHP';
$tags3 = 'Windows, Apach, MySQL, PHP';
if ($_GET['tags'] == "3") {echo $tags2;}
else if ($_GET['tags'] == "2") {echo $tags1;}
else if ($_GET['tags'] == "1" || !isset($_GET['tags'])) {echo $tags3;}
?>" />
 
<input name="post_id" type="hidden" value="1" />
<div><input type="submit" /></div>
</fieldset>
</form>
MySQL Tables

Code: Select all

---- Table structure for table `blog_tags`-- CREATE TABLE IF NOT EXISTS `blog_tags` (  `tag_id` INT(6) NOT NULL AUTO_INCREMENT,  `tag_name` VARCHAR(64) COLLATE utf8_unicode_ci NOT NULL,  `tag_name_base` VARCHAR(64) COLLATE utf8_unicode_ci NOT NULL,  PRIMARY KEY  (`tag_id`)) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=250 ;  ---- Table structure for table `blog_xhref_tags`-- CREATE TABLE IF NOT EXISTS `blog_xhref_tags` (  `xhref_id` INT(6) NOT NULL AUTO_INCREMENT,  `xhref_tag_id` INT(6) NOT NULL,  `xhref_thread_id` INT(6) NOT NULL,  PRIMARY KEY  (`xhref_id`)) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=445 ; 
Post Reply