MySQL SELECT to array then MySQL DELETE by array?
Posted: Thu Nov 20, 2008 1:58 pm
I'm not a genius when it comes to arrays though I think I know how to minimize MySQL queries by using an array when trying to add or delete tags (for my blog).
pytrin showed me an awesome way to compare arrays and what I'm thinking is that after I've compared the original tags to the new tags I can assign those tags that have been added or removed to an array and then (I think what pytrin is showing me in his second post in the thread) use the array to add or remove all the tags in a single MySQL query.
This is sort of really the only thing I've really been struggling with while working on the blog (except when I first was trying to learn the concept of how relational databases work) because I could probably do it fine on my own though using a loop and I know with some help we could do this a lot better.
So I've been messing with a couple temporary files. Below is a form I've set up to quicken the pace of testing. What it essentially does is switch which tags are old and which tags are new. Some tags appear in both the old and new array while there differing tags in both arrays are either to be added or removed. By using a $_GET in the query it switches back and forth between the array...so once this is working I don't have to manually delete anything to retest it (as if this wasn't difficult enough!)
Right now I think I'm still going to have to do two queries to remove tags. In example I'll need to get an array of id's for the tags...
TABLE: blog_tags
COLUMNS: tag_id, tag_name
In this scenario would the mysql_fetch_array function create the type of array we want to work with?
Then the second query somehow takes the array of tag_id's that are now somehow associated with the array of tags to be deleted and we somehow create a MySQL query to remove all of those tags from the relational database. I think this is where the advantage of caffeine starts to end for me.
Well any way here is what my relational table looks like for the tags...
TABLE: blog_xhref_tags
COLUMNS: xhref_id, xhref_tag_id, xhref_thread_id
Any way I need some ideas please to help me in to second gear and maybe a little more organized, here is what I currently have...
pytrin showed me an awesome way to compare arrays and what I'm thinking is that after I've compared the original tags to the new tags I can assign those tags that have been added or removed to an array and then (I think what pytrin is showing me in his second post in the thread) use the array to add or remove all the tags in a single MySQL query.
This is sort of really the only thing I've really been struggling with while working on the blog (except when I first was trying to learn the concept of how relational databases work) because I could probably do it fine on my own though using a loop and I know with some help we could do this a lot better.
So I've been messing with a couple temporary files. Below is a form I've set up to quicken the pace of testing. What it essentially does is switch which tags are old and which tags are new. Some tags appear in both the old and new array while there differing tags in both arrays are either to be added or removed. By using a $_GET in the query it switches back and forth between the array...so once this is working I don't have to manually delete anything to retest it (as if this wasn't difficult enough!)
Right now I think I'm still going to have to do two queries to remove tags. In example I'll need to get an array of id's for the tags...
TABLE: blog_tags
COLUMNS: tag_id, tag_name
In this scenario would the mysql_fetch_array function create the type of array we want to work with?
Then the second query somehow takes the array of tag_id's that are now somehow associated with the array of tags to be deleted and we somehow create a MySQL query to remove all of those tags from the relational database. I think this is where the advantage of caffeine starts to end for me.
TABLE: blog_xhref_tags
COLUMNS: xhref_id, xhref_tag_id, xhref_thread_id
Any way I need some ideas please to help me in to second gear and maybe a little more organized, here is what I currently have...
Code: Select all
<?php
//$thread_tags_new = 'application/xhtml+xml, XML, XHTML, CSS, Flash, ASP.NET, Java';
//$thread_tags_old = 'application/xhtml+xml, XML, XHTML, CSS, JavaScript, PHP, MySQL';
include("_0_header_02_mysql.php");
echo '<br /><br /><br />';
$thread_tags_new = $_POST['tags_new'];
$thread_tags_old = $_POST['tags_old'];
echo '<div>'.$thread_tags_new.'</div>';
echo '<div>'.$thread_tags_old.'</div>';
echo '<br /><br /><br />';
$tags_array_new = explode(', ',$thread_tags_new);
$tags_array_old = explode(', ',$thread_tags_old);
echo '<div><b>Remove Tags</b></div>';
$tags_remove = array_diff($tags_array_old, $tags_array_new);
print_r($tags_remove);
echo '<br /><br /><br />';
/* Deletes multiple tags? */
//$mysql_result = mysql_query("DELETE FROM blog_xhref_tags WHERE xhref_thread_id='1' AND (xhref_tag_id = '" . implode(" OR `tag_column`='",$remove_tags) . "')");
echo '<div><b>Add Tags</b></div>';
$tags_add = array_diff($tags_array_new, $tags_array_old);
print_r($tags_add);
echo '<br /><br /><br />';
echo '<div><b>Tags to Remove</b></div>';
foreach ($tags_remove as $value)
{
echo '<div>'.$value.'</div>';
}
include("_0_header_02_mysql.php");
$query = mysql_query("DELETE FROM `blog_xhref_tags` WHERE `xhref_thread_id`=`1` AND (`tag_column` = '" . implode(" OR `tag_column`='",$tags_remove) . "')");
if (!query) {echo '<div>myql fail: '.mysql_error().'</div>';}
else {echo '<div>it worked? it worked!'.mysql_error().'</div>';}
?>
<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, 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>