CODEWITHSUNDEEP
phpmysql
Jay-oh
Jay-oh

Reputation: 456

Getting multiple value's from same database row

Thanks for the time to read my question. I have a form on my website (Wordpress wp_forms) this generates a database entry wp_wpforms_entry_fields where the entries for the form are stored. My form contains three fields (Name, Email address and a Yes or No question).

In my database this creates the following table: enter image description here

What I'm trying to do is get the field_id from 1 and 3. So with my SQL I do the following:

$query = "SELECT * 
    FROM wp_wpforms_entry_fields 
    WHERE field_id = 1";

But this only selects the value from field_id 1 what I would like is to also have the value from WHERE field_id 3 = Yes. This way I can show the names of people who gave a positive answer to the question.

I have no idea where to start, this is probably not as difficult as I'm making it. But doing:

$query = "SELECT * 
    FROM wp_wpforms_entry_fields 
    WHERE field_id = 1 
        AND field_id = 3 
        AND IS Yes";

Surely doesn't work... thanks again for reading.

PS. I've left out the database connection rules because the connection works, I can retrieve data based on the first SELECT. But I can't seem to get the separation between Yes and No.

Upvotes: 0

Views: 153

Answers (1)

Jiri Tousek
Jiri Tousek

Reputation: 12450

You'll have to join the table to itself:

select f1.value as name
from wp_wpforms_entry_fields f1
    inner join wp_wpforms_entry_fields f3 on (f1.entry_id = f3.entry_id and f3.field_id = 3)
where f1.field_id = 1 and f3.value = 'Yes'

In plain text, you're looking for names of entries, where a row for field_id 3 with same entry_id says 'Yes'.

Alternatively, with a subquery:

select value as name
from wp_wpforms_entry_fields f1
where field_id = 1
    and entry_id in (
        select entry_id from wp_wpforms_entry_fields f3 where field_id = 3 and value = 'Yes'
    )

Here we select a list of entry_ids for which there is 'Yes' in field 3, and then we select values of field 1 for those entries whose entry_id is on that first list.

Upvotes: 1

Related Questions