PHP on Windows - RE: [PHP-WIN] Search results

Author

RE: [PHP-WIN] Search results

Harpreet

2007-02-03, 4:01 am

SQL looks as below:

select top 400 * from na_item_view_new where ((story_name like
'%Midlothian%' ) or (title like '%Midlothian%' ) or (script like
'%Midlothian%' ) or (item_text like '%Midlothian%' ) or (keyword like
'%Midlothian%' ) or (notes like '%Midlothian%' ) or (description like
'%Midlothian%' ) or (clipname like '%Midlothian%' ) or (creator like
'%Midlothian%' )) and Convert(datetime,rundown_date) >=3D '05/02/2006' =
and
Convert(datetime,rundown_date) <=3D '02/02/2007' and type=3D1 and
content_status=3D1 order by convert(datetime, rundown_date) asc

View is as below:
CREATE view na_item_view_new as
select s.story_id,
u.title as title,
s.title as story_name,
u.state,
[rundown_date]=3Dconvert(char(10),u.air_date,101),
'' as video,
'' as cg_text,
SUBSTRING(s.text, 1, 500) AS script,
SUBSTRING(i.text, 1, 500) as item_text ,
i.type,
i.content_status,
k.keyword,
i.editorial_description as description ,
d.description as notes,
s.editor as creator,
i.original_material_id as clipname,
i.ar_material_id as material_id
from=20
(select null as state,null as type, rundown_id, ncs_rundown_id,
edit_duration, title,=20
[air_date]=3Dconvert(char(10),air_date, 101), =20
edit_start_time=3Dsubstring(convert(varc
har(10),=20
edit_start_time, 114),1,8) from na_rundown_tbl where rundown_id not in
(select ref1 from req_state_tbl where type=3D(select type_id from
req_type_tbl where type=3D'NA_ST_RUNDOWN'))
union
select state, type, rundown_id, ncs_rundown_id, edit_duration, title,
[air_date]=3Dconvert(char(10),air_date, 101), =20
edit_start_time=3Dsubstring(convert(varc
har(10),=20
edit_start_time, 114),1,8) from na_rundown_tbl r left outer join
req_state_tbl s on r.rundown_id=3Ds.ref1
where (state=3D5 AND type=3D(select type_id from req_type_tbl where
type=3D'NA_ST_RUNDOWN'))) u
inner join na_story_tbl s on s.rundown_id=3Du.rundown_id=20
left outer join na_item_tbl i on s.story_id=3Di.story_id
left outer join na_itemkeyword_tbl k on i.item_id=3Dk.item_id
left outer join na_itemdesc_tbl d on i.item_id=3Dd.item_id

Regards,
Harpreet Kaur
Crispin Corporation=20
(919) 367-7911
(919) 791-9886 (Cell)

-----Original Message-----
From: Gustav Wiberg [mailto:gustav@hmn.se]=20
Sent: Friday, February 02, 2007 1:47 AM
To: Harpreet; 'php windows'
Subject: RE: [PHP-WIN] Search results

Hi!

I agree to Armando about the optimasation about the search-query. If you
show some bit of code (it may be useful only with the actual query used
to search), we maybe can help you!

Best regards
/Gustav Wiberg
HMN Konsult (Sweden)
http://www.hmn.se/

=20

-----Original Message-----
From: Harpreet [mailto:harpreet@crispincorp.com]=20
Sent: Thursday, February 01, 2007 10:22 PM
To: php windows
Subject: [PHP-WIN] Search results

I have a search page on my web application that searches a database with
1000's of rows. The search results are returned in like 45-50 seconds.
If the user goes to another page and then returns to the same page again
it takes the same amount of time.

Is there a way to use session variables to remember the search results
and if user comes back to the page with the same criteria it shows the
static results and not take as long.

Help is needed urgent. Thanks

Regards,
Harpreet Kaur

--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Armando

2007-02-03, 7:00 pm

I see what you mean now, even 400 results would take a long time doing
like matching on this many text fields without fulltext indexing. In
this case, you want to investigate the use of PHP's serialize() and
unserialize() functions. Check out this article on Zend Developer Zone
for information:

