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 ;