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>