Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    rdl
    rdl is offline
    New to the CF scene
    Join Date
    May 2011
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Selecting from 3 tables inc. search

    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...

    [code]
    $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..

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


    Thankyou,
    rdl

  • #2
    Regular Coder
    Join Date
    Apr 2005
    Location
    Ohio
    Posts
    254
    Thanks
    1
    Thanked 63 Times in 63 Posts
    I ought to brush up on my SQL skills.
    Not sure if it's a typo or not but you have an extra = sign right before the first LIKE in your SQL statement.
    Code:
    report.ReportRef = reportofficer.ReportRef AND reportofficer.ReportRef = reportperson.ReportRef AND report.reportref = LIKE '%" . $search . "%'


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •