Question about Keywords

Help
timt
2010-03-05
2013-05-28
  • timt

    timt - 2010-03-05

    Greetings again!

    After a hiatus, I am back to working on my refbase installation.

    I would like to modify the keyword browsing tool in order to produce something more like the drop-down menu for publications on the advanced search screen. Right now, however, the keyword browsing tool just displays the whole string of keywords that is stored for each record in the database. I would like to be able to sort my keywords individually, however. My keywords are bilingual, so I would like to be able to list them one after another in language pairs.

    Should I create a new MySQL table with separate fields for each keyword? If so, how would I reference that table via PHP to create a drop-down menu?

    I don't know if I'm being very clear, but any help you could provide would be most appreciated.

    Thanks!

    Tim

     
  • Matthias Steffens

    Hi Tim,

    sorry for the late reply. It's a shame that I haven't yet completed the browse feature. Back when I started implementing this, I did face some speed & database issues which I wasn't able to figure out at that time. Since then, other features were always more important, so it didn't get finished. :-/

    Should I create a new MySQL table with separate fields for each keyword?

    Yes, probably. At least, this was the approach I was playing with previously. To facilitate this, I hacked together two functions: function 'createNewTableWithParsedTableData()' and function 'buildRefTableAndFieldNames()' (both located at the bottom of file 'includes/include.inc.php'). It's a long time ago since I've tried these functions, so they might require an update. But it may give you a start.

    If so, how would I reference that table via PHP to create a drop-down menu?

    You'd probably need to create a new function that queries the 'ref_keywords' table and populates the drop-down menu. For the browse feature to work, you'd need some MySQL queries that make use of JOINs:

    Given a user 'msteffens@…' and the tables 'ref_keywords' and 'ref_user_keys' exist (they'd hold all the individual keywords) you might use queries similar to these:

    1) If no user-specific fields are included in the query:

    SELECT ref_keyword AS keyword, COUNT(*) AS records FROM ref_keywords LEFT JOIN refs ON serial = ref_id WHERE location RLIKE "msteffens" GROUP BY ref_keyword ORDER BY records DESC, ref_keyword;

    SELECT ref_keyword AS keyword, COUNT(*) AS records FROM refs, ref_keywords WHERE serial = ref_id AND location RLIKE "msteffens" GROUP BY ref_keyword ORDER BY records DESC, ref_keyword;

    2) If user-specific fields are included in the query:

    SELECT ref_user_key AS user_key, COUNT(*) AS records FROM refs, ref_user_keys LEFT JOIN user_data ON serial = record_id AND user_id = 2 WHERE serial = ref_id AND user_keys RLIKE "my key" AND location RLIKE "msteffens" GROUP BY ref_user_key ORDER BY ref_user_key;

    SELECT ref_user_key AS user_key, COUNT(*) AS records FROM refs, ref_user_keys, user_data WHERE serial = record_id AND user_id = 2 AND serial = ref_id AND user_keys RLIKE "my key" AND location RLIKE "msteffens" GROUP BY ref_user_key ORDER BY ref_user_key;

    I'm sure these queries could be improved, and I'd be very grateful for any suggestions.

    Alternatively to the above, if you're more interested in the drop-down menu (and less so in the full-blown browse view), one could probably use the ability of 'opensearch.php' to return search suggestions:

    http://www.refbase.net/index.php/OpenSearch_service#Search_suggestions

    As an example, here are two URLs that return keywords in HTML or JSON format:

    http://beta.refbase.net/opensearch.php?query=keywords=ice&operation=suggest&recordSchema=json
    http://beta.refbase.net/opensearch.php?query=keywords=ice&operation=suggest&recordSchema=html

    You'd need to parse the returned output and populate the drop-down menu.

    I think this would be much easier to implement so I recommend to try this latter approach first.

    HTH, Matthias

     
  • timt

    timt - 2010-04-09

    Hello again, and thanks for your previous suggestions. I am still working on a database-driven solution to the keyword browse feature, though my very limited PHP skills are not helping me. . . .

    I wasn't quite able to get my head around the prototype functions you suggested above, so I decided to try creating two new database tables. The first table ("terms") has a "term" field for my keywords, which I imported as individual keywords (one per row). It  also has a "term_id" field as its primary key. The second table ("refs_terms") is an intermediary table that has the "term_id" field plus the "serial" field in order to JOIN to the "refs" table.

    I tested the following query in phpmyadmin, and it seems to produce the desired result:

    SELECT term AS keyword, COUNT(*) AS records 
    FROM terms 
    LEFT JOIN refs_terms ON refs_terms.term_id = terms.term_id
    WHERE serial RLIKE ".+"
    GROUP BY term ORDER BY records DESC, keyword
    

    Does this sound like a workable basis for a modified browse view? If so, then how can I change the source code to be able to query these new tables-since currently "refs" and "user_data" are the only tables that can be queried or JOINed, correct? I need to be able to modify the FROM clause and insert a custom INNER JOIN. . . .

    I have been able to get this far with my query by trying to modify the current refbase "browse" view:

    SELECT term AS keyword, COUNT(*) AS records FROM refs WHERE serial RLIKE ".+" GROUP BY term AS keyword ORDER BY records DESC, term AS keyword
    

    Obviously, this query triggers an error message, since "term" is not a valid field in "refs":

    Error 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS keyword ORDER BY records DESC, term AS keyword' at line 1

    Thanks again for your help, and patience!

    Tim

     
  • timt

    timt - 2010-04-09

    I think I meant "custom LEFT JOIN," above, rather than "INNER JOIN."

    Tim

     
  • timt

    timt - 2010-04-09

    And the following query seems to give me what I would want to display when clicking on the "Show" link next to each keyword. Here, "521" is the "term_id" for the keyword "fontes."

    SELECT author, title, year, publication, volume, pages 
    FROM refs 
    LEFT JOIN refs_terms ON refs_terms.serial = refs.serial 
    WHERE term_id = 521
    ORDER BY author, year DESC
    
     

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks