How to save tags/keywords from array to database with php?

Question

I saw this question: how to save tags(keywords) in database?

Good answer for how the database should be structured. But which way is best to do the saving process in php? A process that handles both adding and deleting.

The keywords are posted as an array.

Edit: The current code look like this:

<?php
    $new = explode(',', $_POST['tags']);

    $query = mysql_query("SELECT * FROM pages_tags WHERE page_id = '".$page_id."'") or die(mysql_error());
    while ($row = mysql_fetch_array($query))
    {
    $old[] = $row['tag'];
    }

    $tags_to_add    = array_diff($new, $old);
    $tags_to_remove = array_diff($old, $new);

    if (is_array($tags_to_add))
    {
    foreach ($tags_to_add as $add_tag) { $insert_tags[] = "('".$add_tag."', '".$page_id."')"; }
    $sql_insert_tags = "INSERT INTO pages_tags (tag, page_id) VALUES ".implode(',', $insert_tags);
    mysql_query($sql_insert_tags) or die(mysql_error());
    }

    if (is_array($tags_to_remove))
    {
    foreach ($tags_to_remove as $remove_tag) { $delete_tags[] = "('".$remove_tag."')"; }
    $sql_delete_tags = "DELETE FROM pages_tags WHERE page_id = '".$page_id."' AND tag IN (".implode(',', $delete_tags).")";
    mysql_query($sql_delete_tags) or die(mysql_error());
    }
?>

Mariellamarielle

Answer 1

I propose to select current tags and after you can do:

$tags_to_add = array_diff($new, $old);
$tags_to_remove = array_diff($old, $new);

After that you write 2 trivial queries: one to bulk insert and one to delete.

Kordula

@Kordula This answer was really interesting. So I start with saving the post tags and the tags from the database as each array? And then first run a while loop from database and match with $tags_to_add, and add them who not already exist in there in database. Then do a new database while loop to match the tags to remove? – Mariellamarielle

@Peter Westerlund: bulk insert: INSERT INTO table (field) VALUES ('val1'), ('val2'), .... Delete DELETE FROM table WHERE field IN ('val1', 'val2', ...). So you only need 2 queries. But at first you need to query all currently existing tags ($old) and diff it with $new – Kordula

@Kordula Is it necessary to have the all tables ´posts´, ´tags´ and ´post_tags´? It sabotages it a bit because I want the tag row from the table "tags" to disappear automatically if there is no post left with that tag. – Mariellamarielle

@Peter Westerlund: for what reason? – Kordula

@Kordula Because I need yet another query after each tag loop iteration which checks if there are any more of this tags left in the ´post_tag´ table, and if there isn't, then another query to run to delete the tag from ´tag´ table. – Mariellamarielle

@Peter Westerlund: you don't. Let the once added tag to be stored infinitely. – Kordula

@Peter Westerlund: that code should work. Don't see any reason to clean up tags anyway. – Kordula

@Kordula For example, if you spell wrong. I don't want unnecessary data in the database. I want it to function optimally. – Mariellamarielle

@Peter Westerlund: 1 word is not "not optimal". Even if there will be 10k obsolete elements - you wouldn't measure the performance difference, if you have set up your indexes properly. Anyway, if you want it (terrible idea) - you can create trigger to count currently really used tags and delete the ones with 0 entries by scheduler. – Kordula

@Kordula In my opinion, every meaningless row in the database is a waste of space. I think we have different views on what "optimal" means. Anyway, yes I want the tag to disappear from ´tags´ table also. I'd rather do that directly, but go as quickly as possible. – Mariellamarielle

@Peter Westerlund: I already have given you an idea how to clean them (and I still think that it is wasting of resources. And yes, I'm working with databases of 500gb+ size). – Kordula

@Kordula I don't think we will proceed further than this. Thanks for the solution on how to save the data. I may find out the rest by myself. Or create a new question. – Mariellamarielle

@Peter Westerlund: I already have given an idea how to do the rest ;-) Count with triggers and clean by scheduler. – Kordula

@Kordula Okay, sorry, I don't quite understand your answer. Can you please specify it a bit? – Mariellamarielle

@Peter Westerlund: create triggers ON DELETE and ON INSERT in your relation table. In both triggers update cnt field in tags table to the current amount of occurrences of current tag. – Kordula

@Kordula I think I understand now. I create a new column in tags table called 'count'. And update it at the same time I insert or delete a tag in the relation table? Pretty clever acually =) – Mariellamarielle

@Peter Westerlund: yep. And delete once a day (week, whatever) the ones with 0 – Kordula

@Kordula Well, I don't like that. Want it to be done directly. Think I do it right after the first process. Is that horribly wrong? – Mariellamarielle

@Peter Westerlund: you're talking about "efficiency" but want to do some useless work on each tags editing. Doesn't it sound strange? To do work 1 time a day is efficiently than to do it 100k times a day. Don't you think so? – Kordula