full text search plurals

[jodunno]

Hello everyone,

 

I am wondering if anyone can offer tips for dealing with plural forms of words in searches? l was wondering if boolean mode is simply the answer or if i should implement a keywords column which contains a list of keywords including plurals to be searched with like? I simply wish to find the database entry (which is in singular form) when a user searches for a plural form (user searches for meisen but title column houses meise).

Best wishes

[requinix]

How much do you care about good searches? The professional answer is to not use the database for it and to move to services like elasticsearch (which is free).
It takes some setup work, and it's not trivial, but it's much better at handling searches than a regular database will be.

But definitely don't do a keywords column. That's just not natural. (Tagging, on the other hand, is quite reasonable.)

Are you having a specific problem with your searches? Or do you just want to learn more about it?

[Barand]

WHERE MATCH(title) AGAINST ("Meise*" IN BOOLEAN MODE);

Using the wildcard character "*" finds both meise and meisen in the above example.

Alternatively

WHERE MATCH(title) AGAINST ("meise meisen");

 

[jodunno]

Hi requinix,

I'm actually happy that I can offer a basic search for taxonomy using a database. I am not an advanced programmer yet so building an indexer is not my goal. I simply desire a list of all of the species that i have photographed and identified via genitalia. I currently have over 1,100 species. Thus, i think that a simple keyword list with a link is good. I don't understand why you object to using a database for such a simple search other than because of memory consumption(?). I use Latin (scientific nomenclature), English and German languages. My reason for posting is because plural searches yield no results. Other than that, i am happy that i am getting results at all. I entered a few examples into a table for testing:

Turdus merula link/to/file, Turdus philomelos link/to/file, Blaumeise link/to/file, Kohlmeise link/to/file etc.

typing turdus brings up both merula and philomelos which is good. typing meise brings up both Blaumeise and Kohlmeise. However, typing meisen yields my no results catch (if empty($results))). I just wanted to be able to support plural forms.

I'm definitely interested in learning how to build a search engine and also in becoming an advanced coder. Such desires could become a reality one day but definitely not anytime so soon.

Best wishes, requinix.

Hi Barand,

That is great! Thank you for adding this data. I will recode my simple search query and enjoy the new results.

I was wondering if building a separate database for searches is a good idea or not. I say this because i think that i could make searching faster by adding a table for all letters of the alphabet. Then, take first letter of search term and narrow the field to a single table. Like a dictionary with lettered thumb indexes. Go right to the relevant letter and spare the database resource consumption. yes? no?

just an idea.

I have to go now. Best wishes to all!

[jodunno]

Hello again,

I've had an exhausting, stressful week but i managed to tinker with my simple name search feature. For some reason, MATCH AGAINST full-text searches failed me.

So i redesigned my table with id, title, link, keywords.

keywords is varchar(255), which contains a small list of names for a species in plural form as one word (no spaces). I also discovered the soundex() method of searching for misspelled word matches. I combined like and soundex to get the results that i was seeking.

so as an example of my current test code:

$title = (string) '%' . $_POST['search'] . '%';
$keyword = (string) '%' . str_replace(' ', '', $_POST['search']) . '%';
$stitle = $skeyword =  (string) '%' . soundex($_POST['search']) . '%';

$Query = 'SELECT title, link FROM taxonomy WHERE title LIKE :title or title LIKE :stitle or keywords LIKE :keyword or keywords LIKE :skeyword';

and an example of my table data:

title: Parus major
link: path/to/Parus/major
keywords: Parus major Kohlmeisen Coaltits soundex('Parus') soundex('major') soundex('Kohlmeisen') soundex('Coaltits') soundex('Kohl') soundex('meisen') soundex('coal')

now i wonder if this is too complicated and searches will slow down my website?
i just want to add a very basic/primitive/simple search feature.
i really don't like java and i am not interested in using the free java based search engines like Lucene, Solr etc.
besides, i am not indexing pages. I all ready maintain a manually edited.controlled species list.
i just want the species list to be searchable.

is this new code okay? any suggestions? any criticism?
call me a stupid loser for all i care, just please explain why this method is bad/negative and how could i make it better?

so far it is working well for me. I can type meisen and all entries appear. I can also type maisen and get the same results via soundex. I like it but i am nescient about this subject.

Thank you.

[Barand]

Soundex may not be that usefule EG

mysql> SELECT SOUNDEX('meise'), SOUNDEX('meisen');
+------------------+-------------------+
| SOUNDEX('meise') | SOUNDEX('meisen') |
+------------------+-------------------+
| M200             | M250              |
+------------------+-------------------+

