I'm working with IBM DB2, PHP, and html/css/js.
I've written an application that takes user input and returns a document with data from the DB2 database. My application has a loose MVC structure and most files are object-oriented.
The program flow / user experience map starts at index.php, where the user types the code for the document they'd like to receive, then specifies the type of document they're requesting, and the environment (production or test) they'd like to pull from.
What I would like to do is to create a basic AJAX script that will give suggestions for the code the user is typing -- I've worked with AJAX before, and there are plenty of tutorials on writing basic search-suggestion/autocomplete scripts. What I am having trouble with, however, is figuring out when to generate/update my XML file.
I know that I can use PHP to write an XML file (and I can write a basic sql query to fetch document names from the database, which is all that needs to exist in said XML file). However, I do not know how to make that file refresh.
At first, refreshing the XML every time someone submitted a query seemed like the best option. Sadly, this would not work with expected user behavior, where the first query after a new document has been added to the database will likely be for that new document (meaning that the XML wont be refreshed before the users query for it, meaning that the autocomplete will never be useful).
The other option is to refresh the file every few seconds, which seems a little overkill (and feels like a waste of system resources). I don't actually know how heavy of an operation writing XML is -- if that would be the bulk of the processing, I could check for changes by storing data from the last execution in an array and checking it against the new one. However, with the nature of the documents we're using, every execution will eventually be searching through thousands of rows. If the processing time to refresh the file exceeds the time between each function execution (which I would set somewhere between 5-60 seconds), well, things won't be pretty.
The last option is to find a way to call my code every time someone adds a new document. This makes the most sense, but would require adding functionality to the program that is used to add new documents (which comes from a third party) rather than being something I can control with my application alone. I will be looking into that and will update this post if it turns out to be the ideal solution.
To summarize my question -- what's the best way to keep an updated list of data, in an XML file, that represents one column from a database? Should I go ahead and put all my focus on the last option? Is there a better way to keep track of when something in the database changes?
(Hopefully) final edit / solution:
As it turns out, the user experience does not start at the index page. Most users will not be using the search box for their first visit to a new document.
My coworker has already added a button in the third-party application that takes users straight to the document's page in my application, which means that:
I can generate / update the xml file when the page is loaded for the first time, and the search box (which is actually more likely to be used on subsequent visits) will be able to give the appropriate suggestions when it is used to access the document later without opening any third-party applications.