Results 1 to 6 of 6

Thread: Search 6 different tables

  1. #1
    Join Date
    Aug 2006
    Posts
    65
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Default Search 6 different tables

    I am creating a search for reports that people will submit. I have 6 tables within my database. Below are the Tables:


    Employees
    EID
    UserName
    Password



    WDR_Highlights
    Highlight
    ID_WDR
    Task



    DeliveryOrders
    DO
    TaskName



    Hours
    ID_WDR
    DO
    Hours



    Reports
    ID_WDR
    EID
    ReportDate
    AnticipatedTravel
    AnticipatedLeave



    Travel
    ID_WDR
    DO
    TravelExp
    TravelExpODC



    I need to be able to search by 6 different variables. Right now I am doing if statements but I think there has to be a better way to do this. Can someone point me in the right direction? I have attached what I have done so far....
    Last edited by Humper; 12-15-2008 at 02:58 PM. Reason: Adding file

  2. #2
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,740
    Thanks
    82
    Thanked 90 Times in 88 Posts

    Default

    I'll comment since this forum does not get visited much. I just finished work updating the search program on my site, so I have a little experience with this.

    I need to be able to search by 6 different variables.
    In an ideal world how would your program work? For example do you want to have one or multiple terms which will search a table as a whole or multiple tables as one unified document. Do the values need to be exact. Does the data pulled from the database need to be processed first? etc.
    To choose the lesser of two evils is still to choose evil. My personal site

  3. #3
    Join Date
    Aug 2006
    Posts
    65
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Default

    Quote Originally Posted by james438 View Post
    For example do you want to have one or multiple terms which will search a table as a whole or multiple tables as one unified document
    I need to be able to search the 6 different variables in the search.php page. They should be able to be searched 1 at a time, 2 at a time, 3 at a time, 4 at a time, 5 at a time, or all 6 at a time


    Quote Originally Posted by james438 View Post
    Do the values need to be exact.
    Yes


    Quote Originally Posted by james438 View Post
    Does the data pulled from the database need to be processed first?
    No --- This data should just be pulled from the database




    Take a look at the zip attached in the first post to get a better idea of what I was trying to do.

    Thanks for helping!!

  4. #4
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,740
    Thanks
    82
    Thanked 90 Times in 88 Posts

    Default

    I did look at the files, but I don't think I am that good with reading code of that size. That and I am unsure what errors to look for as well as what it is that you are trying to do.

    When I say match exactly what I mean is will 'abc' match 'Abc' or ' abc' or 'I know my abcs'?

    If you want to combine columns in a table you could do something like:
    Code:
    SELECT premise, summary, conclusion FROM review WHERE concat(premise, conclusion) like '%must%' and concat(premise,summary,conclusion) like '%storyline%'
    You can also join and compare the data from the tables using a Join statement, although I must say that I have never had much need to use them, so my knowledge of inner joins and LEFT or RIGHT outer joins is very little. You can read up a bit more about it here: http://dev.mysql.com/doc/refman/5.0/en/join.html

    When you say 6 terms to be searched one at a time or two at a time, etc, do you mean that you want to search the contents of a table and retrieve the results if all of the terms are located in the table and then search the next table using the same method?

    What is the makeup of the columns in the tables? Integer, text, etc.

    Sorry, I don't promise to be very helpful since my knowledge of MySQL is very limited, but your poblem has me a bit curious.
    To choose the lesser of two evils is still to choose evil. My personal site

  5. #5
    Join Date
    Aug 2006
    Posts
    65
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Default

    Sorry I will try and explain better... bare with me as this may be a bit lengthy...

    I have a page that has 6 drop down boxes that are populated from data that is either in the tables already or populated but a piece of code (mainly the date).... from this page the user can select how to search the information in the tables... so they can either search 1 item at a time, 2 items at a time, 3 items at a time, 4 items at a time, 5 items at a time, or all 6 items at a time... (item -- meaning drop down variable)...... The things is most of the tables have a unique identifier (ID_WDR) that can be used to link all the data since the ID_WDR is created in the Reports table... this is linked to the Employees tabel by the EID... so as you can see all the table s are linked in some way to help with searching..... the thing is I have about 1500 line of code to search them and I dont really know if I got all the ways that this can be searched... I created two search functions and two print functions to help with reuse but I think this can be reduced even further... THIS IS THE MAIN PURPOSE OF THE POST..............I haven't seen any errors as I have been going through this but I am really just spot checking as I go. I have updated the zip to included my new changes... I think this is pretty much completed.... but I would like someone to review it and see if there is any room for improvement.
    Last edited by Humper; 12-17-2008 at 07:55 PM. Reason: Adding file

  6. #6
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,740
    Thanks
    82
    Thanked 90 Times in 88 Posts

    Default

    I looked over your new file a bit more closely now that I have a better idea of what you are trying to do. Mostly I tried scanning it for ways to shorten up the code to make the file smaller, but I couldn't find any way to shorten it further. From what I can tell you are making good use of loops without making it overly complicated and overall the code looks really clean and efficient.

    Thanks for the details. It helped to clear a lot of things up for me that I just was not understanding what you were asking before.
    To choose the lesser of two evils is still to choose evil. My personal site

Bookmarks

Posting Permissions

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