According to the MySQL manual, this could be a problem in your case

Quote

Important

When using SOUNDEX(), you should be aware of the following limitations:

• This function, as currently implemented, is intended to work well with strings that are in the English language only. Strings in other languages may not produce reliable results.
• This function is not guaranteed to provide consistent results with strings that use multibyte character sets, including utf-8. See Bug #22638 for more information.

Your queries won't be able to use any indexes on the table, so be prepared to start the query before you go to bed and hope it's fininshed by breakfast.

• title LIKE 'meise%' - this will use an index
• title LIKE '%meise%' - will not use index and therefor search every record.

If your keywords are a list of words in a single column, they cannot be indexed.

[jodunno]

Hi Barand,

your opinion is trusted, naturally, i just wonder what you mean by finish by breakfast since my results are instantaneous on xampp. I assume that you mean lots of users? anyway, i heed your advice and remove the current code.

I have started over with full text and match against, like so:

CREATE TABLE taxonomy (id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(128), link VARCHAR(128), keywords TEXT, FULLTEXT (keywords)) ENGINE=InnoDB;

INSERT INTO taxonomy SET title = 'Cyanistes caeruleus', link = 'path/to/sp', keywords = CONCAT ('Cyanistes caeruleus blau meise meisen blaumeise blaumeisen blue tit tits bluetits bluechickadees chickadee chickadees',' ',soundex('Cyanistes'),' ',soundex('caeruleus'),' ',soundex('blau'),' ',soundex('meise'),' ',soundex('meisen'),' ',soundex('blaumeise'),' ',soundex('blaumeisen'),' ',soundex('blue'),' ',soundex('tit'),' ',soundex('tits'),' ',soundex('bluetits'),' ',soundex('bluechickadees'),' ',soundex('chickadee'),' ',soundex('chickadees'));

$keyword = (string) $_POST['suchworte'];
$skeyword =  (string) soundex($_POST['suchworte']);

$searchQuery = 'SELECT title, link FROM taxonomy WHERE match(keywords) against(:keyword) or match(keywords) against(:skeyword)';

it is working but i have to add all of the keywords and soundex values.

is this method better?

ps: i romanize German, id est, u with umlaut is ue. Thus, soundex is working just fine for me. I really have no complaints. I suppose that they mean non romanized languages?

Best wishes.

[Barand]

27 minutes ago, jodunno said:

i just wonder what you mean by finish by breakfast

I may be guilty of exaggeration. I was just saying that the queries are are going be as slow as they could get.

If you want to send me a dump of your full taxonomy table I'll experiment to see if I can find a way to make searches more efficient

[jodunno]

43 minutes ago, Barand said:

I may be guilty of exaggeration. I was just saying that the queries are are going be as slow as they could get.

If you want to send me a dump of your full taxonomy table I'll experiment to see if I can find a way to make searches more efficient

Hi Barand,

I don't think it is exaggeration since we are talking about internet usage. People/website users think a second too long is a second too long. I was just confused because my queries were working fast on xampp. Anyway, i've removed this code and started over.

the taxonomy table is very small for search testing. I now only have Cyanistes caeruleus in the table. I've started over. I will build a bigger table for experimentation and send it to you. Meantime, my website started out as static html/css. I decided to take it to the server side using php. I started studying php two years ago in my spare time. I have acquired alot of knowledge since then but i am still a novice. I just don't know how certain tasks are accomplished in this industry. Anyway, i have a species list for each major class. I use it on my publicly non-logged in pages for prospective members to see which species i have documented. I can also send a copy of these lists to you. I mean, if there is any significance in doing so. Otherwise, please allow me to build a bigger table. I will send the code and also a dump of the database for you. You would probably shriek if you saw my entire website code. LOL I use my own router based upon post methods. I do alot of things that coders hate. For example, i use the session to store image names for loading on a page so that images cannot be viewed unless the variable is set. You'd probably lay an egg looking at that code alone. I also keep my taxonomical navigator private with prg posts instead of uris with get requests. You'd probably faint if you saw it. So i will stick to the relevant code for critique.

I will build my table tommorrow. I am very tired today and i will go to bed soon.

Thank you for guiding me in the right direction. As a wanna-be coder, it is very nice to have a master coder offer advice and guidance. Really, i appreciate every word that you type to me.

Best wishes. Stay healthy and happy.

[Barand]

1 hour ago, jodunno said:

my results are instantaneous on xampp.

 

22 minutes ago, jodunno said:

I now only have Cyanistes caeruleus in the table.

The latter statement may well explain the former. Queries on a single record table tend to be at the faster end of the spectrum.

I was expecting you to have thousands of species.

