Code to create list of completed forms based on User id?

[SineMacula]

Using Joomla! 1.5...

I'm planning to use Chronoforms to collect data for a research project, creating several instruments as separate forms. I am including user id as one of the fields in each of the forms.

What I want to be able to do is create a list of the forms that have already been submitted by the logged in user - so I need to:

• get the user id
• query the database to check all of the tables of the forms to see if the user id is there
• f it is, to then display the form name (which will be in another field of each form's table)
• if it isn't, then to skip it

An alternative to only listing those that have a row with the user id, is to list them all, but to have "DONE" or something displayed before the form name where the user id was found.

My plan is to use Jumi or IncludePHP to just put this in an article.

Can someone help me work on the code needed to accomplish this?

Thanks,
Scott

[SineMacula]

Well, in case anyone's wondering, I figured it out.

Actually, I've managed to get something beyond what I had imagined - a list of all surveys (done in Chronoforms) associated with the research project the user indicated at registration (using JUserSuite component), in random order, with an indication of which ones have been completed, and those that have not been completed are links to the actual surveys.

I had to do some workarounds - e.g., the research project name is the only metakey for the projects' informed consent document, it's also the email subject in the forms, and the database table that's associated with each survey is in that survey's "titlesall" field.

In case anyone's interested (or wants to offer advice on how to make better code) - here it is:

Code: Select all

{php}
$db = JFactory::getDBO();
$user = &JFactory::getUser();

if($user->id == 0) {
echo "<span class=\"info\">Access to the instruments is restricted to participants who have registered and logged in.</span>";

} else {


$sql = "SELECT `fvalue` FROM `jos_juser_extended_data` WHERE `field_id` = '1' AND `user_id` = '$user->id'";

$db->setQuery($sql);
$study = $db->loadResult();

echo "You signed up for <strong>".$study. "</strong>, so only instruments for that study will be displayed.";

$sql = "SELECT `id` from jos_content WHERE `metakey` = '$study'";

$db->setQuery($sql);

$ifc = $db->loadResult();

echo "<span class=\"info\">Be sure to read the Informed Consent (<a href=\"index.php?view=article&id=".$ifc."%3Ainformed_consent&tmpl=component&print=1&page=&option=com_content\" title=\"Print\" onclick=\"window.open(this.href,'win2','status=no,toolbar=no,scrollbars=yes,titlebar=no,menubar=no,resizable=yes,width=640,height=480,directories=no,location=no'); return false;\">click here</a>), as you will have to acknowledge having read it before submitting each instrument.</span>"; 

echo "<h3>Instruments for study: ".$study."</h3>";
echo "<table width=\"auto\" cellpadding=\"5px\" >";
echo "<tr style=\"border-bottom: 5px solid #D6D6D6\">";
echo "<td style=\"width:auto; min-width:150px; border-bottom: 5px solid #D6D6D6\"><strong>Instrument</strong></td>";
echo "<td style=\"text-align:center; border-bottom: 5px solid #D6D6D6\" width=\"25px\"><strong>Completed?</strong></td>";
echo "</tr>";



$sql = "SELECT * FROM `jos_chrono_contact` WHERE `emailsubject` = '$study' ORDER BY RAND()";

$db->setQuery($sql);

$rows = $db->loadObjectList();

foreach ( $rows as $row ){

//echo "<p>".$row->name." stored in ".$row->titlesall.".</p>";

$table = $row->titlesall;

$query = "SELECT * FROM $table WHERE `username` = '$user->username'" or die("Could not select examples");

$db->setQuery($query);

$db->query();

$numrows = $db->getNumRows();

//echo $numrows;

echo "<tr>";
if($numrows > 0) {
echo "<td>".$row->name."</td><td align=\"center\"><img src=\"templates/rt_mediamogul_j15/images/check.png\"></td></tr>";
} else {
echo "<td><a href=\"index.php?option=com_chronocontact&chronoformname=testform2\">".$row->name." </a></td><td align=\"center\"><img src=\"templates/rt_mediamogul_j15/images/alert.png\"> </td></tr>";
}
}
echo "</table>";
}
{/php}

I used the includePHP plugin to put all that in an article which loads upon login.

It took an all-niter, and a lot of trial and error, but it feels good to have gotten it working!