Storing tags without several trips to the database

This is what I normally do to store tags in a database:

   foreach ($tags as $tag)
   {
      if (mysqli_query($link, "INSERT INTO tag (name) VALUES ('$tag')") === false)
      {
         $queries_ok[] = false;   # I'm using this because I ha开发者_开发问答ve autocommit set to false, i.e. mysqli_autocommit($link, FALSE);
      }      
   }

However this involves connecting to the database several times, so if you have 10 tags, it involves connecting 10 times, is there another, simplier, faster way of doing this?

MySQL has an 'extended insert' syntax, which looks like this:

INSERT INTO table (a,b,c) VALUES (1,2,3), (4,5,6), (7,8,9), etc...

The only drawback is that this format is not supported by prepared statements, so you have to build the query yourself, which means taking the usual steps to avoid SQL injection problems.

I would recommend using prepared statements. Psuedocode, because I don't remember PHP's exact functions:

$st = $db->prepare('INSERT INTO tag(name) VALUES (?)');
$st->bindParam(1, $tag);
foreach ($tags as $tag)
  $st->exec();

While you are still hitting the DB per call, the query itself is not parsed on each exec() and you get the added benefit of safe parameter checking.

You can insert multiple rows with one insert statement:

INSERT INTO tag (name) VALUES ('tag1'), ('tag2'), ('tag3'), ...

I found the select/union alternatieve to be fairly generic and have used it lots of times on systmes where the extended insert wasn't available :

something (I have no databases on this host..) like this :

INSERT INTO TABLE (a,b,c)
SELECT 1, 2, 3 
union 
SELECT 4, 5, 6
union 
SELECT 7, 8, 9

In addition to what mfonda said you can do a

INSERT INTO table (a,b,c)
SELECT 1,2,3
UNION 
SELECT 4,5,6
UNION
SELECT 7,8,9;

This is the old school way, pre SQL-92 standard. This is how I was taught by all the old timers.