select query from 3 tables inc. search page

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

select query from 3 tables inc. search page

Author

Topic

rdl
Starting Member

25 Posts

Posted - 2011-05-18 : 19:29:33

Hi,
I have the following query...

select distinct * from report, reportofficer, reportperson
where report.reportref = 17
and reportofficer.reportref = 17
and reportperson.reportref = 17
group by report.reportref;

this works in phpmyadmin console...however when trying it in .php file it isn't working.

In the php file I have created a form where a user can enter the reportref, town, category or reportstatus ;(user can choose any of them) and on clicking search button, I want some fields of these tables to be shown please.

I have tried the below query in php file which isn't working...

$result = mysql ("select report.ReportRef, report.ReportDate,report.Category, report.Street, report.Town, report.Status, person.ID,
person.Surname, person.Name, officer.OfficerID
from report, reportofficer, reportperson where
report.ReportRef = reportofficer.ReportRef AND reportofficer.ReportRef = reportperson.ReportRef AND report.reportref = LIKE '%" . $search . "%'
or report.ReportRef = reportofficer.ReportRef AND reportofficer.ReportRef = reportperson.ReportRef AND Town LIKE '%" . $Town . "%'
or report.ReportRef = reportofficer.ReportRef AND reportofficer.ReportRef = reportperson.ReportRef AND Category LIKE '%" . $Category . "%'
or report.ReportRef = reportofficer.ReportRef AND reportofficer.ReportRef = reportperson.ReportRef AND ReportStatus LIKE '%" . $ReportStatus . "%' ");

However when trying this - using only one table in .php file, it worked and gave good results..

$result = mysql_query("SELECT * FROM report WHERE ReportRef LIKE '%" . $search . "%' or Town LIKE '%" . $Town . "%' or Category LIKE '%" . $Category . "%' or ReportStatus LIKE '%" . $ReportStatus . "%' ");

Thankyou,
rdl

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-05-18 : 19:50:16

This is a forum for SQL Server - and I doubt many here will know anything about mySQL or PHP. You really should post this in a PHP and/or mySQL forum.

I would, however, recommend that you write the query using join syntax and replace the * with the actual columns you really need. It is recommended that you only get the columns that you actually need - instead of defaulting to all columns. An example would be:


SELECT DISTINCT
       col1
       ,col2
       ,col3
  FROM Report r
 INNER JOIN ReportOfficer ro ON ro.ReportRef = r.ReportRef
 INNER JOIN ReportPerson rp ON rp.ReportRef = r.ReportRef
 WHERE r.ReportRef LIKE '%" . $search . "%'
    OR r.Town LIKE '%" . $Town . "%'
    OR r.Category LIKE '%" . $Category . "%'
    OR r.ReportStatus LIKE '%" . $ReportStatus . "%'

Looking at your previous code, my guess as to why it isn't working is because of the mix between AND and OR's without using a parantheses to force the evaluation order.

Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources