Hello experts,
I have been struggling for days to solve this problem and every
suggestion I find via Google does not work for me. There is probably a
solution out there that will do what I want, but I probably have not
properly implemented the solutions I find.
I am trying to make a page to allow personnel the ability to search our
backend DB (which is Ingres 2.0) through a web interface. Ingres has a
cgi program called ICE that can receive SQL queries and return data to
format. I have lots of pages setup that query correctly and work really
well. The one I am struggling with is searching our parts database
because I want to allow wildcard searches. The Ingres wildcard is a %
sign so I can input 011% and it will find any item number that starts
with 011. I can use %011% and it will find any part with 011 in the
string. I only care about three fields item_no, description_1, and
description_2 for searching.
I have set up a simple form that queries properly using only hidden
variables and GET and no JS, but the wildcards have to be explicitly
entered in the form. I want use JS to validate the form and add the
wildcards for the user.
So, here is the incredibly ugly code I hacked together that almost
achieves what I want. The problem is using either GET or POST for the
action, the query the DB receives does not have "item_no like '011%' "
instead it is "item_no like '011' " so it returns ONLY the item with
item_no EXACTLY 011. Not helpful. If I use an alert to display the
send_string before the submit, it has wildcards in the string as I
expect but for some reason when I look in the DB logs and the apache
logs the query has no % and the GET statement passed to apache has no %
signs. What can I do to validate the form and get the strings passed to
the query with the % wildcards intact?
I want the user to only have to fill in what he cares about and then I
can validate and correctly compose the SQL query from there. The most
common example is to fill in the item_no with a string. I then make the
two description fields a single % so the query returns any items that
match the item_no without caring about the description fields. That is
why the code checks if all fields in one group are empty, it makes the
contains parameter for this field a single %.
Anyone who can steer me the correct way will be much appreciated.
<SCRIPT LANGUAGE="Javascript">
<!--
function checkme(parts) {
var start_string=document.forms[0].start_item_no.value;
var length_start_string=start_string.length;
var contains_string=document.forms[0].contains_item_no.value;
var length_contains_string=contains_string.length;
var end_string=document.forms[0].end_item_no.value;
var length_end_string=end_string.length;
if (length_start_string != 0) {start_string=start_string+"%"};
if (length_contains_string != 0)
{contains_string="%"+contains_string+"%"};
if (length_end_string != 0) {end_string="%"+end_string};
if (length_start_string== 0 && length_contains_string==0 &&
length_end_string==0) {contains_string="%"};
var
send_string="start_item_no="+start_string+"&contai ns_item_no="+contains_string+"&end_item_no="+end_s tring;
var start_desc1=document.forms[0].start_description_1.value;
var length_start_desc1=start_desc1.length;
var contains_desc1=document.forms[0].contains_description_1.value;
var length_contains_desc1=contains_desc1.length;
var end_desc1=document.forms[0].end_description_1.value;
var length_end_desc1=end_desc1.length;
if (length_start_desc1 != 0) {start_desc1=start_desc1+"%"};
if (length_contains_desc1 != 0)
{contains_desc1="%"+contains_desc1+"%"};
if (length_end_desc1 != 0) {end_desc1="%"+end_desc1};
if (length_start_desc1==0 && length_contains_desc1==0 &&
length_end_desc1==0) {contains_desc1="%"};
send_string=send_string+"&start_description_1="+st art_desc1+"&contains_description_1="+contains_desc 1+"&end_description_1="+end_desc1;
var start_desc2=document.forms[0].start_description_2.value;
var length_start_desc2=start_desc2.length;
var contains_desc2=document.forms[0].contains_description_2.value;
var length_contains_desc2=contains_desc2.length;
var end_desc2=document.forms[0].end_description_2.value;
var length_end_desc2=end_desc2.length;
if (length_start_desc2 != 0) {start_desc2=start_desc2+"%"};
if (length_contains_desc2 != 0)
{contains_desc2="%"+contains_desc2+"%"};
if (length_end_desc2 != 0) {end_desc2="%"+end_desc2};
if (length_start_desc2==0 && length_contains_desc2==0 &&
length_end_desc2==0) {contains_desc2="%"};
send_string=send_string+"&start_description_2="+st art_desc2+"&contains_description_2="+contains_desc 2+"&end_description_2="+end_desc2;
document.parts.action="http://192.168.254.238/cgi-bin/ice/macro_partsearch.html?"send_string;
document.parts.submit();
} //submit function
-->
</SCRIPT>
<FORM NAME="parts" METHOD="GET" onSubmit="javascript
:checkme(this);">
<P>
Enter a string that the item number begins with
<INPUT TYPE=text NAME="start_item_no" VALUE=""><br>
Enter a string that the item number contains
<INPUT TYPE=text NAME="contains_item_no" VALUE=""><br>
Enter a string that the item number ends with
<INPUT TYPE=text NAME="end_item_no" VALUE="">
<br><p>
Enter a string that description 1 begins with
<INPUT TYPE=text NAME="start_description_1" VALUE=""><br>
Enter a string that description 1 contains
<INPUT TYPE=text NAME="contains_description_1" VALUE=""><br>
Enter a string that description 1 ends with
<INPUT TYPE=text NAME="end_description_1" VALUE="">
<br><p>
Enter a string that description 2 begins with
<INPUT TYPE=text NAME="start_description_2" VALUE=""><br>
Enter a string that description 2 contains
<INPUT TYPE=text NAME="contains_description_2" VALUE=""><br>
Enter a string that description 2 ends with
<INPUT TYPE=text NAME="end_description_2" VALUE="">
<br><p>
<INPUT TYPE="submit" VALUE="Find">
<CENTER>
</CENTER>
</FORM>
