Search 6 different tables

[Humper]

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

[james438]

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.

[Humper]

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

Do the values need to be exact.

Yes

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

[james438]

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:

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.

[Humper]

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.

[james438]

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.