Tag System

[eluser]JulianM[/eluser]
Hi.

Did you evaluate to use CXTags 1.0?

It worked nice for me for many projects.

Julian

[eluser]Sein Kraft[/eluser]
Yes, the problem I've already finished the read system of tags and integrated to my project...at this point i shouldn't change everithing.

[eluser]JulianM[/eluser]
Ah Ok.
However CXTags has a simple scheme, it would be easy to integrate to any existing project.

Julian

[eluser]jedd[/eluser]
[quote author="Sein Kraft" date="1239597633"]It simple.
t_count is an integer. If the tag is used in another image it grow.

By ex. I've 27 images with the tag "coconuts" the value of t_count on the tag "coconuts" will be 27.
[/quote]

Don't do it this way.

If you want to know how many pictures have a given tag, use a SELECT COUNT() query instead. eg.

Code:

mysql>  SELECT COUNT(*) AS count FROM tag WHERE name="coconut";

You then say:

I'm inserting an auto increment because i use it to vincule the tags with the images using the node table.

But you can't have multiple rows with the same ID.

Or more accurately, you can't have multiple rows with the same Primary Key - and since you won't show me your schema, I'm just guessing that your ID column is your primary key, in which case my first statement is true.

So your code - that you still have here (I corrected it in the last post) :

Code:

$data = array(
                        't_id'     => NULL,
                        't_name' => $tag,
                        't_count' => '1'
                        );
        $this->db->insert('tags', $data);

-- will work once, but the second time it will fail because you're trying to have two primary keys both set to NULL.

Please refer my previous post for how to not do this.

You shouldn't really, if you are using an autoincrement field, have a NULL in there at all - it'll make subsequent searches much harder.

[eluser]Sein Kraft[/eluser]
Here you have the sql

Code:

-- phpMyAdmin SQL Dump
-- version 3.1.1
-- http://www.phpmyadmin.net
--
-- Servidor: localhost
-- Tiempo de generación: 12-04-2009 a las 21:48:42
-- Versión del servidor: 5.1.30
-- Versión de PHP: 5.2.8

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Base de datos: `kl6g56pd8p`
--

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `node`
--

CREATE TABLE IF NOT EXISTS `node` (
  `g_id` varchar(10) NOT NULL,
  `t_id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Volcar la base de datos para la tabla `node`
--

INSERT INTO `node` (`g_id`, `t_id`) VALUES
('rprvrqlfxx', 1),
('rprvrqlfxx', 2),
('rprvrqlfxx', 3),
('cmvbqyjrmc', 6),
('cmvbqyjrmc', 5),
('cmvbqyjrmc', 4),
('qmfeyvssdg', 1),
('qmfeyvssdg', 3),
('qmfeyvssdg', 7),
('ocaybtkxaa', 8),
('ocaybtkxaa', 9),
('ocaybtkxaa', 10);

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tags`
--

CREATE TABLE IF NOT EXISTS `tags` (
  `t_id` int(10) NOT NULL AUTO_INCREMENT,
  `t_name` varchar(64) NOT NULL,
  `t_count` int(10) NOT NULL,
  PRIMARY KEY (`t_id`),
  UNIQUE KEY `t_name` (`t_name`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

--
-- Volcar la base de datos para la tabla `tags`
--

INSERT INTO `tags` (`t_id`, `t_name`, `t_count`) VALUES
(1, 'tail', 1),
(2, ' beetlecat', 1),
(4, 'ink', 1),
(8, 'text', 1),
(9, ' forum', 1),
(10, ' txt', 1);

g_id is the id of the image in images table.

with a big gallery of thousands of images the Select Count can be a problem.

[eluser]jedd[/eluser]
Hmm .. I thought you said you were using the 3-table approach, but you have shown two here?

Did you specify a primary key on your node table? It doesn't look like it has one - it might be assuming something that you don't agree with (either the first, or a compound key).

with a big gallery of thousands of images the Select Count can be a problem.

Are you saying this because you have already run tests to evaluate the performance ... or because you think a computer will have trouble counting past 1,000 ?

Seriously - use COUNT() - it'll be blindingly fast, and you won't have to update your tag table every time you modify your image table.

How frequently do you think you will need to know the number of occurrences of a tag across all images anyway?

[eluser]Crimp[/eluser]
This thread seems to complicate things more than necessary. You have a standard many-to-many db relationship and thus need to use a lookup table. The t_count column is, as pointed out, not needed. Delete it.