Blog Tags: MySQL involved in editing a post.
Posted: Sat Nov 29, 2008 2:06 am
In the past month half the time spent has gone towards merely creating the functionality to edit the tags in an existing blog post. Initially I simply had no concept about how to approach the "mini" project though I knew I wanted to minimize the number of MySQL queries made even if (when this is later implemented as a function) this function will only be used on rare occasion. It turned out I had to do a lot of work with arrays and I'll be honest it's not really a strong point for me. However tonight when I finally uncommented out the mysql_query functions everything seemed to work on the first try! I simply didn't know how I was going to get this to work though there are about six queries total for adding and removing tags when editing an existing blog post...which I think is pretty minimal. Any way I want to thank everyone who posted trying to help me out with this. I know I don't have perfect etiquette here on the forums so if I do anything rude I swear it's not intentional. This stuff might take some of you guys an hour or even less to write but with me we're talking crazy villains with ray guns and I dunno a giant octopus slash tank with laser eyes kind of mind warping craziness for me.
Any way I'm not 100% sure this works perfectly and I'm sure there are ways to improve this. My main concern is posting this "mini" project in public for others to tinker with, maybe it'll benefit some other people since others were so kind enough to help me to begin with. So the main point of this was to initially output MySQL statements with a form that toggled between two sets of tags so I didn't have to manually enter anything in (though I could manually change the text field of course). That makes it super easy and quick to test. The bold red text signifies MySQL queries that are executed. Again I expect to find a bug or two myself...not sure how good this is on the efficiency scale. Keep in mind the PHP below is part of my test file and is not yet implemented in to the blog software just yet.
Any way once I tune this up, implement it, and finish some other (thankfully much easier) things I'm going to post the entire blog software soon so everyone can tinker with that too. Please let me know what you guys think please and I'll try to read your posts a little more carefully from now on.
MySQL
test3.php
Any way I'm not 100% sure this works perfectly and I'm sure there are ways to improve this. My main concern is posting this "mini" project in public for others to tinker with, maybe it'll benefit some other people since others were so kind enough to help me to begin with. So the main point of this was to initially output MySQL statements with a form that toggled between two sets of tags so I didn't have to manually enter anything in (though I could manually change the text field of course). That makes it super easy and quick to test. The bold red text signifies MySQL queries that are executed. Again I expect to find a bug or two myself...not sure how good this is on the efficiency scale. Keep in mind the PHP below is part of my test file and is not yet implemented in to the blog software just yet.
Any way once I tune this up, implement it, and finish some other (thankfully much easier) things I'm going to post the entire blog software soon so everyone can tinker with that too. Please let me know what you guys think please and I'll try to read your posts a little more carefully from now on.
MySQL
Code: Select all
-- PHP Version: 5.2.5SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";-- Database: `jabcreat_members`-- 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=132;-- Dumping data for table `blog_tags` INSERT INTO `blog_tags` (`tag_id`, `tag_name`, `tag_name_base`) VALUES(1, 'application/xhtml+xml', 'applicationxhtmlxml'),(2, 'XML', 'xml'),(3, 'XHTML', 'xhtml'),(4, 'CSS', 'css'),(5, 'JavaScript', 'javascript'),(6, 'Example Tag', 'example_tag'),(7, 'Tag1', 'tag1'),(8, 'Tag2', 'tag2'),(9, 'Tag3', 'tag3'),(19, 'MySQL', 'mysql'),(18, 'PHP', 'php'),(36, 'Flash', 'flash'),(37, 'ASP.NET', 'aspnet'),(38, 'Java', 'java'); -- 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=37 ; -- Dumping data for table `blog_xhref_tags` INSERT INTO `blog_xhref_tags` (`xhref_id`, `xhref_tag_id`, `xhref_thread_id`) VALUES(1, 1, 1),(2, 2, 1),(3, 3, 1),(4, 4, 1),(5, 2, 2),(6, 3, 3),(7, 4, 4),(8, 3, 2),(9, 2, 3),(10, 1, 4),(11, 3, 4),(12, 3, 6),(13, 3, 5),(14, 3, 7),(28, 34, 1),(27, 33, 1),(29, 5, 1);Code: Select all
<?php
include("_0_header_02_mysql.php");
?>
<style type="text/css">
b.mysql {color: #f00;}
</style>
<?php
function name_base($key)
{
$name2 = str_replace(" ", "_", $key);
$name3 = str_replace("&", "and", $name2);
$name4 = strtolower($name3);
//echo $name4.'<br />';
return $name4;
}
$thread_id = $_POST['post_id'];
$thread_tags_new = $_POST['tags_new'];
$thread_tags_old = $_POST['tags_old'];
echo '<div><b>Thread ID: </b>'.$thread_id.'</div>';
echo '<div><b>New: </b>'.$thread_tags_new.'</div>';
echo '<div><b>Old: </b>'.$thread_tags_old.'</div>';
$tags_array_new = explode(', ',$thread_tags_new);
$tags_array_old = explode(', ',$thread_tags_old);
echo '<div><b>Remove Tags: </b>';
$tags_remove = array_diff($tags_array_old, $tags_array_new);
print_r($tags_remove);
echo '</div>';
/* Deletes multiple tags? */
//$mysql_result = mysql_query("DELETE FROM blog_xhref_tags WHERE xhref_thread_id='1' AND (xhref_tag_id = '" . implode(" OR xhref_tag_id='",$remove_tags) . "')");
echo '<div><b>Add Tags: </b>';
$tags_add = array_diff($tags_array_new, $tags_array_old);
print_r($tags_add);
echo '</div>';
if (!empty($tags_add))
{
$count = count($tags_array_new);
$query1 = "SELECT tag_name FROM blog_tags WHERE tag_name='".implode("' OR tag_name='",$tags_add)."'";
$mysql_result1 = mysql_query("SELECT tag_name FROM blog_tags WHERE tag_name='".implode("' OR tag_name='",$tags_add)."'");
echo '<div><b class="mysql">SELECT tags: </b>';
print_r($query1);
$mysql_q01 = mysql_query($query1);
echo '</div>';
echo '<div><b>Tags Existing in Tags Table: </b>';
$i = 1;
while ($i <= $count && $row = mysql_fetch_array($mysql_result1))
{
//print_r($row[0]);
print_r($row[0]);
echo ', ';
//echo '<div>'.$i.' == '; print_r($row[0]); echo '</div>';
$i++;
$tags_add_existing[$i] = $row[0];
}
echo '</div>';
$tags_add_insert = array_diff($tags_add, $tags_add_existing);
$count2 = count($tags_add_existing);
$tags_add_insert = array_diff($tags_add, $tags_add_existing);
echo '<div><b>Tags for TAG table INSERT: </b>';
print_r($tags_add_insert);
echo '</div>';
$test = array_map('name_base', $tags_add_insert);
$c = array_combine($tags_add_insert, $test);
$count = count($c);
function mysql_insert_add($c, $count)
{
$i = "1";
foreach($c as $key => $value)
{
//echo "('".$key."', '".$value."')";
$final .= "('".$key."', '".$value."')";
if ($i < $count) {$final .= ", ";}
$i++;
}
return $final;
}
echo '<div><b class="mysql">INSERT tags: </b>';
$query2 = "INSERT INTO blog_tags (tag_name, tag_name_base) VALUES ".mysql_insert_add($c, $count);
echo $query2;
$mysql_q02 = mysql_query($query2);
echo '</div>';
echo '<div><b class="mysql">SELECT tag ID\'s: </b>';
$query3 = "SELECT tag_id FROM blog_tags WHERE tag_name='".implode("' OR tag_name='",$tags_add)."'";
echo $query3;
$mysql_q03 = mysql_query($query3);
//$row_q03 = mysql_fetch_assoc($mysql_q03);
$count = mysql_num_rows($mysql_q03);
$i=0;
while ($i <= $count && $row = mysql_fetch_array($mysql_q03))
{
$row_q03[$i] = $row[0];
$i++;
}
$count_add = count($tags_add);
$query4 = "INSERT INTO blog_xhref_tags (xhref_tag_id, xhref_thread_id) VALUES ('".implode("', '".$thread_id."'), ('",$row_q03)."', '".$thread_id."')";
$mysql_q04 = mysql_query($query4);
echo '<div><b class="mysql">RELATIONAL TAG INSERT: </b>'.$query4.'</div>';
}
if (!empty($tags_remove))
{
echo '<h2>Delete Tags!</h2>';
$query5 = "SELECT tag_id FROM blog_tags WHERE tag_name='".implode("' OR tag_name='",$tags_remove)."'";
echo '<div><b class="mysql">SELECT tags: </b>'.$query5.'</div>';
$mysql_q05 = mysql_query($query5);
print_r($query10);
echo '</div>';
$count = mysql_num_rows($mysql_q05);
$i=0;
while ($i <= $count && $row = mysql_fetch_array($mysql_q05))
{
$row_q04[$i] = $row[0];
$i++;
}
$query6 = "DELETE FROM blog_xhref_tags WHERE xhref_thread_id='".$thread_id."' AND (xhref_tag_id='".implode("' OR xhref_tag_id='",$row_q04)."')";
echo '<div><b class="mysql">DELETE tags RELATIONAL: </b>'.$query6.'</div><br />';
$mysql_q06 = mysql_query($query6);
}
?>
<form action="<?php
if ($_GET['tags'] == "1") {echo 'test3.php?tags=2';}
else if ($_GET['tags'] == "2" || !isset($_GET['tags'])) {echo 'test3.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 = 'application/xhtml+xml, XML, XHTML, CSS, Flash, ASP.NET, Java1';
$tags2 = 'application/xhtml+xml, XML, XHTML, CSS, JavaScript, PHP1, MySQL1';
if ($_GET['tags'] == "1") {echo $tags1;}
else if ($_GET['tags'] == "2" || !isset($_GET['tags'])) {echo $tags2;}
?>" /></div>
<input name="tags_old" type="hidden" value="<?php
$tags1 = 'application/xhtml+xml, XML, XHTML, CSS, Flash, ASP.NET, Java1';
$tags2 = 'application/xhtml+xml, XML, XHTML, CSS, JavaScript, PHP1, MySQL1';
if ($_GET['tags'] == "2") {echo $tags1;}
else if ($_GET['tags'] == "1" || !isset($_GET['tags'])) {echo $tags2;}
?>" />
<input name="post_id" type="hidden" value="1" />
<div><input type="submit" /></div>
</fieldset>
</form>