Page 1 of 1

MySQL SELECT to array then MySQL DELETE by array?

Posted: Thu Nov 20, 2008 1:58 pm
by JAB Creations
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...

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>

Re: MySQL SELECT to array then MySQL DELETE by array?

Posted: Thu Nov 20, 2008 2:38 pm
by VladSun
:? :? :?
So ...

Code: Select all

$thread_tags_new = explode(', ', $_POST['tags_new']);
$tags_array_old = explode(', ', $_POST['tags_old']);
$tags_remove = array_diff($tags_array_old, $tags_array_new);
 
$query = mysql_query("DELETE FROM `blog_xhref_tags` WHERE `xhref_thread_id`=`1` AND (`tag_column` = '" . implode(" OR `tag_column`='",$tags_remove) . "')");
is this the code you need help with?
JAB Creations
DevNet Resident
Joined: 14/01/2005 02:44
Posts: 1505
:dubious:

What errors do you get ... ?

Re: MySQL SELECT to array then MySQL DELETE by array?

Posted: Thu Nov 20, 2008 2:56 pm
by JAB Creations
No, right now I'm trying to determine how to take an array of ID's from a SELECT query in regards to the regular tag table. That's easy enough though I'm not sure if I should use the mysql_fetch_array function. I was doing some reading on arrays somewhere earlier today where someone suggested using a different function.

I have to match arrays; in example I have to somehow say the 5th key of the first array (from the first query) matches the 3rd key from the tag_remove array.

Then once I know the id's of the tags that need to be removed I need to determine how to construct the second query to remove all those tags (or essentially the rows) from the tags relational table.

That is about as clear as I've been able to put the idea in to words.

Re: MySQL SELECT to array then MySQL DELETE by array?

Posted: Fri Nov 21, 2008 5:07 am
by JAB Creations
Ah ha! I took a chance to stick pytrin's implode code in to a print_r function and I can see the output now!

What I could not get my head wrapped around was how you can implode an array? To me you would implode in ***to*** an array...and explode *out* of an array.

I didn't expect to be handed such a powerful piece of code like that!

Re: MySQL SELECT to array then MySQL DELETE by array?

Posted: Fri Nov 21, 2008 9:38 am
by JAB Creations
Well I've been slowly making progress with the test file and it's seemingly coming along so I wanted to post what I have. I currently can SELECT the tag id's from existing tags that I intend to delete. I've tested that MySQL query and it works beautifully! I'm soon going to test the DELETE query and once I confirm that works I'm going to work on the INSERT query. Once I get to that point then ... err maybe I'll be able to breath! :mrgreen:

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");
$query1 = "SELECT tag_id FROM blog_tags WHERE tag_name='".implode("' OR tag_name='",$tags_remove)."'";
 
$query2 = "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>';}
 
echo '<br /><br />';
echo "SELECT tag_id FROM blog_tags WHERE tag_name='";
print_r(implode("' OR tag_name='",$tags_remove));
echo "'";
echo '<br /><br />';
print_r($query1);
echo '<br /><br />';
echo '<br /><br />';
echo "DELETE FROM blog_xhref_tags WHERE xhref_thread_id='1' AND (tag_column='";
print_r(implode("' OR tag_column='",$tags_remove));
echo "')";
echo '<br /><br />';
print_r($query2);
echo '<br /><br />';
echo '<br /><br />';
?>
 
<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>