2. PHPRunner General questions
  3. SubSearch ou double Search
This topic is locked

SubSearch ou double Search

 		11/7/2006 8:34:47 AM
PHPRunner General questions
J
 Jean author

Hello,
Users ask to me a way to refine searchs. Anybody knows how to make a SubSearch on the result of a first Search ?
Thanx in advance,
Jean
T
 		thesofa 11/7/2006

this involves using the HAVING key word, if you look at my post about searching aggregate values you will find how I got round it.

It may be that just an additional line in the advanced search will do the trick instead of having to use HAVING.

comeback if you need more help.

In addition, there is always the subquery, search the forum for alexy's post about subquery
J
 		Jean author 11/7/2006

Say, are you really a speaking sofa ?
I tried with enthusiasm the Having strategy but had to be disapointed. I followed the procedure in http://www.asprunner.com/forums/index.php?showtopic=3636# but met an error msg about the sql syntax. Is there something wrong in this procedure ? Is there anything to do with the mysql release <5 ?
Sorry, just lammer's questions...
T
 		thesofa 11/7/2006

Ok, please give us more details about what you are trying to do??????
J
 		Jean author 11/11/2006

Ok...
My users would be happy if they could do a simple search with "+word1 +word2 -word3" (1).

I think they would remain happy with just ""+word1 +word2" (2).
As the _list.php code (searchcode) seems obscure to me, I tried to find another way in filtering with a Having procedure : "word1" then "word2" in the remaining records (3).
Have you some way of reaching the (1) (2) or (3) ?
T
 		thesofa 11/12/2006

Ok...

My users would be happy if they could do a simple search with "+word1 +word2 -word3" (1).

I think they would remain happy with just ""+word1 +word2" (2).



when you ask for word1+word2, am I right in thinking that word1 and word2 are stored in the same field in the database?
J
 		Jean author 11/12/2006

when you ask for word1+word2, am I right in thinking that word1 and word2 are stored in the same field in the database?


If "Any field" is choosen in the Simple search, had to look in every field for word1 and word2 being everywhere in the current fields. If a field is specified in the simple search, word 1 and word2 hat to be stored in the field specified.
T
 		thesofa 11/12/2006

I think this post about searching for 2 values in one field may answer your question
J
 		Jean author 11/12/2006

I think this post about searching for 2 values in one field may answer your question


It will do the trick, thank you very much Speaking Sofa ! And so easy to activate...
T
 		thesofa 11/12/2006

a votre service
T
 		thesofa 12/7/2006

Hi, its me again.

If you do not want to have to do an advanced search on the data to activate the Having clause in the MYSQL filter, you can try this.

Open the file /include/xxx_variables.php and look for the line that says

$gstrOrderBy=



this will prolly be line 11.

This line carries the ORDER clause for the MYSQL query. The having clause needs inserting before the ORDER clause, so if I have this line

$gstrOrderBy="ORDER BY `pupils`.`lname` ASC";

all I have to do is replace it with this line

$gstrOrderBy=" Having Count(`ID`) >=5 ORDER BY `pupils`.`lname` ASC";


The main SQL Query reads thus

$gstrSQL = "select Count(`ID`) AS `Total` ,

`ID`,

`DateGiven`,

`TutorGroup`,

`DayForDetention`,

`sess`,

concat(`pupils`.`fname`,' ',`pupils`.`lname`) AS `Pupil`,

`pupils`.`lname`,

`detentions`.`detainee`

from ((`detentions` inner join `pupils` on((`pupils`.`idPupil` = `detentions`.`detainee`)))

inner join `dept` on((`dept`.`idDept` = `detentions`.`department`)))

WHERE (`detentions`.`sess`='3') and (`detentions`.`Lunchtime`=\"P\")

Group By `detentions`.`detainee` ";



so you can see that I am wanting to do the filtering of the sessions (=3) and the type from Lunchtime (='P'), then I need to filter the results for the count of the ID field to be 5 or more.

I think I have lost the thread there, but it works.

HTH
J
 		Jean author 12/7/2006

Hi, its me again.


Merci beaucoup, Talking Sofa! I will study and try it this week-end probably.
T
 		thesofa 12/7/2006

Bon

Joyeux Noel
T
 		thesofa 12/7/2006

OK, so I have been playing a bit more, and i have found that the solution given above breaks when I click on a column head to sort by a different column.

It would seem that the click forces a re-query and ordering from the tables in the mysql database.

After a little searching in the xxx_list.php file for the word 'order', I found this line in the section

// process session variables

// order by


if($order_dir=="a")

$strOrderBy=" order by ".($order_ind+1)." asc";



when I changed it to this

if($order_dir=="a")

$strOrderBy="having Count(`ID`) >='5' order by ".($order_ind+1)." asc";



it seemed to work whichever column I clicked on to sort.

You also need to change the line

$strOrderBy=" order by ".($order_ind+1)." desc";

to read

$strOrderBy="having Count(`ID`) >='5' order by ".($order_ind+1)." desc";


Obviously you will change the part that reads > having Count(`ID`) >='5'

to suit your needs!
It seems to work with export and printer friendly pages too!

Now please break it for me.

HTH