Sandbox

Posted: **Thu Jan 31, 2008 8:29 am**

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

Posted: **Thu Jan 31, 2008 12:19 pm**

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

Posted: **Thu Jan 31, 2008 5:42 pm**

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.

Posted: **Thu Jan 31, 2008 6:51 pm**

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

Posted: **Thu Jan 31, 2008 8:10 pm**

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";
      }
}

Sandbox

Need mysql statement help

Need mysql statement help

Re: Need mysql statement help

Re: Need mysql statement help

Re: Need mysql statement help

[SOLVED] Re: Need mysql statement help