CODEWITHSUNDEEP
phpmysqlsqltagging
john mossel
john mossel

Reputation: 2156

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 have 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?

Upvotes: 1

Views: 133

Answers (5)

Kuberchaun
Kuberchaun

Reputation: 30314

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.

Upvotes: 0

Matthew
Matthew

Reputation: 48284

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.

Upvotes: 2

Peter
Peter

Reputation: 48958

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

Upvotes: 0

Marc B
Marc B

Reputation: 360572

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.

Upvotes: 2

mfonda
mfonda

Reputation: 7993

You can insert multiple rows with one insert statement:

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

Upvotes: 1

Related Questions