Searching on aggregate values

Hi,
I recommend you to create a view in your database with CREATE VIEW command then use this view in PHPRunner.

Then you'll be able to search on aggregate values without any code modifications.

Hi,

My fault this, I was not explicit enough.

The report is already based upon a view made in PHPRunner, will making a view in the MYSQL databse, V5, make any difference?

I need to be able to do an advanced search on three different fields, so if I create a view in the MYSQL database, rather than through PHP, will I be able to search `TutorGroup`CONTAINS "xxxx" AND `DateGiven` BETWEEN xx/xx/xxxx and xx/xx/xxxx AND Count(`detainee`)>=3?????
The full sql query is as follows

Cheers and thanks for the help

Hi,
create a view by issuing the command in MySQL:

create view myview as

Then you'll be able to search for this condition:

`TutorGroup`CONTAINS "xxxx" AND `DateGiven` BETWEEN xx/xx/xxxx and xx/xx/xxxx AND Total>=3

Once again, I am not being clear in my request, i feel.

I need it to filter out those detentions given between 2 dates, group them by detainee, THEN it has to count the detentions given per detainee, Then it has to filter out only those with 3 or more.

The view you gave me does not give the correct results when I do that. It shows the count of all the detentions given to that detainee, not just those given during the specified period.

I then need to be able to filter the count field.

its like a query in a query, but I need a filter on the inner query as well as a count on that filter, then a filter on the outer query.
I am stuck.

I can do it on my thumbs, using php makes this harder.

Hi,
I see what you saying.

You can replace WHERE with HAVING in generated ..._list.php file.

Find this line there:

$strSQL = AddWhere($strSQL,$strWhere);

and replace it with:

$strSQL .= " having ".$strWhere;

Then find this one:

$strSQL = AddWhere($strSQL,$sWhere);

and replace it with:

$strSQL .= " having ".$sWhere;

I don't guarantee this would work with Master-detail relationship or with Advanced Security.

Hi,

Aha, a sneaky one that, I have been digging in the xxx_search.php file, perhaps that is why I found nothing <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=12057&image=1&table=forumreplies' class='bbcemoticon' alt='<<' />

I have tried this and it produces a query like this

and an error

Unknown column 'detentions.Total' in 'having clause'

so I played around with the query and got this to work

This produces the correct answer from the tables, having filtered them by hand in Navicat, the numbers check out.
If I try this as a filter

it only returns half the records.. It would seem that the use of having excludes any pupils who have detentions outside the date range specified, even if they have some inside the range.
I believe I need to have a query string based upon the date and tutor group in a where clause with the total count being in the Having part.

I am quite happy to Hack the final code to produce this effect, only I will need guidance as to where and having, Where do they go in the code?
I am now almost totally bald, but I should like to crack this one, with all of your help please.
You told me to replace various strings with new ones, is the first one for simple search and the second for advanced search?
Is there any way I can use the totalling/count feature on step 8, field order and totals to count the detainees records?

bump

Hi,
the first replacement I gave you is for simple search, the second - for advanced.
I'm not sure what results do you expect from your SQL query.

Possibly you need to use both WHERE and HAVING clauses. I.e. something like this:

select

PHPRunner doesn't deal with HAVING clause at all.

You can modify generated ..._list.php file to construct complex search queries.

Here is the snippet int he file that constructs Advanced Search SQL string.

else if(@$_SESSION[$strTableName."_search"]==2)

Modify it to add both Having and Where clauses to $strSQL.

Hi Alexy, thanks for the snippet, I had come to the same conclusion as you, I need WHERE and HAVING.

I am more than happy to hack code, what I need to know is the variable names for the search criteria from the advanced search page, so if I have the following fields on the advanced search page, in this order

TutorGroup

DateGiven

DayForDetention

Total......Count(`detentions`.`detainee`)
What will the variables be, and will it vary if I only use 3 of them rather than all 4?If I use TutorGroup, Dategiven and Total, will that change the variable names used to build the sql query?

Many thanks for all your help, I seem to be getting more than my fair share, but I am trying to put some back too!

Cheers

bump

Hi,
$f variable in the code contains a field name.

$where - a search criteria for a given field
all you need is to combine search criterias properly.

Hi,

Hi

OK I admit it, I have no idea where to start, please reduce status to Noob.

what I would like to do is get the search string and just before it gets fired into the mYSQL server, add a clause at the end along the lines of "HAVING Count(`detentions`.`detainee`)>=3"

so that the query only displays the records where they have 3 detentions in the period set by the advanced search options.

If the staff want to change the number of detentions that triggers another letter home, then I will change it in the code by hand.

Please help me ONCE AGAIN.

Cheers

I have been thinking for a change, if I create a temporary table of the data I need for the search to work, I can have a column of the count of the detentions, with the value created as the table is written. This table will have to be named with the name or identifyer of the logged in user, and it will have to be emptied and dropped after each use.

I can hear the sound of thrashing hard discs here. This will only need to be used when the user tries to filter on the Total column, so I will need some decision making and branching here.

Please can you tell me whereabouts in the code created by PHPrunner is the finalised sql search string creted before it is used to query the database?

Then I can try to create or re-work the query to suit my needs?

Cheers

I think I have cracked it.

I have a field in the sql query of

As I always want a count of detentions, but between certain variable date and variable year groups, But I always want the details from all those who have 3 or more detentions in the given period.

So I have to modify the search parts of the xxx_list.php files.

find the part around line 248 (in mine anyhow)

that reads

above this is the line like this

add this snippet to the end

so the line reads

this adds the 'HAVING' clause just before the 'ORDER BY' statement and produces correct results.

OK so I have to add the code in each time I rebuild, but it works.

Hack, Hack ,Hack....