Updating a tag array using a CMS

[kingnutter]

Hi everyone,

 

I want to put a tag cloud on a site I’m developing, and have changed my original table structure to accommodate this. Originally I had all my tags entered as a comma separated array which could just be updated as a text field.

 

Advice given on the MySQL board was that this was a very clunky approach and that tags should all be stored in separate tables.

 

Fair enough, but it is making the ‘update tags’ facility in my CMS very tricky to create.

 

This is the form field on the Update page that recalls previously entered tags:

 

<td>
<input size="45" max length="254" type="text" name="moj_genre" value="
<?php


$query="SELECT moj_genre FROM genres INNER JOIN genrelinkcd ON genres.genre_id=genrelinkcd.genre_id WHERE genrelinkcd.moj_id='$id'";
$result=mysql_query($query);
$num_rows=mysql_num_rows($result);
$count=1;
while($orig_genres = mysql_fetch_row($result))
{

echo "$orig_genres[0]";

if ($count<$num_rows)

{echo ", ";}

$count++;
}?>	

">

 

As you can see I have two tables:

 

1) genres – which assigns an id (genre_id ) to each tag (moj_genre)

2) genrelinkcd – which links genre_id to moj_id

 

I think I have found the best approach to create the CMS page allowing me to update these, but am getting a bit mixed up with all the table joins and comparing arrays.

 

Once the new tags have been entered (separated by commas) they are exploded into an array ($new_genres).  I need to compare between the original array ($orig_genres) and new tags and there are three possible options for each instance:

 

1) A different tag is entered. A new record has to be created in ‘genrelinkcd’. Also, if this is a brand new tag a new entry must be made into ‘genres’

2) A tag has been removed. A record has to be removed from ‘genrelinkcd’. If no instances of this tag remain in genrelinkcd the tag needs to be removed from ‘genres’

3) A tag remains unchanged. No action need be taken. Phew.

 

So after all that, my big question is what is the best way to compare two arrays and update / amend them? Should I be using associative arrays for any reason e.g. genre_id, moj_genre rather than having to make loads of comparitive joins between tables?

 

Or am I taking the wrong approach entirely?

 

Any pointers or links to tutorials appreciated.

 

Thanks.

KN

 

[kingnutter]

Have I asked for too much information here?

 

If anybody can help if I break it down more please let me know.