We have clients do various assessments and questionnaires online, and all data is stored in a mysql database. It would be fantastic if we could create a HUD that would query the database and produce links to the online reports that get generated.

I've got a working sql query on the site, but I'd much rather have it linked to the client within DL, and a HUD seems the perfect way to go.

Is this possible? Any hints? (e.g., can a HUD handle some php code within the html template?) Or is this more suited to a plugin?

Thanks,
Scott

Hi Scott.

One very key and fundamental concept about the HUD widgets is that they are HTML pages rendered in a webkit view, which is basically a little mini web browser. Each widget has it's own "web browser" space to live in.

With this in mind, you could pretty much do anything you want to do that you could do in a web browser.

So, if you created web based access to your mysql database (i.e. through a CGI program returning HTML derived from your DB data), then you could most certainly do what you are looking for.

In fact, you can take this idea one step further, and using an HTML meta refresh command you can pass data to your CGI about the current object you are looking at in Daylite.
For example, you could pass the contacts email address to the CGI and use that to do specific queries in your mysql database.

I hope this helps and doesn't overwhelm. But really, once you wrap your head around this technique you can do some really amazing things!!
Michael.

Thanks Michael - I'm very excited about the possibilities! I'm going to try to figure out how to do what I'm looking for... I may end up back here with specific questions if/when I get stuck!

Scott

No problem.
This will be a great place to discuss issues/ideas/problems as you and others attempt to leverage Daylite add-ons!

Good luck and keep us posted!

Looks like I need to come back for help already! (I'm not actually a coder, just self-taught enough to usually be able to tweak existing php/mysql/html stuff - so this is a turning out to be bit of a challenge.)

I've learned enough so far to relize the best thing will probably be to have the HUD present a list of the completed assessments for the contact with links to the actual assessment reports on the website, since each client does several -- otherwise it would require a separate HUD for each assessment, more complicated mysql queries, and more space than is really sensible for a HUD.

To generate a url for an assessment report requires pulling data from several mysql tables. I've got a mysql query that will do the trick, with one of the variables being a client's website username, which we store in DL in a custom form for the project.

The mysql query that I know works (tested by manually entering a value in place of the username field):

SELECT CONCAT('<a href=http://online.trimergence.com/index2.php?option=com_feedback&sqid=',
 t2.stored_questionnaire_ID, '>', t1.questionnaire_name,'</a>', ' completed on ',
 t2.stored_questionnaire_date) AS Link FROM qst_questionnaires AS t1, qst_stored_questionnaires
 AS t2, mos_users AS t3 WHERE t1.questionnaire_ID = t2. questionnaire_ID AND t2.user_id = t3.id AND
 t3.username = '<$client username as stored in custom form$>' ORDER BY t2.stored_questionnaire_date DESC

[it seems the "code" function doesn't wrap what's placed inside - so I've edited to add carriage returns to the query so it displays reasonably]


So, some questions:

1. can I pull data from a custom form to have added to the mysql query?
1a. if yes, what's the code needed?
1b. if no, would using an "extra" field for the project work?

2. can I include all of the html/php/mysql in the HUD template? or does it need to reside on the server and then just call the page from the template using the url?
2a. if the php code has to be in a document on the webserver, how do I pass the DL data to the mysql query in that php code (it's not part of the url as in the stock.quote example)

3. Any other ideas/suggestions for how I might go from the above mysql query to a HUD displaying the results of the query?

Thanks,
Scott

Testing/exploring/playing has shown me that I can't just take the php code that would work on my website and put it in the HUD template. So, I've got a working php page on my site that lists the completed assessments the way I want, only using a "static" username at the moment.

So, I guess the only remaining question is:
How do I pass the username from DL (which is stored either in a custom form or an "extra" field) to the mysql query that is embedded in the php on the website page?

I guess I shouldn't post until I've really given up... I managed to figure it out (thanks, in part, to the online PHP manual).

I'll post the solution here just in case it might be instructive for anyone else fiddling with this sort of thing.

I added

?user=<$model.extra1$>

to the url in the HUD template, and then added

$user = $_GET['user'];

to the PHP so that the value of extra1 gets passed to $user which is in the mysql query.

Now, since we've been using a custom form to capture the username, I'd still be interested if there's a way to get the custom form data into the HUD template, as it would save a lot of work copying them over into extra1.

quote:

Originally posted by Scott McCulloch:

Now, since we've been using a custom form to capture the username, I'd still be interested if there's a way to get the custom form data into the HUD template, as it would save a lot of work copying them over into extra1.

Thanks to Andy and Michael in this thread:
http://forums.marketcircle.com/eve/forums/a/tpc/f/8741071343/m/4771052743
I've got it working perfectly pulling from the custom form.