I have a table with 3 columns: SubmissionId, FieldName and FieldValue.

1) SubmissionId keeps ID submission.

2) FieldName stores field name of a form.

3) FieldValue stores field value of form field.

I have a form with 3 fields: name, email and status. I have four submissions, in total there are 12 entries: 3 per submission.

How do I get 3 records of a submission if the status field of that submission is equal to the string "open"?

It can? I start with this, but evidently only retrieves the record containing the "status" field. I need the other 2 fields.

UPDATED:

$query
    ->select('a.SubmissionId, a.DateSubmitted, a.status, b.SubmissionId, b.FieldName, b.FieldValue')
    ->from($db->quoteName('#__rsform_submissions', 'a'))
    ->join('INNER', $db->quoteName('#__rsform_submission_values', 'b') . ' ON (' . $db->quoteName('a.SubmissionId') . ' = ' . $db->quoteName('b.SubmissionId') . ')')
    ->where($db->quoteName('b.FieldName') . ' = \'status\' AND ' . $db->quoteName('b.FieldValue') . ' = \'open\'' );
  • Can you please show your full database query? – Lodder Jul 22 '16 at 8:20
  • @Loder, I updated the query, :) – George Berkeley Jul 22 '16 at 14:10
  • @GeorgeBerkeleyGeorge I think you are asking for a "pivot table" (current answers are not offering this technique). If you can confirm my suspicions by providing an sql export of 10 or 20 rows of data and your expected resultset from that data, I am confident that I can offer you a professional grade solution. Please help me to help you (which may help future researchers). – mickmackusa Jul 23 at 12:37

"If the submission is equal to open"

In which case, isn't this what you need?

$query
    ->select(array('a.SubmissionId, a.DateSubmitted, a.status, b.SubmissionId, b.FieldName, b.FieldValue'))
    ->from($db->qn('#__rsform_submissions', 'a'))
    ->join('INNER', $db->qn('#__rsform_submission_values', 'b') . ' ON (' . $db->qn('a.SubmissionId') . ' = ' . $db->qn('b.SubmissionId') . ')')
    ->where($db->qn('b.FieldValue') . ' = ' . $db->qn('open'));

Here you are:

$query->select($db->qn('s.SubmissionId','id'))
        ->select($db->qn('s.DateSubmitted','date'))
        ->select($db->qn('sv.FieldValue','status'))
        ->select($db->qn('nv.FieldValue','name'))
        ->select($db->qn('ev.FieldValue','email'))
        ->from($db->qn('#__rsform_submissions','s'))
        ->leftJoin($db->qn('#__rsform_submission_values','sv').' ON '.$db->qn('sv.SubmissionId').' = '.$db->qn('s.SubmissionId').' AND '.$db->qn('sv.FieldName').' = '.$db->q('status'))
        ->leftJoin($db->qn('#__rsform_submission_values','nv').' ON '.$db->qn('nv.SubmissionId').' = '.$db->qn('s.SubmissionId').' AND '.$db->qn('nv.FieldName').' = '.$db->q('name'))
        ->leftJoin($db->qn('#__rsform_submission_values','ev').' ON '.$db->qn('ev.SubmissionId').' = '.$db->qn('s.SubmissionId').' AND '.$db->qn('ev.FieldName').' = '.$db->q('email'))
        ->where($db->qn('sv.FieldValue').' = '.$db->q('open'));

As output you'll have row with data you need. I believe the code is easy enough to figure out each line.

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.