Retrieve PRIMARY KEY value from SELECT DISTINCT with INNER JOIN

Question

I have a set of joined tables that I am querying in the following way:

$query = $this->mysqli->query("
    SELECT DISTINCT name, website, email
    FROM sfe
    INNER JOIN ef ON sfe.ID_SFE = ef.ID_SFE
    INNER JOIN f  ON   f.ID_F   = ef.ID_F
    INNER JOIN ad ON  ad.ID_SFE = ef.ID_SFE
    WHERE name         LIKE '%{$sanitized}%' OR 
          website       LIKE '%{$sanitized}%' OR
          business_name LIKE '%{$sanitized}%' OR
          email         LIKE '%{$sanitized}%'
");

where ID_SFE is the primary key of table sfe and also the foreign key of ef.

When I make this query, I then echo the list of results with the following:

while ($result = $query->fetch_object()) {
    $query_result = $result->"name";

    echo "$query_result"
}

Because now I would like to also find the value of ID_SFE inside the same while loop, I tried to add ID_SFE in the list of SELECT DISTINCT together with name, website, email, however, I get ERROR: There was a problem with the query.

How can I get the value of ID_SFE and store it to another variable inside the while loop?

Thanks

score 1 · Accepted Answer · answered Feb 07 '16 at 05:34

1

You can not simply add ID_SFE to the list of fields to retrieve, because such field exists in the ad and ef tables.

You will be able to add ad.ID_SFE and/or ad.ID_SFE fields -note you need to specify the table name when specifying the field, as the fields needs to be referenced unequivocally.

answered Feb 07 '16 at 05:34

Nico Andrade

880
5
16

Thank you, Nico, that has indeed resolved the problem. – s coronado Feb 07 '16 at 05:45
You're welcome! Happy to hear that. Can you please mark this answer as the right one? – Nico Andrade Feb 07 '16 at 05:53

Retrieve PRIMARY KEY value from SELECT DISTINCT with INNER JOIN

1 Answers1