http://devzone.zend.com/node/view/id/1258

If your requirement is user-specific then you'll need to determine how
you want to accomplish that but that shouldn't be too difficult. The
above page uses MySQL as it's example but since you're doing the caching
via PHP then it shouldn't be an issue. HTH. Cheers.

Armando

..

Harpreet wrote:
> SQL looks as below:
>
> select top 400 * from na_item_view_new where ((story_name like
> '%Midlothian%' ) or (title like '%Midlothian%' ) or (script like
> '%Midlothian%' ) or (item_text like '%Midlothian%' ) or (keyword like
> '%Midlothian%' ) or (notes like '%Midlothian%' ) or (description like
> '%Midlothian%' ) or (clipname like '%Midlothian%' ) or (creator like
> '%Midlothian%' )) and Convert(datetime,rundown_date) >= '05/02/2006' and
> Convert(datetime,rundown_date) <= '02/02/2007' and type=1 and
> content_status=1 order by convert(datetime, rundown_date) asc
>
>
> View is as below:
> CREATE view na_item_view_new as
> select s.story_id,
> u.title as title,
> s.title as story_name,
> u.state,
> [rundown_date]=convert(char(10),u.air_date,101),
> '' as video,
> '' as cg_text,
> SUBSTRING(s.text, 1, 500) AS script,
> SUBSTRING(i.text, 1, 500) as item_text ,
> i.type,
> i.content_status,
> k.keyword,
> i.editorial_description as description ,
> d.description as notes,
> s.editor as creator,
> i.original_material_id as clipname,
> i.ar_material_id as material_id
> from
> (select null as state,null as type, rundown_id, ncs_rundown_id,
> edit_duration, title,
> [air_date]=convert(char(10),air_date, 101),
> edit_start_time=substring(convert(varcha
r(10),
> edit_start_time, 114),1,8) from na_rundown_tbl where rundown_id not in
> (select ref1 from req_state_tbl where type=(select type_id from
> req_type_tbl where type='NA_ST_RUNDOWN'))
> union
> select state, type, rundown_id, ncs_rundown_id, edit_duration, title,
> [air_date]=convert(char(10),air_date, 101),
> edit_start_time=substring(convert(varcha
r(10),
> edit_start_time, 114),1,8) from na_rundown_tbl r left outer join
> req_state_tbl s on r.rundown_id=s.ref1
> where (state=5 AND type=(select type_id from req_type_tbl where
> type='NA_ST_RUNDOWN'))) u
> inner join na_story_tbl s on s.rundown_id=u.rundown_id
> left outer join na_item_tbl i on s.story_id=i.story_id
> left outer join na_itemkeyword_tbl k on i.item_id=k.item_id
> left outer join na_itemdesc_tbl d on i.item_id=d.item_id
>
>
> Regards,
> Harpreet Kaur
> Crispin Corporation
> (919) 367-7911
> (919) 791-9886 (Cell)
>
> -----Original Message-----
> From: Gustav Wiberg [mailto:gustav@hmn.se]
> Sent: Friday, February 02, 2007 1:47 AM
> To: Harpreet; 'php windows'
> Subject: RE: [PHP-WIN] Search results
>
> Hi!
>
> I agree to Armando about the optimasation about the search-query. If you
> show some bit of code (it may be useful only with the actual query used
> to search), we maybe can help you!
>
> Best regards
> /Gustav Wiberg
> HMN Konsult (Sweden)
> http://www.hmn.se/
>
>
>
> -----Original Message-----
> From: Harpreet [mailto:harpreet@crispincorp.com]
> Sent: Thursday, February 01, 2007 10:22 PM
> To: php windows
> Subject: [PHP-WIN] Search results
>
>
> I have a search page on my web application that searches a database with
> 1000's of rows. The search results are returned in like 45-50 seconds.
> If the user goes to another page and then returns to the same page again
> it takes the same amount of time.
>
> Is there a way to use session variables to remember the search results
> and if user comes back to the page with the same criteria it shows the
> static results and not take as long.
>
> Help is needed urgent. Thanks
>
> Regards,
> Harpreet Kaur
>
> --
> PHP Windows Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>

Sponsored Links