UltimateGoal: Edit tags for an existing blog post using minimal number of MySQL queries.
Secondary Goal: Must determine if tag exists in tag table (not the relational tag table).
Third Goal: If the tag exists in the tag table add it to relational table else if the tag does not exist in the tag table it must be added to the tag table before it can be added to the relational table.
So...
I'm trying to build an array of id's for the tags from the tag table first which is what this thread is all about.
Once I have that array then I can compare the arrays and where tags don't yet exist in the tag table build up a single MySQL query to INSERT all the new tags.
Then once all those new tags exist take get the ID's once more and INSERT to the tag relational table.
It's a bit of a mess right now because unfortunately there is both a lot of code the inability to coherently minimize the explanation. That is why I am trying to be as specific as I can for this thread.
As far as this thread is concerned I'm trying to align arrays however if I remove a tag from an existing blog post then if that is the fifth of twenty tags all tags five and greater will not align as desired and the wrong tags will be added or removed. Therefor by having a null value when a row is otherwise not returned
whatsoever I retain that structure. Otherwise at least from my POV I'll end up having to stick a MySQL query inside of a loop and that just screams noob to me.
I have successfully created the opposite where I have to
remove tags when they no longer appear in the $_POST array for editing a blog entry.
Here is the PHP and MySQL example where I have successfully created a *single* dynamic delete query to remove all the relational tag rows for a blog entry if they are removed from a blog entry...
Code: Select all
<?php
include("_0_header_02_mysql.php");
$thread_tags_new = $_POST['tags_new'];
$thread_tags_old = $_POST['tags_old'];
$tags_array_new = explode(', ',$thread_tags_new);
$tags_array_old = explode(', ',$thread_tags_old);
$tags_remove = array_diff($tags_array_old, $tags_array_new);
$result = mysql_query("SELECT tag_id FROM blog_tags WHERE tag_name='".implode("' OR tag_name='",$tags_remove)."'");
//$row = mysql_fetch_array($result);
$count = count($tags_remove);
echo '<div>count == '.$count.'</div>';
echo '<div>';
$i = 0;
while ($i <= $count && $row = mysql_fetch_array($result)) {
//print_r($row[0]);
echo '<div>'.$i.' == '; print_r($row[0]); echo '</div>';
$i++;
$new_array[$i] = $row[0];
}
mysql_free_result($result);
echo '</div>';
//print_r($new_array);
//$query2 = "DELETE FROM blog_xhref_tags WHERE xhref_thread_id='1' AND (xhref_tag_id='".implode("' OR xhref_tag_id='",$tags_remove)."')";
echo "DELETE FROM blog_xhref_tags WHERE xhref_thread_id='1' AND (xhref_tag_id='";
print_r(implode("' OR xhref_tag_id='",$new_array));
echo "')";
?>
<form action="<?php
if ($_GET['tags'] == "1") {echo 'test5.php?tags=2';}
else if ($_GET['tags'] == "2" || !isset($_GET['tags'])) {echo 'test5.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, Java';
$tags2 = 'application/xhtml+xml, XML, XHTML, CSS, JavaScript, PHP, MySQL';
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, Java';
$tags2 = 'application/xhtml+xml, XML, XHTML, CSS, JavaScript, PHP, MySQL';
if ($_GET['tags'] == "2") {echo $tags1;}
else if ($_GET['tags'] == "1" || !isset($_GET['tags'])) {echo $tags2;}
?>" />
<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=39 ; ---- 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'); SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";-- Database: `jabcreat_members` -- 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=31 ; -- 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);