Select to retrieve records with different values

Question

Select to retrieve records with different values

Navigation

#1 by (0 votes)

2

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

1) SubmissionId save the submission ID.

2) FieldName and FieldValue save respectively the name of a form field and its value.

I have a form with 3 fields: Name, email and status. If 4 submissions have been done, in total there will be 12 records: 3 for each submission.

How do I recover the 3 records of a submission if the submission's status field is equal to the "open" string?

Can you? I start with this, but evidently only retrieves the record that contains the "status" field. I need the other 2 fields.

->where($db->quoteName('b.FieldName') . ' = \'estatus\' AND ' . $db->quoteName('b.FieldValue') . ' = \'abierto\'' )

php joomla3

asked by George Berkeley 21.07.2016 в 20:43

source

1 answer

Read WAV file and analyze samples every n milliseconds [closed] How to do click counter in PHP, MySQL and AJAX?

score 0 · Answer 1

I would do it with a subquery that first finds the submissionId of the submission with the two values I have and then I simply get the lines that have that submissionId:

$tableName = 'submissions';

$subQuery = $db->getQuery(true)
    ->select($db->qn('b.SubmissionId'))
    ->from($db->qn($tableName))
    ->where($db->qn('b.FieldName') . " = 'estatus' AND " . $db->qn('b.FieldValue') . " = 'abierto'" );

$query = $db->getQuery(true)
    ->select('*')
    ->from($db->qn($tableName))
    ->where($db->qn('b.SubmissionId') . " = (" . $subQuery . "));

$db->setQuery($query);