?
Solved

Urgently need help on building search engine

Posted on 2009-04-05
12
Medium Priority
?
288 Views
Last Modified: 2013-12-16
Having input text problems.
I've created the search form, results form and frame form. When i input any alphabet or number onto the textbox and click search, the outcome is not what i expected instead it shows all the results from the database table. What i want is that when i type anything, be it an number or an alphbet that is same to the data of a fieldname in rows(i.e 350v221, 350v265cc, a135vod etc..) inside the textbox, it will show the results of that particular files only. My database table have fieldnames like, DOC_NO (containing data of [i.e 350v000, 350v2587, 350v226 etc..]) [IN ROWS] ), DESCRIBE, REMARKS and STATUS. Also i got some tables that have many data (in number of rows) that consist of more than 20,000 information. My database table's name are saved as the starting number/alphbet (ie. 335a.dbm, 335b.dbm, 335c.dbm, 360V.dbm, wei.dbm, ght.dbm etc...). So is there any simple ways to query for these data? I've created frames so as to show the resulting page on the right frame and the searching page on the left frame. How do i link it together so as the results will show only on right frame even it get refresh?
1-Quotation.jpg
2-001.jpg
3.jpg
4.jpg
0
Comment
Question by:alexanderax
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 16

Expert Comment

by:duncancumming
ID: 24072859
Just a word of advice.  You're usually better pasting your code as text, either in the main text box or in the 'Attach Code Snippet' box.  That way anyone answering your question won't have to manually retype anything.  

Also "How do i link it together so as the results will show only on right frame even it get refresh?" is really a second related question.  The main question is just how do you get your query to work.  

When you say
WHERE doc_no LIKE %#form.txtNumberInput#%

if the user types in say a  3, that will return all things where doc_no is like:
3
123
131
300
333
etc.  Basically anything with a 3 anywhere in it.  If you only want things that start with a 3, take off the first %
WHERE doc_no LIKE #form.txtNumberInput#%

That way you will only get things like:
3
31
333
300
etc
0
 

Author Comment

by:alexanderax
ID: 24074111
Thanks for your advice. Next time i will post it in the text. Anyway i'm not sure why i got error saying that txtNumberInput is undefined. Below the codes on snippet are abit different from the above as i just edited it. I still do not understand why i cannot get the outcome of it. Sometimes when i changed the code, there's no error but only that cannot get the output of the results that i expected.

Thank you for your help.
<b><!---Search_results.cfm---></b>
 
<CFset index="1">
 
<cfif not IsDefined("Form.txtNumberInput")> <!---If there's no input been type onto textbox, it will do the following code--->
 
    <cfquery name="get_FirstNo" datasource="DSN_db1_IWS">
     select *
     from 335v where doc_no like '%#form.txtNumberInput#%'
     order by doc_no
     </cfquery>
 
    <cfelseif IsDefined("form.txtNumberInput")>
    <cfquery name="get_FirstNo" datatsource="DSN_db1_IWS">
     select *
     from 335v where doc_no like '%#form.txtNumberInput#%'
     order by doc_no
     </cfquery>
 
     <cfquery name="get_total" datatsource="DSN_db1_IWS"> 
     select *
     from 335v where doc_no not in ('abc')
     </cfquery>
</cfif>
 
    <cfif not IsDefined("form.ddlnumber")>
    <cfquery name="get_FirstNo" datasource="DSN_db1_IWS">
     select *
     from 335v where doc_no like '%#A#%'
     order by doc_no
     </cfquery>
 
     <cfif IsDefined("form.ddlnumber")>
     <cfquery name="get_FirstNo" datasource="DSN_db1_IWS">
     select *
     from 335v where doc_no like 'b%'
     order by doc_no
     </cfquery>
 
    <cfquery name="get_total" datatsource="DSN_db1_IWS"> 
     select *
     from 335v
    </cfquery>
 
     <cfelseif #form.ddlnumber# is "Display Number">
     <cfquery name="get_FirstNo" datasource="DSN_db1_IWS">
     select *
     from 335v where doc_no like %#form.txtNumberInput#%
     order by doc_no
     </cfquery>
 
    <cfquery name="get_total" datatsource="DSN_db1_IWS"> 
     select *
     from 335v
    </cfquery>
    </cfif>
 
