Jump to content

TeNDoLLA

Members
  • Posts

    762
  • Joined

  • Last visited

    Never

Profile Information

  • Gender
    Male
  • Location
    Finland

TeNDoLLA's Achievements

Newbie

Newbie (1/5)

0

Reputation

  1. Seems like the problem lies in that there was occurence of the word 'test' in both of the originals and in both of the translations I was testing with, and that is why I get those double results with that query. Tested now with defferent values for example 'car -> auto' and 'carbon -> hiili' and used search '%car%' and it owrks liek it should. This is the current query now. Dunno then if there will be someday again multiple occurences in both translations and originals, it will probably get fucked up SELECT t.translation_id AS translation_id, t.translation AS translation, o.translation AS original, o.translation_id AS original_id FROM test2_common_translations t JOIN test2_common_translations o ON o.constant = t.constant WHERE t.lang_id = 3 AND o.lang_id = 1 AND (t.translation LIKE '%car%' OR o.translation LIKE '%car%')
  2. Hmms. It is the same table that holds the translations and the english translation is sued as the "original". So the the "main" table and the joined table are kinda both the main table. I am sure this can be done other way also but I can't just figure it out. Say we have a word car, it has some constant. There is car for 3 different languages and the constant is the same for all the rows.
  3. Here: http://imageshack.us/photo/my-images/35/explainresults.png/
  4. Hi, I have a table for word translations and I would need to fetch the original word/translated word -pairs for some language based on users search from the table. I managed to create a query that does it, but the problem is this query takes way too long to execute (around 200ms). I tried removing the BINARY keyword from the query, but then I get the results doubled. Select distinct does not work either because all the rows are distinct. The question is is there a way I could get the same results as with the query below but FASTER. Or is there way to use the query with the BINARY keywords and modify it somehow to run faster? Here is the query SELECT translations.translation AS translation, translations.translation_id AS translation_id, originals.translation AS original, originals.translation_id AS original_id, translations.locked FROM test2_common_translations translations JOIN test2_common_translations originals ON BINARY translations.constant = BINARY originals.constant WHERE translations.lang_id = 1 AND originals.lang_id = 3 HAVING translation LIKE "%test%" OR original LIKE "%test%" ORDER BY original DESC LIMIT 0, 20 And here is the table structure CREATE TABLE `test2_common_translations` ( `translation_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, `lang_id` int(11) UNSIGNED NOT NULL COMMENT 'links to language table', `constant` varchar(255) NOT NULL, `translation` varchar(255) NOT NULL DEFAULT '', `description` varchar(255) NOT NULL DEFAULT '', `locked` tinyint(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Shows if the data is locked for editing', PRIMARY KEY (`translation_id`), KEY `translation` (`translation`), KEY `translation_2` (`translation`,`constant`), KEY `i_constant` (`constant`), KEY `i_lang_id` (`lang_id`), CONSTRAINT `test2_common_translations_ibfk_1` FOREIGN KEY (`lang_id`) REFERENCES `test2_language` (`lang_id`) ON UPDATE CASCADE ) ENGINE=InnoDB; The results what i get now with this slow query is something like this for example translated to finnish language: translation | translation_id | original | original_id | locked omena | 1 | apple | 2 | 0 auto | 3 | car | 4 | 0 If I remove the BINARY keywords the query runs fast but the result is this: translation | translation_id | original | original_id | locked omena | 1 | apple | 2 | 0 auto | 3 | apple | 2 | 0 omena | 1 | car | 4 | 0 auto | 3 | car | 4 | 0 I would need to optimize this query somehow and getting the desired results, anyone can help? Would appreciate a lot, thanks.
  5. Sorry I couldn't resists. What is a long weekend? Longer than normal weekends? I wish i had these...
  6. It will not be a forum made by you if someone else codes the features in it for you, right? If you got some code of your own and problems getting things working paste it here and tell us what the problem is.
  7. You can get the JS script to connect your server/db with AJAX.
  8. I guess this could have been done easiest with some sort of bubblegum loop in PHP, but I wanted to try and do it in SQL. I guess I was thinking too complicated and tried to make it in loop inside a stored procedure. Problem was solved with just a simple joins, probably not the best way but did what I wanted it to do. /* Query to get all synonyms from the old table to the new synonyms table */ INSERT INTO new_synonyms (item_id, item_name) SELECT item_id, item_name FROM old_synonyms; /* Below is queries to get all the note translations from the old synonym table to the new synonym note translations table. */ /* for finnish notes */ INSERT INTO new_synonym_notes (synonym_id, lang_id, note) SELECT t2.synonym_id, 1, s.synonym_note_fin FROM new_synonyms t2 JOIN old_synonyms s ON s.item_name = t2.item_name; /* for english notes */ INSERT INTO new_synonym_notes (synonym_id, lang_id, note) SELECT t2.synonym_id, 3, s.synonym_note_eng FROM new_synonyms t2 JOIN old_synonyms s ON s.item_name = t2.item_name; /* for swedish notes */ INSERT INTO new_synonym_notes (synonym_id, lang_id, note) SELECT t2.synonym_id, 4, s.synonym_note_swe FROM new_synonyms t2 JOIN old_synonyms s ON s.item_name = t2.item_name;
  9. $newdate = date("D, j F, Y", strtotime('+30 days', $date)); echo $newdate; ... assuming $date is a timestamp.
  10. If you do var_dump($_POST['name_of_textarea']); instantly after posting the form before doing anything to the value does it really give you that as a value? Since mysql_real_escape_string should not produce a string like that.
  11. Not sure what you trying to achieve with this line $addition = strtotime($addtiondate) > strtotime($newdate); but $addition variable will have a boolean value based on the condition. In this case it will be "true" since the $additiondate is greater than $newdate.
  12. This $TodaysDate = date("Y.m.d"); should probably be $TodaysDate = date("Y-m-d"); so the date will be in the correct format (YYYY-MM-DD, not YYYY.MM.DD) for the mysql field.
  13. Hello, I need help moving data between the old db structure and the new db structure. Old db structure (1 table): table synonyms ---------------- synonym_id (auto_increment) item_id item_name note_fin note_eng note_swe New db structure (2 tables, 3 tables if we count the language table also, which defines the lang_id): table synonyms ---------------- synonym_id (auto_increment) item_id item_name table synonym_notes ---------------------- synonym_note_id (auto_increment) synonym_id note lang_id The problem is how to move with SQL commands the old data from one old table to the 2 new tables. I should get item_id and item_name from the old table to the new synonyms table. I should also move the corresponding notes to the new synonym_notes table for the 3 languages in a way that the synonym_id in the synonym_notes table refers to the synonym_id in the new synonyms table for the corresponding items. lang_id's in the new structure are like: fin=1, eng=3, swe=4. Anyone can help me with this transition?
  14. You should find out what functions are and how they work. What means declaring a function and what means using it after it has been declared. After you understand these things you can continue working on the script. When you understand these things, you find the answer for the error in this thread already.
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.