Major Query Dilema

[pashadowops]

Hello all,

I have a question that I can't seem to find the answer to.

I have created a database for our online directory.

The database was built using MySQL 3.4x and because of server constraints we are connecting to the DB using ASP. The constraint being it's a Winblows box instead of Linux.

Now to the fun part I have written the queries out to make sure that all the search functions of the directory will work however, since this directory will have the capability to be searched by multiple fields, will I have to write a query for each and every search probability or is there a way to setup one or two queries and have the ASP page input the search parameter into the query then execute it?

Below I have given an sample of one of the queries I am using. Where it says 'input' is where I would like the ASP input form to insert the search criteria and then when the submit button is hit , Run the query:

mysql> Select company.company_name from company, catcomlnk where company.company_ID = catcomlnk.company_ID and catcomlnk.cat_ID = 'input' and county.county_ID = 'input' and keyword.kw_ID = 'input' order by company_name;

Can you imagine if I had to write a similar query for each and every search possibilty? HOLY COW!!!!! I understand that SQL offers something called parameter search or query, something to that effect. I haven't found anything in MySQL that resembles that. Is there?

If what I am asking can't be done with MySQL, what am I to do? This directory goes live online in less then a week. I know it's ambitious to take on something of this scale from scratch but that's the way I do things. Plus I've only been playing with MySQL for about 4 months. If you want an Idea of what the search criteria I will be working with, Please visit the site under construction at:

http://www.easternpaonline.com.

Thanks in advance for any help anyone can offer.
Man I hope it's something simple like my last question on the my.cnf file.

 

[symbiotic]

You sound panicked, man, I can't quit tell what you're asking. If you mean what I think you mean, it's simple. You mean you have these three inputs, company_name, county, and keyword, and you don't want to write nine queries for each possible combination of inputs the user might choose to use or not use?

If I totally misunderstood you, calm down, take a deep breath and try again

 

[pashadowops]

Symbiotic,

You hit the nail on the head. Can you hear me taking a deep breath....ahhhhhhh, ok....now....is there something I can do with MySQL to make my life easy or, are my fingers going to fall off from all the typing I'm gonna have to do?

Thanks for your response.

 

[danielhozac]

Wouldn't something like this work? (Psuedo code since I've never had to use ASP, thank god)

query = "Select company.company_name from company, catcomlnk where company.company_ID = catcomlnk.company_ID";

if (catcomlnk.cat has input)
    query += " and catcomlnk.cat_ID = 'input'";
if (county.county_ID has input)
    query += " and county.county_ID = 'input'";
if (keyword.kw_ID has input)
    query += " and keyword.kw_ID = 'input'";

query += " order by company_name";

//Daniel

 

[pashadowops]

Daniel,

I'll try anything once, twice if I like it. As I am not a programmer, how exactly would that pseudo code fit into the query? remember I'm only using MySQL for about 4 months. Thanks.

 

[symbiotic]

Daniel is exactly right. It's very simple. I can't tell you the exact server side code you're gonna use either because it's been a real long time since I've used ASP.

The pseudo-code doesn't fit into the query, the query fits into the pseudo-code. You have to use server-side VBScript or whatever ASP uses to basically say (I'll try to make the pseudo code closer to VBS but my syntax is rusty so don't quote me)

<%
//Start query
SQLquery = &quot;Select company.company_name from company, catcomlnk where company.company_ID = catcomlnk.company_ID&quot;

//If company field has input, then add it to query
//Don't remember how ASP gets variables from a form,
//look it up

if cat != '' //or cat is not valid input, always test
then
query = query + &quot; and catcomlnk.cat_ID = 'input'&quot;
end if

%>

And so on for each input. Don't know how to make it simpler, just test that each input has (valid) data, then add it to the query

 

[pashadowops]

That's awesome. I'll play with that over the extended weekend and let you all know whow it turns out. Thanks again for all of help.