<body bgcolour="A7EF92">
<table width....>
<tr>
<th width......>Number of files Generated:</br>
<font....><cfoutput>#get_total.qty#</cfoutput>
</table><br>
 
<table>
<tr>
<td...><font....>S/N</font></td>
<td...><font....>DOC_NO</font></td>
</tr>
 
<cfoutput query="get_FirstNo">
<tr>
<td align=""....>
<cfScript>
 
        writeoutput(index);
        index=index+1;
</cfscript>
</td>
<td.....><a href="../drawing search/searching.cfm?Doc_no=#'txtNumberInput'#" target="rightBottomFrame">#get_FirstNo.doc_no#</a></font></td>
 
<td....><font..>#get_FirstNo.DESCRIBE#</font></td>
<td....><font..>#get_FirstNo.STATUS#</font></td>
<td....><font..>#get_FirstNo.REMARKS#</font></td>
</TR>
</cfoutput>
</table>
</body></html>
 
<b><!---searching.cfm</b>
 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
 
<html>
<head>
<title>Searching</title>
</head>
 
<body bgcolor....>
<cfif not IsDefined("Form.txtNumberInput")> 
 <cfquery name="get_FirstNo" datasource="DSN_db1_IWS">
     select *
     from 335v where doc_no like 'A%'
     order by doc_no
 </cfquery>
 
<cfelseif IsDefined("Form.txtNumberInput")> 
 <cfquery name="get_FirstNo" datasource="DSN_db1_IWS">
     select *
     from 335v where doc_no like 'h%'
     order by doc_no
 </cfquery>
</cfif>
<cfquery name="get_FirstNo" datasource="DSN_db1_IWS">
     select *
     from 335v 
</cfquery>
 
<cfquery name="get_total" datatsource="DSN_db1_IWS"> 
     select count(*) as qty
     from 335v
     </cfquery>
 
<form action="search_results.cfm" method="post" target="rightBottomFrame">
<font>...
</font>
<input name="txtNumberInput" type="text" size="25">
<br>
</form></body></html>
 
 
     

Open in new window

0
 
LVL 16

Expert Comment

by:duncancumming
ID: 24074859
ok, firstly
<cfif not IsDefined("Form.txtNumberInput")>

txtNumberInput is an input type of text, right?  Even if you don't type anything in, and submit the form, this value will always be defined.  Just it'll be an empty string.  Change that line to
<cfif not Len(Form.txtNumberInput)>


Secondly, the query you have immediately after that relies on Form.txtNumberInput, which you thought wasn't even defined?  Not sure what you were thinking of there.  What really happens is
select *
     from 335v where doc_no like '%%'
     order by doc_no

i.e. it'll get every row in the table I think.


Don't bother with this.  WriteOutput is sort of deprecated these days.
<cfScript>
 
        writeoutput(index);
        index=index+1;
</cfscript>
There's a much easier way, just output #get_FirstNo.CurrentRow#

0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

Author Comment

by:alexanderax
ID: 24074980
yes. txtNumberInput is specific as the (input name). I already edited the code but it still stated that i'm having error. ELEMENT txtNumberInput is undefined in form. error search_results.cfm: Line5
<cfif not IsDefined("Form.txtNumberInput")> Why is that so?
0
 
LVL 16

Expert Comment

by:duncancumming
ID: 24079582
Either your page has no Form struct, or you've maybe made a typo in the fieldname and there is no field called txtNumberInput in your Form struct.
0
 

Author Comment

by:alexanderax
ID: 24083181
What do you mean by fieldname anc? And how does form structure looks like? Can help me do my edit my code? Thanks for helping
0
 
LVL 16

Expert Comment

by:duncancumming
ID: 24084457
At the top of search_results.cfm, put this:
<cfdump var="#Form#">

That will output the entire form structure.  Does it include a field called txtNumberInput ?  
0
 

Author Comment

by:alexanderax
ID: 24084519
I guess so. txtNumberInput is the name for user to input any text or number so that it will take that value and output on the results page.
0
 

Author Comment

by:alexanderax
ID: 24085107
what is the use of cfdump? when i type it on top of the search_results.cfm, it still having the same error. txtNumberInput is undefined in form.
0
 
LVL 16

Accepted Solution

by:
duncancumming earned 120 total points
ID: 24110825
So the cfdump didn't work, or did it output anything at all?  If so, what?

Instead of this line:
<cfif not IsDefined("Form.txtNumberInput")>
Try
<cfif not StructKeyExists(Form, "txtNumberInput")>
0
 

Author Comment

by:alexanderax
ID: 24128175
Sorry took quite long to reply. Last week i tried myself my own coding because i'm running out of time and i managed to get it myself. Can i change questions so that i can give you points? Below are the codes that i edited myself

<b><!---Search_results.cfm---></b>
 
<CFset index="1">
 
    <cfquery name="get_FirstNo" datasource="DSN_db1_IWS">
     select *
     from 335v 
	<cfif #txtNumberInput# is not "">
	where doc_no like <cfoutput>'%#txtNumberInput#%'</cfoutput>
     	order by doc_no
	</cfif>
     </cfquery>
 
     <cfquery name="get_total" datatsource="DSN_db1_IWS"> 
     select count(*) as CurrentRows
     from 335v
     </cfquery>
 
<meta http-equiv="Contecnt-Type" content="text/html; charset=iso-8859-1"><style type="text/css">
body {background-color=#A7EF9228bdee;
}
.style2{color:#b51111}
</style></body>
 
<table width....>
<tr>
<th width......>Number of files Generated:</br>
<font....><cfoutput query="get_total"><b>#get_total.CurrentRows#</cfoutput>
</tr></th></table><br>
 
<table width...>
<tr>
<th width.....><font...>Search Results:</font></th>
 
<tr>
<td width...><font....>S/N</font></td>
<td...><font....>DOC_NO</font></td>
<td...><font....>DESCRIPTION</font></td>
<td...><font....>STATUS</font></td>
<td...><font....>REMARKS</font></td>
</tr>
 
<cfoutput query="get_FirstNo">
<tr>
<td align=""....>
<cfScript>
 
        writeoutput(index);
        index=index+1;
</cfscript>
 
</td>
<td.....><a target="rightBottomFrame" href="../drawing search/mainSearching.cfm?Doc_no=#get_FirstNo.doc_no#">#get_FirstNo.DOC_NO></a></font></td>
<td....><font..>#get_FirstNo.DESCRIBE#</font></td>
<td....><font..>#get_FirstNo.STATUS#</font></td>
<td....><font..>#get_FirstNo.REMARKS#</font></td>
</TR></cfoutput></table></body></html>
 
 
 
<b><!---searching.cfm</b>
 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Searching</title>
<meta http-equiv="Contecnt-Type" content="text/html; charset=iso-8859-5"><style type="text/css">
body {
	background-color:#28bdee;
}
.style1{
	color: #9be094;
	font-weight: bold;
}
.style5{color:#C0D259}
.style6{color:#D1DE87}
.style7{color:#C7D76F}
</style></head>
 
<body class="sub"><br><br>
<font color....><span class="style5">&nbsp;<span class="style6"><strong>Document&nbsp;Number:</strong></span><br><br>
 
<form action="search_results.cfm" method="post" target="rightBottomFrame">
<font color="#FFFFFF"></font>&nbsp;&nbsp;
<input name="txtNumberInput" type="text" size="25" style="width:70%;"><br><br>
&nbsp;&nbsp;&nbsp;
<input type="Submit" Name="btnNumber" value="Display Number" style="width:70%;"><br><br>
<input name="btnReset" type="reset"  value="clear" style="width:70%;"><br><br>
&nbsp;&nbsp;&nbsp;<br><br></td></tr>
</table></form></body></html>

Open in new window

0
 

Author Closing Comment

by:alexanderax
ID: 31566792
Anyway thanks for your help
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
This article provides a case study on how our local youth baseball league deployed a new website, including the platform selection, implementation and benefits to the league.
The purpose of this video is to demonstrate how to integrate Mailchimp with Facebook. This will be demonstrated using a Windows 8 PC. Mailchimp and Facebook will be used. Log into your Mailchimp account. : Click on your name. Go to Account Setti…
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Go t…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question