Minimal MySQL queries for Blog Tags
Posted: Wed May 20, 2009 8:59 pm
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. 
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
MySQL Tables
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>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 ;