Need mysql statement help

[SineMacula]

Hi,

I know enough about mysql to know about the existence of various ways of making compound queries with JOIN statments and such, but I don't know enough to figure out how to do it... so I'm looking for some help figuring out the best way to set up a query that uses three interdependent tables.

Here are the statements I've got:

Code: Select all

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

SELECT `surveyls_survey_id` FROM `lime_surveys_languagesettings` WHERE `surveyls_title` = `fvalue`

SELECT `token` FROM `lime_tokens_x` WHERE `completed` = 'Y'

In the last statement, the 'x' in 'lime_tokens_x' is the value from 'surveyls_survey_id' in the previous statement.

What I'm wanting to end up with is to know whether the logged in user has completed the survey, so I just need to end up with a count of "Y" in field "completed" of the "lime_tokens_x" table - because if it's > 0, then it's been completed.

Any suggestions for the best way to structure this?

Thanks,
Scott

[bascherz]

Hi Scott,

If the tables are interdependent, what field do they have in common? For instance, if they all have a userid field that maps to the one in the first query, you can use a JOIN. In that case you'd have something like this:

Code: Select all

SELECT `a.fvalue`,`b.surveyls_survey_id`,`c.token`
  FROM `jos_juser_extended_data` AS a
JOIN `lime_surveys_languagesettings` AS b
  ON `a.user_id`=`b.user_id`
JOIN `lime_tokens_x`as c
  ON `b.user_id`=`c.user_id
WHERE `a.field_id` = '1'
  AND `a.user_id` = '$user->id'
  AND `b.surveyls_title` = `fvalue`
  AND `c.completed` = 'Y';

I'm a bit confused about what `fvalue` is, though. I am sure we'll get this for you, but this is all I can make from what you've provided. I didn't test the above, by the way.

Bruce

[SineMacula]

Thanks, Bruce.

Well, they're interdependent in a chain kind of way, rather than a "all share one field" kind of way.

By that I mean that

jos_users has $id which links to $user_id in jos_jusers_extended_data

jos_jusers_extended_data has $fvalue which links to $surveyls_title in lime_surveys_languagesettings

lime_surveys_languagesettings has $surveyls_survey_id which is the value of x in the table name lime_tokens_x

lime_tokens_x has $completed and $token and I need to know if there's a row where $completed = "Y" and $token = $user->username


So, what I'm starting with is that I know $user->username and $user->id by using

Code: Select all

$user = &JFactory::getUser();

From knowing the $user->username and $user->id, I need to go to the jos_jusers_extended_data to find out which project they've signed up for ($fvalue when $field_id=1), then go to the lime_surveys_languagesettings to find out the id numbers of all the surveys that are associated with that project ($surveyls_title = $fvalue), then go to the appropriate lime_tokens_x tables to find out if there are any $completed = "Y"


As I wrote that out, I realized that since there could be multiple lime_tokens_x tables applicable to a particular user, I probably have to have a separate SELECT statement for each one. And as I think about that, I think it might be just as easy to query all lime_tokens_x tables looking for $token = $user->username and $completed = "Y" rather than trying to figure out which token tables to check (so, for example, instead of going through a long process to figure out which 3 or 4 of 12 surveys a user is taking in order to only check those 3 or 4 tables, just check all 12).

This would work because at registration to the Joomla site, I've added code to put the username in the $token field of the appropriate lime_tokens_x tables.

So, now my needs change somewhat - to focus on generating the list of surveys someone is taking and whether they're done - so, I think I need something like this:

Code: Select all

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

$sql= "SELECT `completed` FROM `lime_tokens_14114` WHERE `token` = $user->username";

$db->setQuery($sql);
$db->query();

if($db->getNumRows != 0) {

$sql = "SELECT `surveyls_title` FROM `lime_surveys_languagesetting` WHERE `sid` = '14114'";

$db->setQuery($sql);
$db->query();

if($completed == 'Y') {

echo $surveyls_title. " - Completed";

} elseif ($completed == 'N') {

echo "<a href="path/to/survey/">".$surveyls_title."</a> - Not Completed";

}

}

And I would have to do this for all surveys that are currently active - which seems more straightforward than where I started with trying to narrow down how many queries I had to do based on that fvalue.

So, does what I've got there look okay? Is there a better (more efficient) way to achieve it?

Thanks.

[SineMacula]

After playing around with that a bit, some trial and error, and so far, all error... I got an idea on how to do my original approach - but will still need some help sorting it out.

I think this time I'm giving you more to work with though  ;)

So, here's what I've got so far:

Code: Select all

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

$sql = "SELECT * FROM `lime_surveys` WHERE `attribute1` = $fvalue";
$db->setQuery($sql);

$rows = $db->loadObjectList();

foreach ($rows as $row) {
   
   $table = echo "lime_tokens_".$row->sid;
   
   $sql = "SELECT `surveyls_title` FROM `lime_surveys_languagesettings` WHERE `surveyls_survey_id` = $row->sid";   
   $db->setQuery($sql);
   $survey_title = $db->loadResult();
   
   $sql = "SELECT `completed` FROM $table WHERE `token` = '$user->username'";
   $db->setQuery();
   
   if($db->loadResult() == 'Y') {
      echo $survey_title." - Completed";
      }
         
   if($db->loadResult() == 'N') {
      echo "<a href="index.php?sid=".$table.">".$survey_title."</a> - Not Completed";
      }
}

So, what this is intended to do is:

• check jos_user_extended_data to get the study name (held in the fvalue field where field_id = 1) for the current user
• find all surveys that are assigned to that study - attribute1 will hold the study name, so if attribute1 = fvalue, the survey is part of the study for that user
• for each survey found for that study, check its token table to see if the current user has completed it
• if the survey has been completed, print the survey name and "Done"
• if the survey has not been completed, print the linked survey name and "Not Completed"
• check the next survey's token table and repeat

Having tested this, I see that it's not working.

I'll keep up with my trial and error, but would appreciate any ideas you care to share. 

Thanks,
Scott

[SineMacula]

Got it!!  :D

Code: Select all

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

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

$sql = "SELECT * FROM `lime_surveys` WHERE `attribute1` = '$fvalue'";
$db->setQuery($sql);

$rows = $db->loadObjectList();

foreach ($rows as $row) {
   
   $sid = $row->sid;
   
   $sql = "SELECT `surveyls_title` FROM `lime_surveys_languagesettings` WHERE `surveyls_survey_id` = $sid";   
   $db->setQuery($sql);
   $survey_title = $db->loadResult();
   
   $sql = "SELECT `completed` FROM `lime_tokens_$sid` WHERE `token` = '$user->username'";
   $db->setQuery($sql);
   $completed = $db->loadResult();
   
   if($completed == 'Y') {
      echo $survey_title." - Completed";
      }
         
   if($completed == 'N') {
      echo "<a href="index.php?sid=".$sid."">".$survey_title."</a> - Not Completed";
      }
}