2. PHPRunner General questions
  3. Custome Queries
This topic is locked

Custome Queries

 		4/2/2007 8:33:55 PM
PHPRunner General questions
D
 Dale author

Is it possible to create a custom query to actually append related records to a parent record for searching.
If have a construction_data table and a project_details table.

construction_data table

construction_data_id INT 10 autoincrementing. Primary

city varchar 30

project varchar 100
project_details table

construction_data_id INT 10

project_detail_id INT 10 autoincrementing Primary.

description text



There are of course other fields in both tables. Just trying to keep it simple.
My customer wants to be able to use the search function on the construction_data list page ( and the advanced search page ) to search the fields on the construction_data record AND the description field on the related project details records. Return all construction data records with a match in either record set.
I have tried modifying the construction data table query to join the project details to the construction data, BUT as expected I end up with a row each for each construction record that has project_details related in my construction data list page. I tried a group by, works for NOT duplicating the construction data records, but it screws up the Records Found count etc etc.
Is there a query that I can actually append the project_details records to the construction data record and be able to search the appended data as well.
Help would be greatly appreciated, any suggested websites for info would be great.

Version 3, but I believe the custom query stuff should be the same)
D
 		Dale author 4/3/2007

Im guessing this was a stupid question ?
Sergey Kornilov admin 4/3/2007

We had a similar challenge in one of our internal projects.
We ended up creating a description field in master table that stores concatenated description field from all details records.

To make this work you need to call the same piece of code after detail record is added, deleted or altered.
In this case you only need to run a search against the master table and it returns correct number of records.

This approach also make search work much faster if you have a larger number of records.
W
 		webwork 4/4/2007

<img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=16687&image=1&table=forumreplies' class='bbc_emoticon' alt=':blink:' /> The correct way to do this is using a Left Join in an SQL Query, for MS SQL it would look at lot like this;/

SELECT dbo.construction_data.city, dbo.construction_data.project, dbo.project_details.description,

dbo.construction_data.construction_data_id AS Results_ID

FROM dbo.construction_data LEFT OUTER JOIN

dbo.project_details ON dbo.construction_data.construction_data_id = dbo.project_details.project_detail_id

WHERE (dbo.construction_data_id = xyz)
For MySQL it might be worded a bit differently.

The results of this query will be matching records from both tables, but each table must share a "key" field for this to work.
Tass
[quote name='DaleM' date='Apr 2 2007, 08:33 PM' post='16663']

Is it possible to create a custom query to actually append related records to a parent record for searching.
D
 		Dale author 4/4/2007

Thanks Sergey,
I had thought of that, but I was just hoping that I would not have to save the same data in two places. I come from the old days when conserving space in a database was imperative. I guess that is really not important as much with todays technology.
I already update the master record with a couple of the project detail fields on edit and add, so It will not be hard to add this to the events
I was just hoping there was a magic trick with queries that would avoid duplicating the data.
Thanks for the response.
Thanks for your suggestion also Tass. Im not an mysql query guru as you probably can tell.

I will do some experimenting with your suggestion also.