This topic is locked

Custom Search & Query Capabilities?

12/6/2006 8:09:29 PM
PHPRunner General questions
larsonsc author

I have a need to build a custom search page that executes a given query (multi-table) based on the input data but I am not sure if PHPRunner can help me accomplish this or not.
What I need to do is construct a page that has an input box where the user will input an eight digit numeric and then click a search button. I need to have that numeric passed to a given SQL query that returns data from multiple tables. Is this possible with this version of PHPR? Or would this be classified as "reporting" and as I have read in other threads will not be available until the next version.

Jane 12/7/2006

you can do it using custom view.

Create custom view on the Datasource tables tab and edit SQL query on the Edit SQL query tab.

larsonsc author 12/7/2006


you can do it using custom view.

Create custom view on the Datasource tables tab and edit SQL query on the Edit SQL query tab.

thanks for the reply. I'll try this tonight and see what I can come up with.

larsonsc author 12/9/2006

OK, so here is my problem now. I took Jane's suggestion and built a custom multi-table query to build the report data that I need. I have the search page setup so that a single value is entered, and then it is used as a key value to get data from three tables total. The query is running seemingly fine, except that I suspect I am missing something in it to clean up the output results.
My issue though now lies in that one of the tables will almost certainly always have more than one row that matches the selection criteria. So what is happening is that I am getting multiple full data results instead of what I am looking for. Let me see if I can explain this a little better.
Here is the query I am using:

select `inventory`.`Intake_ID`,

`Cat_Name` AS "Cat's Name",

concat((YEAR(curdate())-YEAR(Date_Of_Birth)), ' Year(s),', (MONTH(curdate())-MONTH(Date_Of_Birth)), ' Month(s)') AS "Estimated Current Age",

concat(`Breed`, ' ', `Color`, ' ', `Markings`) AS "Breed/Color/Markings",

`Intake_Source` AS "Source of Intake",

`Date_Of_Intake` AS "Date of Intake",

`Altered` AS "Spayed or Neutered",

concat(`Adopter_First_Name`, ' ', `Adopter_Last_Name`) AS "Adopted To",

concat(`Adopter_Address`, ' ', `Adopter_Address_2`) AS "Address",

`Adopter_City` AS "City",

`Adopter_State` AS "State",

`Adopter_Zip_Code` AS "Zip Code",

`Adopter_Home_Phone` AS "Home Phone",

`Adopter_Alt_Phone` AS "Alternate Phone",

`Adopter_Email` AS "Email Address",

`Adoption_Date` AS "Date of Adoption",

`Adoption_Location` AS "Adoption Location",

`Adoption_Fee` AS "Adoption Fee",

`Adoption_Payment_Method` AS "Paid By",

`medical_records`.`Date_Of_Treatment` AS "Treatment Date(s)",

`medical_records`.`Treatment` AS "Type of Treatment",

`medical_records`.`Medication` AS "Medication Used",

`medical_records`.`Description` AS "Description of Treatment",

`medical_records`.`Clinic` AS "Clinic",

`inventory`.`Foster_Name` AS "Fostered By",

`foster_homes`.`Contact_Info` AS "You Can Contact The Foster At"

From `adoption_records`, `medical_records`, `inventory`, `foster_homes`

WHERE `adoption_records`.`Intake_ID` = `inventory`.`Intake_ID`

AND `medical_records`.`Intake_ID` = `inventory`.`Intake_ID`

AND `inventory`.`Foster_Name` = `foster_homes`.`Foster_Name`

What I am wanting is a nice formatted result that shows only one instance of all of the data that is not from the `medical_records` table followed by each of the individula results from the `medical_records` table. What I am getting is each record from the `medical_records` table in a separate full data record that includes all of the other results. Which then leads to another problem with my redesigned Printer-Friendly page, but I'll worry about that once I get this other issue figured out.
Is the way I am trying to format the result data even possible or are my users just going to have to run multiple queries from other pages to generate what I am trying to get?
Thank for the help.