Query XML data type : SOLUTION

What a blessed morning today.. All praises due to Allah.. ^_^

Click here to read previous entry on query XML data type and what drive me to that. So, without further delay, here is the solution :





See??? As simple as that. The key is EXTRACTVALUE().

I'm proudly declare, that I'm ready for any type of reports they want me to generate. Oh... how I wish I could find this earlier... It's OK. Better late than never. Keep learning.. ;) 

Query XML data type

First, let me share the background story. Books were written in various language. In our library, there are book in Malay, Arabic, English and etc. The most is Arabic I think. The information of a book such as title, author, ISBN, barcode, language, subject, call number and so on were kept in MARC. MARC is Machine Readable Catalog. I will share on MARC later. MARC were kept in database in a field with longtext data type in an XML format.

 So, my task is to generate a statistic based on item's language.

  

I did successfully produce the statistic by manipulationg some PHP pre-defined function such as explode.. *_*

  

However, when it comes to huge data for example statistic for every item in the library (currently our library hold more than 50 000 items and a lot more thousands to come ) .... it stop!!! :( 

To access more than 50 000 MARCs just to get that tiny info each. Huh.... I know it must  a better and clever way to do it precisely and most important is the query performance. I'm keen to know 'how to query XML data type' even I'm not sure does it really help. At least I try. Once I got the solution, I will definitely paste it here.. 

Ok.. work hard, pray a lots..

Overdue Item that aren’t yet overdue

Currently we are using KOHA version 3.10. Previous version used is 3.06.

Problem reported :
Tarikh hari ini jadi "Over Due". Sepatutnya tarikh selepas itu iaitu esok.
 
Cause
 
 
 
Solution
I'm not sure whether this is the right solution. The ideas are :

1. Update data type for field date_due from date to datetime
2. Update records for date_due >= today, set the date as it is plus time 23:59 (Just to rectify existing records with 00:00 time. Dont bother about record with date_due < today, they are no doubt overdue.)
3. Done

As for now, it works well. The following incoming record will automatically record date_due in datetime format.

Alhamdulillah. Case settle! Yeayy... lompat bintang.... ;P