i have 3 tables called company, customers,and comments.first i add company then the customers are added in customers table and finaly the customers add comments in comments table.
Here is the structure of company table:
id company type address
1 abc running xyz
2 def old asd
Here is the structure of customers table:
cid company customername location
1 1 test delhi
2 2 test1 noida
Here is the structure of comments table:
id company customer comments
1 1 test testcomments
2 2 test1 test1comments
now i want to search in cutomers table by customers name, company name and location so i have tried this but unable to get the value of comments on search how can i achieve it.
here is my search query for companyname and location:
$sql = mysql_query("Select * from customers AS cust INNER JOIN company AS comp ON cust.company = comp.id where cust.name like '%$term%' or cust.location like '%$term%' or comp.company like '%$term%'");
SELECT * from customers AS cust INNER JOIN company AS comp ON cust.company=comp.id
INNER JOIN comments AS com ON com.company = comp.id WHERE .......
(where cust.name like '%$term%' or cust.location like '%$term%' or comp.company like '%$term%')
Hope this helps. Replace WHERE filter as you want.
if you mean that you want to retrieve comments made by which customer then this is an answer to that :
$sql=mysql_query("select companies.company, customers.customername,
comments.comments from companies,customers,comments
where customers.cid=companies.id and comments.customer=customers.customername");
but notice that you must redefine your tables relations and foreign keys should be ids, not names, my answer won't be optimal unless you redefine your tables relations and remove unnecessary fields. you can find alot about database design and normalization rules