[jodunno]

1 hour ago, Barand said:

I was expecting you to have thousands of species.

I have one thousand one hundred species documented (photos, genitalia, confirmation from experts, data and measurements). Building my site prevents me from working on nature.

Anyway, i don't use a database for retrieving species. I use alphabetic functions which return arrays to load each rank. I don't have actual php files per species. I use one file to load all species based upon the flat file functions. I avoid using a database for every little aspect of the site to avoid locking and overruse. My alphabetic/targeted approach is working faster than a db. I use the db to verify that you are a logged in user and match your db session id with your session id instead. In order to use a search, i'll have to build a search table.

I actually narrow the alphabetic approach to the first three letters of the target. So Aves will be found in function file A under function Ave. Lightning fast retrieval and better than a db.

Best wishes.

[Barand]

59 minutes ago, jodunno said:

I have one thousand one hundred species documented (photos, genitalia, confirmation from experts, data and measurements).

Bird porn?

[jodunno]

10 hours ago, Barand said:

Bird porn?

yes, i'm making a nature porn website. I'm hoping to have a million bird subscribers. 😄

I think that i'll call my site "feather fantasy". LOL

Birds are easy to id in most cases. Flies, spiders and beetles pose a big problem most of the time. If you ever start photographing nature you will learn that many insects cannot be identified by photo, which sucks. You may have an award winning photo but no id for the subject. Experts will often tell you species sp. I started studying this subject and i managed to teach myself how to dissect and identify my own subjects. Sometimes i need experts to confirm my id.

I will attach an example of fly porn: a male Helina pertusa

 

[jodunno]

I made a video of my site but it is 163mb and too large to attach to email from Google. I'll try to add two screen captures, nay, resized screen captures. I hope that they are small enough to attach here. I've spent alot of time on the front end trying to avoid the cookie cutter designs.

anytime i mention photos and trying to protect my hard work, people automatically think that my photo driven site is porn or something. LOL

I had some things/designs in mind when i began the site: photo backgrounds that can be disabled, background tint that can be changed and strength can be changed, language options, bookmarks (browsers have no business knowing what your customers are looking at. my pages are protected, so i offer a bookmarking system which also stops browsers from aiding yet more espionage), i wanted a search feature but i'm struggling with this concept, i recently imagined my sticky notes being private messages (which you all ready know about) and i wanted to keep all of my photos and data outside of the root directory. I've managed to accomplish most of these tasks on my own.

[jodunno]

I am trying to build the table of birds for search keywords and it is brutal. I've spent two hours now and i only have 15 species entered of 79. While building this list i wondered if it would be faster to make a large array in cpp and use that instead. Then i got an idea for a cache system. Instead of building a database, how about i build a cache folder with every possible keyword as a file with pre determined results? wouldn't that be fast and easy? so cache folder has meise.php meisen.php and soundexmeise.php and soundexmeisen.php since i am manually building this list i think it would be better than using a slow and sluggish database.

what do you think? search meise and if file exists searchterm.php then show file with manually entered matches.

[Barand]

Sounds like you are manully typing in the data when you already have it in your files. (Or are keywords a new addition?)

[Barand]

I decided to create a test table of my own. Only 133 records so far. I want several hundred before I try benchmark time tests on it so I'll generate a few hundred more dummy ones.

CREATE TABLE `taxonomy_base` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(128) DEFAULT NULL,
  `common` varchar(45) DEFAULT NULL,
  `link` varchar(128) DEFAULT NULL,
  `keywords` text,
  `percent` decimal(6,2) DEFAULT NULL,                          -- added a couple of columns on population
  `trend` varchar(45) DEFAULT NULL,                             -- increase/decline for a charting exercise 
  PRIMARY KEY (`id`),
  FULLTEXT KEY `keywords` (`keywords`)
) ENGINE=InnoDB AUTO_INCREMENT=134 DEFAULT CHARSET=utf8;

INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (1,'Cyanistes caeruleus','Blue tit','path/to/sp','Cyanistes caeruleus blau meise meisen blaumeise blaumeisen blue tit tits bluetits bluechickadees chickadee chickadees C5232 C642 B400 M200 M250 B452 B4525 B400 T000 T200 B432 B4232 C300 C320',NULL,NULL);
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (2,'Chloris chloris','Greenfinch',NULL,'chloris greenfinch C462 G651',-9.47,'strong decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (3,'Corvus monedula','Jackdaw',NULL,'corvus monedula jackdaw C612 M534 J230',2.79,'weak increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (4,'Falco tinnunculus','Kestrel',NULL,'falco tinnunculus kestrel F420 T552 K236',-3.99,'strong decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (5,'Emberiza schoeniclus','Reed Bunting',NULL,'emberiza schoeniclus reed bunting E516 S524 R300 B535',0.80,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (6,'Corvus frugilegus','Rook',NULL,'corvus frugilegus rook C612 F624 R200',-1.52,'weak decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (7,'Columba palumbus','Woodpigeon',NULL,'columba palumbus woodpigeon C451 P451 W312',-0.42,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (8,'Motacilla flava','Yellow Wagtail',NULL,'motacilla flava yellow wagtail M324 F410 Y400 W234',4.32,'strong increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (9,'Emberiza calandra','Corn Bunting',NULL,'emberiza calandra corn bunting E516 C453 C650 B535',-0.04,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (10,'Carduelis carduelis','Goldfinch',NULL,'carduelis goldfinch C634 G431',4.08,'strong increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (11,'Perdix perdix','Grey Partridge',NULL,'perdix grey partridge P632 G600 P636',-3.24,'strong decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (12,'Vanellus vanellus','Lapwing',NULL,'vanellus lapwing V542 L152',-6.12,'strong decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (13,'Carduelis cannabina','Linnet',NULL,'carduelis cannabina linnet C634 C515 L530',-0.21,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (14,'Alauda arvensis','Skylark',NULL,'alauda arvensis skylark A430 A615 S462',-2.18,'weak decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (15,'Sturnus vulgaris','Starling',NULL,'sturnus vulgaris starling S365 V426 S364',-1.72,'weak decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (16,'Columba oenas','Stock Dove',NULL,'columba oenas stock dove C451 O520 S320 D100',4.06,'strong increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (17,'Passer montanus','Tree Sparrow',NULL,'passer montanus tree sparrow P260 M535 T600 S160',4.14,'strong increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (18,'Streptopelia turtur','Turtle Dove',NULL,'streptopelia turtur turtle dove S361 T636 T634 D100',-21.41,'strong decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (19,'Sylvia communis','Whitethroat',NULL,'sylvia communis whitethroat S410 C552 W336',1.23,'weak increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (20,'Turdus merula','Blackbird',NULL,'turdus merula blackbird T632 M640 B421',-0.36,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (21,'Cyanistes caeruleus','Blue Tit',NULL,'cyanistes caeruleus blue tit C523 C642 B400 T300',-0.43,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (22,'Pyrrhula pyrrhula','Bullfinch',NULL,'pyrrhula bullfinch P640 B415',2.38,'weak increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (23,'Fringilla coelebs','Chaffinch',NULL,'fringilla coelebs chaffinch F652 C412 C152',-1.49,'weak decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (24,'Prunella modularis','Dunnock',NULL,'prunella modularis dunnock P654 M346 D520',0.01,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (25,'Parus major','Great Tit',NULL,'parus major great tit P620 M260 G630 T300',-0.80,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (26,'Sylvia curruca','Lesser Whitethroat',NULL,'sylvia curruca lesser whitethroat S410 C620 L260 W336',0.01,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (27,'Aegithalos caudatus','Long-tailed Tit',NULL,'aegithalos caudatus long-tailed tit A234 C332 L523 T300',-2.13,'weak decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (28,'Erithacus rubecula','Robin',NULL,'erithacus rubecula robin E632 R124 R150',-0.06,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (29,'Turdus philomelos','Song Thrush',NULL,'turdus philomelos song thrush T632 P454 S520 T620',-0.88,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (30,'Strix aluco','Tawny Owl',NULL,'strix aluco tawny owl S362 A420 T500 O400',0.64,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (31,'Troglodytes troglodytes','Wren',NULL,'troglodytes wren T624 W650',1.84,'weak increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (32,'Sylvia atricapilla','Blackcap',NULL,'sylvia atricapilla blackcap S410 A362 B421',6.93,'strong increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (33,'Phylloscopus collybita','Chiffchaff',NULL,'phylloscopus collybita chiffchaff P421 C413 C121',4.13,'strong increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (34,'Periparus ater','Coal Tit',NULL,'periparus ater coal tit P616 A360 C400 T300',-2.14,'weak decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (35,'Sylvia borin','Garden Warbler',NULL,'sylvia borin garden warbler S410 B650 G635 W614',-1.94,'weak decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (36,'Regulus regulus','Goldcrest',NULL,'regulus goldcrest R242 G432',0.09,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (37,'Dendrocopos major','Great Spotted Woodpecker',NULL,'dendrocopos major great spotted woodpecker D536 M260 G630 S133 W312',-0.27,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (38,'Picus viridis','Green Woodpecker',NULL,'picus viridis green woodpecker P220 V632 G650 W312',-1.70,'weak decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (39,'Garrulus glandarius','Jay',NULL,'garrulus glandarius jay G642 G453 J000',1.78,'weak increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (40,'Dendrocopos minor','Lesser Spotted Woodpecker',NULL,'dendrocopos minor lesser spotted woodpecker D536 M560 L260 S133 W312',-11.08,'strong decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (41,'Poecile palustris','Marsh Tit',NULL,'poecile palustris marsh tit P240 P423 M620 T300',-2.50,'weak decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (42,'Luscinia megarhynchos','Nightingale',NULL,'luscinia megarhynchos nightingale L250 M265 N235',5.04,'strong increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (43,'Sitta europaea','Nuthatch',NULL,'sitta europaea nuthatch S300 E610 N332',2.91,'strong increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (44,'Carduelis cabaret','Lesser Redpoll',NULL,'carduelis cabaret lesser redpoll C634 C163 L260 R314',2.58,'weak increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (45,'Phoenicurus phoenicurus','Redstart',NULL,'phoenicurus redstart P526 R323',6.20,'strong increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (46,'Accipiter nisus','Sparrowhawk',NULL,'accipiter nisus sparrowhawk A213 N220 S162',-1.11,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (47,'Muscicapa striata','Spotted Flycatcher',NULL,'muscicapa striata spotted flycatcher M221 S363 S133 F423',1.15,'weak increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (48,'Anthus trivialis','Tree Pipit',NULL,'anthus trivialis tree pipit A532 T614 T600 P130',4.98,'strong increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (49,'Certhia familiaris','Treecreeper',NULL,'certhia familiaris treecreeper C630 F546 T626',2.03,'weak increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (50,'Poecile montana','Willow Tit',NULL,'poecile montana willow tit P240 M535 W400 T300',-1.46,'weak decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (51,'Phylloscopus trochilus','Willow Warbler',NULL,'phylloscopus trochilus willow warbler P421 T624 W400 W614',-1.41,'weak decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (52,'Ficedula hypoleuca','Pied Flycatcher',NULL,'ficedula hypoleuca pied flycatcher F234 H142 P300 F423',2.77,'weak increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (53,'Phylloscopus sibilatrix','Wood Warbler',NULL,'phylloscopus sibilatrix wood warbler P421 S143 W300 W614',2.43,'weak increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (54,'Loxia curvirostra','Common Crossbill',NULL,'loxia curvirostra common crossbill L200 C616 C550 C621',-4.94,'strong decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (55,'Carduelis spinus','Siskin',NULL,'carduelis spinus siskin C634 S152 S250',-0.49,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (56,'Tetrao urogallus','Capercaillie',NULL,'tetrao urogallus capercaillie T360 U624 C162',-2.38,'weak decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (57,'Stercorarius parasiticus','Arctic Skua',NULL,'stercorarius parasiticus arctic skua S362 P623 A623 S000',-15.16,'strong decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (58,'Rissa tridactyla','Black-legged kittiwake',NULL,'rissa tridactyla black-legged kittiwake R200 T632 B424 K320',-2.66,'weak decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (59,'Uria aalge','Common guillemot',NULL,'uria aalge common guillemot U600 A420 C550 G453',3.15,'strong increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (60,'Phalacrocorax artistotelis','European shag',NULL,'phalacrocorax artistotelis european shag P426 A632 E615 S200',-4.39,'strong decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (61,'Larus marinus','Great Black-backed Gull',NULL,'larus marinus great black-backed gull L620 M652 G630 B421 G400',5.07,'strong increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (62,'Phalacrocorax carbo','Great cormorant',NULL,'phalacrocorax carbo great cormorant P426 C610 G630 C656',-1.78,'weak decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (63,'Larus argentatus','Herring Gull',NULL,'larus argentatus herring gull L620 A625 H652 G400',7.96,'strong increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (64,'Fulmarus glacialis','Northern fulmar',NULL,'fulmarus glacialis northern fulmar F456 G424 N636 F456',-2.54,'weak decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (65,'Sterna hirundo','Common Tern',NULL,'sterna hirundo common tern S365 H653 C550 T650',-5.28,'strong decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (66,'Sterna sandvicensis','Sandwich Tern',NULL,'sterna sandvicensis sandwich tern S365 S531 S532 T650',-3.96,'strong decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (67,'Sternula albifrons','Little Tern',NULL,'sternula albifrons little tern S365 A411 L340 T650',0.75,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (68,'Sterna paradisaea','Arctic Tern',NULL,'sterna paradisaea arctic tern S365 P632 A623 T650',2.88,'strong increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (69,'Alca torda','Razorbill',NULL,'alca torda razorbill A420 T630 R261',2.82,'strong increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (70,'Actitis hypoleucos','Common Sandpiper',NULL,'actitis hypoleucos common sandpiper A233 H142 C550 S531',-2.04,'weak decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (71,'Cinclus cinclus','Dipper',NULL,'cinclus dipper C524 D160',-0.08,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (72,'Mergus merganser','Goosander',NULL,'mergus merganser goosander M622 M625 G253',3.10,'strong increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (73,'Motacilla cinerea','Grey Wagtail',NULL,'motacilla cinerea grey wagtail M324 C560 G600 W234',-3.04,'strong decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (74,'Cettia cetti','Cetti\'s Warbler',NULL,'cettia cetti cetti\'s warbler C300 C300 C320 W614',1.91,'weak increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (75,'Emberiza schoeniclus','Reed Bunting',NULL,'emberiza schoeniclus reed bunting E516 S524 R300 B535',-2.40,'weak decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (76,'Acrocephalus scirpaceus','Reed Warbler',NULL,'acrocephalus scirpaceus reed warbler A262 S612 R300 W614',-1.52,'weak decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (77,'Acrocephalus schoenobaenus','Sedge Warbler',NULL,'acrocephalus schoenobaenus sedge warbler A262 S515 S320 W614',-4.52,'strong decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (78,'Fulica atra','Coot',NULL,'fulica atra coot F420 A360 C300',-3.87,'strong decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (79,'Podiceps cristatus','Great Crested Grebe',NULL,'podiceps cristatus great crested grebe P321 C623 G630 C623 G610',-2.82,'strong decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (80,'Tachybaptus ruficollis','Little Grebe',NULL,'tachybaptus ruficollis little grebe T211 R124 L340 G610',0.11,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (81,'Anas platyrhynchos','Mallard',NULL,'anas platyrhynchos mallard A520 P436 M463',-0.98,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (82,'Gallinula chloropus','Moorhen',NULL,'gallinula chloropus moorhen G454 C461 M650',-3.81,'strong decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (83,'Aythya fuligula','Tufted Duck',NULL,'aythya fuligula tufted duck A300 F424 T133 D200',-1.21,'weak decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (84,'Numenius arquata','Curlew',NULL,'numenius arquata curlew N552 A623 C640',-0.78,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (85,'Vanellus vanellus','Lapwing',NULL,'vanellus lapwing V542 L152',-3.42,'strong decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (86,'Egretta garzetta','Little Egret',NULL,'egretta garzetta little egret E263 G623 L340 E263',4.76,'strong increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (87,'Cygnus olor','Mute Swan',NULL,'cygnus olor mute swan C252 O460 M300 S500',-0.27,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (88,'Tringa totanus','Redshank',NULL,'tringa totanus redshank T652 T352 R325',0.12,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (89,'Gallinago gallinago','Snipe',NULL,'gallinago snipe G452 S510',-0.64,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (90,'Anas crecca','Teal',NULL,'anas crecca teal A520 C620 T400',5.85,'strong increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (91,'Motacilla flava','Yellow Wagtail',NULL,'motacilla flava yellow wagtail M324 F410 Y400 W234',-12.79,'strong decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (92,'Ardea cinerea','Grey Heron',NULL,'ardea cinerea grey heron A630 C560 G600 H650',-1.47,'strong decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (93,'Alcedo atthis','Kingfisher',NULL,'alcedo atthis kingfisher A423 A320 K521',-2.12,'weak decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (94,'Haematopus ostralegus','Oystercatcher',NULL,'haematopus ostralegus oystercatcher H531 O236 O236',0.00,'weak decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (95,'Riparia riparia','Sand Martin',NULL,'riparia sand martin R160 S530 M635',0.00,'weak decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (96,'Recurvirostra avosetta','Avocet',NULL,'recurvirostra avosetta avocet R261 A123 A123',0.93,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (97,'Panurus biarmicus','Bearded Tit',NULL,'panurus biarmicus bearded tit P562 B652 B633 T300',5.22,'strong increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (98,'Chroicocephalus ridibundus','Black-headed Gull',NULL,'chroicocephalus ridibundus black-headed gull C622 R315 B423 G400',0.85,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (99,'Buteo buteo','Buzzard',NULL,'buteo buzzard B300 B263',0.69,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (100,'Corvus corone','Carrion Crow',NULL,'corvus corone carrion crow C612 C650 C650 C600',0.79,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (101,'Emberiza cirlus','Cirl Bunting',NULL,'emberiza cirlus cirl bunting E516 C642 C640 B535',3.81,'strong increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (102,'Streptopelia decaocto','Collared Dove',NULL,'streptopelia decaocto collared dove S361 D223 C463 D100',-2.81,'strong decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (103,'Crex crex','Corncrake',NULL,'crex corncrake C620 C652',-0.82,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (104,'Cuculus canorus','Cuckoo',NULL,'cuculus canorus cuckoo C242 C562 C200',2.53,'weak increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (105,'Sylvia undata','Dartford Warbler',NULL,'sylvia undata dartford warbler S410 U533 D631 W614',-10.24,'strong decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (106,'Regulus ignicapilla','Firecrest',NULL,'regulus ignicapilla firecrest R242 I252 F626',21.77,'strong increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (107,'Anas strepera','Gadwall',NULL,'anas strepera gadwall A520 S361 G340',3.66,'strong increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (108,'Pluvialis apricaria','Golden Plover',NULL,'pluvialis apricaria golden plover P414 A162 G435 P416',-0.69,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (109,'Anser anser','Greylag Goose',NULL,'anser greylag goose A526 G642 G200',3.05,'strong increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (110,'Circus cyaneus','Hen Harrier',NULL,'circus cyaneus hen harrier C622 C520 H500 H660',-3.23,'strong decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (111,'Falco subbuteo','Hobby',NULL,'falco subbuteo hobby F420 S130 H100',-4.82,'strong decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (112,'Corvus cornix','Hooded Crow',NULL,'corvus cornix hooded crow C612 C652 H330 C600',3.17,'strong increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (113,'Delichon urbicum','House Martin',NULL,'delichon urbicum house martin D425 U612 H200 M635',-1.62,'weak decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (114,'Passer domesticus','House Sparrow',NULL,'passer domesticus house sparrow P260 D523 H200 S160',0.37,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (115,'Pica pica','Magpie',NULL,'pica magpie P200 M210',0.51,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (116,'Anthus pratensis','Meadow Pipit',NULL,'anthus pratensis meadow pipit A532 P635 M300 P130',2.92,'strong increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (117,'Larus melanocephalus','Mediterranean Gull',NULL,'larus melanocephalus mediterranean gull L620 M452 M336 G400',3.72,'strong increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (118,'Turdus viscivorus','Mistle Thrush',NULL,'turdus viscivorus mistle thrush T632 V216 M234 T620',-1.79,'weak decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (119,'Falco peregrinus','Peregrine',NULL,'falco peregrinus peregrine F420 P626 P626',0.44,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (120,'Motacilla alba','Pied/White Wagtail',NULL,'motacilla alba pied/white wagtail M324 A410 P330 W234',1.06,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (121,'Aythya ferina','Pochard',NULL,'aythya ferina pochard A300 F650 P263',3.87,'strong increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (122,'Coturnix coturnix','Quail',NULL,'coturnix quail C365 Q400',-9.40,'strong decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (123,'Corvus corax','Raven',NULL,'corvus corax raven C612 C620 R150',1.61,'weak increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (124,'Lagopus lagopus scotica','Red Grouse',NULL,'lagopus scotica red grouse L212 S320 R300 G620',5.44,'strong increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (125,'Milvus milvus','Red Kite',NULL,'milvus red kite M412 R300 K300',13.15,'strong increase');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (126,'Mergus serrator','Red-breasted Merganser',NULL,'mergus serrator red-breasted merganser M622 S636 R316 M625',-2.63,'weak decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (127,'Tadorna tadorna','Shelduck',NULL,'tadorna shelduck T365 S432',-1.46,'weak decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (128,'Anas clypeata','Shoveler',NULL,'anas clypeata shoveler A520 C413 S146',1.01,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (129,'Saxicola rubicola','Stonechat',NULL,'saxicola rubicola stonechat S224 R124 S352',-8.24,'strong decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (130,'Hirundo rustica','Swallow',NULL,'hirundo rustica swallow H653 R232 S400',-1.38,'weak decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (131,'Apus apus','Swift',NULL,'apus swift A120 S130',-4.83,'strong decline');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (132,'Saxicola rubetra','Whinchat',NULL,'saxicola rubetra whinchat S224 R136 W523',-0.34,'no change');
INSERT INTO `taxonomy_base` (`id`,`title`,`common`,`link`,`keywords`,`percent`,`trend`) VALUES (133,'Lullula arborea','Woodlark',NULL,'lullula arborea woodlark L440 A616 W346',4.67,'strong increase');

 

[Barand]

Beware of difference between MySQL soundex() and PHP soundex() results when creating and querying the data

echo soundex('sterna');        // ==> S365
echo soundex('sternus');       // ==> S365
echo soundex('sternula');      // ==> S365

mysql> select SOUNDEX('sterna'), SOUNDEX('sternus'), SOUNDEX('sternula');
+-------------------+--------------------+---------------------+
| SOUNDEX('sterna') | SOUNDEX('sternus') | SOUNDEX('sternula') |
+-------------------+--------------------+---------------------+
| S365              | S3652              | S3654               |
+-------------------+--------------------+---------------------+

 

[jodunno]

5 hours ago, Barand said:

Sounds like you are manully typing in the data when you already have it in your files. (Or are keywords a new addition?)

i only maintain a latin (scientific) name with a German translation (if one exists in vernacular form). Alot of insects do not have a common name. I live in Berlin, so German is the default language of the system. I wanted to add English since it is my native language but it does not exist in my flat files at this time. So English is being added to search table. I try to add both American and British English whenever possible. I suppose that it might be easier to use my flat files for this data?

I will zip some of my code for you to look at, so you can see how my site is working. Code will answer more questions than i can answer. I'll show you how my router works based upon the flat file system. I was going to convert it to a db but it was easier to continue with a functional approach because php has more power than sql. For example, if else. I kept the functional approach for this reason and because i am really new to database design. I can only issue basic commands using sql. join is beyond me right now. But again, my functions work very well and quickly and i have more power over the data with php. Thus, i keep using them instead of a db.

originally, i had folders and files (static pages of html/css only). I experimented with php and ended up with a routing system which depends upon arrays. I never tweaked the arrays to something smaller and easier like numbers. My goal was to eliminate files and folders. I've accomplished this goal.

Anyway, many keywords are not in the arrays used to fill the template with selections. I'd have to add multi-dimensional arrays for other languages.

it may be easier to show you code. I'll email some scriots to you, if it is okay to do so.

[Barand]

I have been experimenting with three different configurations of your data.

Version 1 - as you have have it now.

Version 2 - remove the keywords column and put keywords in separate table, one keyword per row (normalized) with index on keyword column.

Version 3 - as version 2 but with keyword in one column and soundex(keyword) in a separate column (indexes on both columns)

I still only have the 133 rows so I performed searches for 400 keywords 5 times on each configuration. Each set of 2000 searches (on a sluggish SQL database?) took less than a second. Version 3 was consistently fastest.

Times for 2,000 keyword searches

Version 1 : 0.949888 seconds
Version 2 : 0.872437 seconds
Version 3 : 0.615272 seconds

Version 3:

+------------------+
|  taxonomy        |
+------------------+
|  id              |---------+         +--------------+
|  title           |         |         |  keyword     |
|  common          |         |         +--------------+
|  link            |         |         |  kw_id       |
|  id              |         +--------<|  tax_id      |
+------------------+                   |  kword       |
                                       |  skword      |
                                       +--------------+
                                       
SELECT id, title, common
FROM taxonomy_1 t 
    JOIN keyword_2 k ON t.id = k.tax_id 
WHERE 
    kword = 'strna' 
    OR skword = 'S365';
    
+----+---------------------+---------------+
| id | title               | common        |
+----+---------------------+---------------+
| 15 | Sturnus vulgaris    | Starling      |
| 65 | Sterna hirundo      | Common Tern   |
| 66 | Sterna sandvicensis | Sandwich Tern |
| 67 | Sternula albifrons  | Little Tern   |
| 68 | Sterna paradisaea   | Arctic Tern   |
+----+---------------------+---------------+

 

[jodunno]

you really are a fantastic programmer. You work very quickly, so it is obvious that coding is second nature to you. I have alot to learn before i could reach such a level. I cannot thank you enough for spending time on this subject. I was pulling my hair out trying to get it to work. Your code makes much more sense.

I will add my species list to your table design and try it out tomorrow. I'm going to bed soon. I've sent a zip file to you with some of my routing code that uses my taxonomical arrays. The router just explodes the path to check for a rank. I then use a rank and name array so the page knows what it is (latin and vernacular). I believe my arrays are actually called $yourRank and $yourName. So each traversal through the file system can ask what it is and recieve an answer. I can show you the code if you need to see how it works.

Anyway, i'll try this tomorrow. Sleep well, Barand and please stay healthy. I hate this corona stuff that we all have to deal with.