如何在sql查询中搜索外键表的列名？

Question

I going to create a search function in my project but found that some problem in selecting the column record from another foreign key's table is make me frustrated .

This is a normal sample output before searching:

This is a issued table after searching:

mysql query of my table named "member_booking"

If I'm search the title which is based on event table,it will show me a string of title record where no restricted by Foreign key.

mysql query may look like this:

select *
from member_booking,event,member
where member_booking.E_ID=event.E_ID and member_booking.MEM_ID=member.MEM_ID and  member_booking.REF_CODES like '%$keyword%' OR event.E_TITLE like '%$keyword%' OR member.MEM_USERNAME like '%$keyword%' OR member.MEM_EMAIL like '%$keyword%' 

Thanks.

Answer 1

Be careful of SQL injection attack if you inject $keyword into those "like" operators. Use "inner join" or left/right join to join the tables.

for example.

select 
-- your comma separated list of fields here, for example
-- e.event_title , e.amount , e.event_date , e.MEM_ID, 
from 
member_booking mb inner join event e on mb.E_ID = e.E_ID 
inner join member m on mb.MEM_ID = m.MEM_ID
where  mb.REF_CODES like '%$keyword%' 
OR e.E_TITLE like '%$keyword%' 
OR m.MEM_USERNAME like '%$keyword%' 
OR m.MEM_EMAIL like '%$keyword%' **