• We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.

SQL query question

D

dum

Senior member
I have a database table with 4 columns, let's say (animal_type, animal_breed, color, sex) and none of these can be null values when entered.

I have a form that has 4 fields (2 select boxes for type and sex, 2 text boxes for color and breed) that searches the table based on the criteria provided and returns the results.

What I'm wondering is whether or not there is a way to have a single query return the results I want even if I don't put something in each form field. (ie. show all black dogs regardless or breed or sex or show all female animals regardless of other criteria)

my SQL skills are ok, but the only ways I can see to do this is with multiple queries.
 
Look as stored procedures, you can build your query based on the parameters passed in.
Bill
 
if i'm using mySQL will sotred procedures work?

i know on earlier versions they did not support stored procedures.
 
What language are you programming in? Since the input is different each time, you might be better off specifying it in the script.
 
Maybe I'm not seeing something in your explanation of the situation? Let's say you have the table you describe above (animals), and want to retrieve the either of the two datasets you mentioned:

Select *
from animals
where animal_breed = 'dog'
and color = 'black'


or, for the second set:

Select *
from animals
where sex = 'female'

Like I said, I might not be seeing something, but why would you need multiple queries for this?
 
You will either have to programatically build your query (meaning that you would not be able to use a stored procedure), or you would have to use LIKE for each field.

LIKE is not as bad as it seems if your table is relatively small.
 
Originally posted by: Lint21
Maybe I'm not seeing something in your explanation of the situation? Let's say you have the table you describe above (animals), and want to retrieve the either of the two datasets you mentioned:

Select *
from animals
where animal_breed = 'dog'
and color = 'black'


or, for the second set:

Select *
from animals
where sex = 'female'

Like I said, I might not be seeing something, but why would you need multiple queries for this?
Click to expand...

i'm with lint on this one. i'm not sure what the difficulty or the question is.

are you trying to prefill the dropdowns/select box according to what's in the table? is that your question?
 
when the form is submitted as i see it there are 4! different combinations that could be submitted:

all 4 fields have something in them
only 1 of the fields has something in it
2 of the fields have something in them
3 of the fields have something in them


what i'm trying to do is come up with 1 query that will work no matter which one of those situations above occurs.

i know it can be done with a series of simple queries like the ones suggested above, but i'm trying to come up with just 1 query that can, based on the criteria passed through the form, as simple as:

select * from animals
where sex='male'

to something along the lines of:

select * from animals
where sex='male' AND breed='lab' AND type='dog' AND color='black'


does that make sense?
 
select from table where color="black"
select from table where color="black" and sex="m"
select from table where color="black" and sex="m" and type="cat"

etc...

$query = "select from table where";
if (sex was selected in form){
$query = "$query and sex='m'";
}
if (breed was seelcted in form){
$query = "$query and breed='poodle'";
}

etc.
 
Originally posted by: notfred
select from table where color="black"
select from table where color="black" and sex="m"
select from table where color="black" and sex="m" and type="cat"

etc...
Click to expand...


The problem is that he doesn't know which situation he will encounter since users may or may not enter the information. I'm still thinking about this one. It has to be done in the script itself, I think. That's the way I would approach it. Can you query the results of a query? That would be easy enough.
 
Originally posted by: FOBSIDE
Originally posted by: notfred
select from table where color="black"
select from table where color="black" and sex="m"
select from table where color="black" and sex="m" and type="cat"

etc...
Click to expand...


The problem is that he doesn't know which situation he will encounter since users may or may not enter the information. I'm still thinking about this one. It has to be done in the script itself, I think. That's the way I would approach it. Can you query the results of a query? That would be easy enough.
Click to expand...


Didn't read the second half of my post?
 
Originally posted by: notfred

You type slow, it took you 9 minutes to type 2 lines of text 😛
Click to expand...

We're off topic now, but yes, I am slow. I opened the reply window then actually went to get some work done. Who would have thought I would get work done on a weekday? 😉
 
Can you do something in PHP to replace a null field with '%' and then use
SELECT *
FROM animals
WHERE sex LIKE(input1) AND breed LIKE(input2) AND type LIKE(input3) AND color LIKE(input4)

The actual query wouldn't be as efficient this way, but if that's something you can live with you may want to try it.
 
You must log in or register to reply here.

TRENDING THREADS

Back
Top