Select multiple columns from two tables for searching

[josephbupe]

Hi,

 

How can I select multiple columns from two tables and run a search through multiple fields?

 

My tables are:

 

t_persons (holds information about persons)

t_incidents (holds foreign keys from other tables including t_persons table)

 

What I want is to pull some columns from the two tables above and run a search with a LIKE criteria, something like below. The code originally worked well with only one table, but for two tables it generate errors:

$query = "SELECT
p.PersonID
,p.ImagePath
,p.FamilyName
,p.FirstName
,p.OtherNames
,p.Gender
,p.CountryID
,i.IncidentDate
,i.KeywordID
,i.IncidentCountryID
,i.StatusID

FROM t_incidents AS i
    LEFT JOIN t_persons AS p ON i.PersonID = p.PersonID
WHERE
    FamilyName LIKE '%" . $likes . "%' AND
    FirstName LIKE '%" . $likes . "%' AND
    OtherNames LIKE '%" . $likes . "%' AND
    Gender LIKE '%" . $likes . "%' AND
    IncidentDate LIKE '%" . $likes . "%' AND
    KeywordID LIKE '%" . $likes . "%' AND
    IncidentCountryID LIKE '%" . $likes . "%' AND
    StatusID LIKE '%" . $likes . "%'
ORDER BY PersonID DESC $pages->limit";

Errors are:

Column 'IncidentDate' in where clause is ambiguous
Column 'KeywordID' in where clause is ambiguous
Column 'IncidentCountryID' in where clause is ambiguous
Column 'StatusID' in where clause is ambiguous

These columns are foreign keys on t_incidents table. I have also attached the table relationship diagram if it helps.

 

I will appreciate any better way to do this.

 

Thanx.

 

Joseph

Edited October 1, 2014 by josephbupe

[cyberRobot]

Errors like "Column 'IncidentDate' in where clause is ambiguous" usually mean that the column "IncidentDate" appears in more than one table. Try changing the column names in your where clause so that they specify which table to use. For example, this:

IncidentDate LIKE '%" . $likes . "%' AND

Would likely be this:

i.IncidentDate LIKE '%" . $likes . "%' AND

[ginerjm]

You don't have to change any names - just continue to use the aliases you assigned to the tables in the from clause (p,i).

[josephbupe]

Thanx, it's working.

[Barand]

id columns are internal key links in the database and are no concern of the user.

 

In addition, LIKE is used for wildcard string searches and not for numeric values like id fields.

 

So why are you including the id fields in your search criteria?