WHERE equalities

I am impoding an array into my query


what is the sql statement for something like

where color = red or color=blue and shape = square OR shape = circle

I want to return only results where there are red and blue squares and circles

 

SELECT * FROM 
  mytable
WHERE
  color IN ('red', 'blue')
AND
  shape IN ('circle', 'square')

Code (markup):

i tested the code above on my server and it worked

 

that works like a charm but let me ask you

is there a way to say IN (ALL) null treats it as null and % doesn't work.

i want to set the variable to be IN ALL if the variable is not posted.

i tried searching around but couldn't find much

 

then you have to make it a dynamic sql statement

either build up the sql statement in php or whatever you are doing and leave out the where part where you don't have a parameter for

or make a stored procedure that gets color and shape as input params, checks them for null and creates a dynamic statement and executes it

 

that is what i was thinking but i can't quite figure out what to do with my WHERE statement,

I could just go in and say if the variable is not set then load all values but i am trying to make this as universal as possible

 

if you don't know color then just have:

WHERE shape IN ('circle', 'square')

and if you don't know shape then only have:

WHERE color IN ('red', 'blue')

be careful if you don't have anything at all
1. leave out 'where' when you're building a sql string
2. it'll return your whole database

 

I really am having trouble with the logic here.

I can easily do it if I put all values in for IN if the GET varialbe is not set however I am trying to make this code universal

So then I thoght I would make the WHERE only appear if the GET value was set but because there is more than one, then WHERE would have to appear on the first one and the others would need AND

any ideas?

 

Do you want the code to be in php? or another language? There are some ways to do it: you can use an array to store the differrent conditions, and then just implode it after all the checking.

here's an example of how you can do it in php:

// check if any GET variables are set:
if (isset($_GET) && sizeof($_GET) > 0) {
  $sql_conditions = array();
  if (isset($_GET['shape']) {
    $sql_conditions[] = "IN('" . implode("', '", $_GET['shape']) . "')"; // i don't know what the value of the shape textfield is, but for now i assume it's an array
  }
  if (isset($_GET['color']) {
    $sql_conditions = "IN('" . implode("', '", $_GET['color']) . "')"; // i don't know what the value of the shape textfield is, but for now i assume it's an array
  }

  if (sizeof($sql_conditions) > 0) {
    $sql = "WHERE ".implode(' AND ', $sql_conditions);
  }
} else {
  $sql = NULL;
}

// now run the query

PHP:

 

nice code

i didn't think about using implode on the WHERE. Thanks


now i just have to rebuild my get statement for the sort and it is ready freddy....

 

just did it and it worked great!

i can even recreate my GET url with some minor modifications

would it be hard to add 2 text box to this like a From [text box] To [text box]

 

What do you mean exactly? add some extra conditional statements to the query?

 

Yeah here is the problem and you fixed 90% of it


we wanted the multiple select forms that you code allowed but now we need to allow the user to put in a range for a group of numbers, if they want to.

so there will be an another AND or it could be a WHERE if no other GET variable are set

the next statement I would need to add would be

WHERE/AND number BETWEEN GET[] AND GET[]

 

if both numbers need to be set, it's pretty easy:

if (isset($_GET['start_number']) && isset($_GET['end_number'])) {
  $sql_conditions[] = "BETWEEN " . intval($_GET['start_number']) . " AND " . $_GET['end_number'];
}

PHP:

you can just add this after:

  if (isset($_GET['color']) {
    $sql_conditions = "IN('" . implode("', '", $_GET['color']) . "')"; // i don't know what the value of the shape textfield is, but for now i assume it's an array
  }

PHP:

If at least one number should be set, it's gonna be a little trickier:

if (isset($_GET['start_number']) && isset($_GET['end_number'])) {
  $sql_conditions[] = "field BETWEEN " . intval($_GET['start_number']) . " AND " . $_GET['end_number'];
}
elseif (isset($_GET['start_number']) && !isset($_GET['end_number'])) {
  $sql_conditions[] = "field > " . intval($_GET['start_number']);
}
elseif (!isset($_GET['start_number'])  && isset($_GET['end_number'])) {
  $sql_conditions[] = "field < " . intval($_GET['end_number']);
}

PHP:

 

hey you are helping me on both threads. i really appreciate. i have learned alot in the last two days

 

is that intval to protect again sql injections?

 

No, not really: intval is a function that grabs the integer value of a certain variable. It will help in this case as it will remove all non-numeric characters from the entered string. So when i enter 123-hi-homie, intval will return 123.
To prevent SQL injection with strings, you have to use mysql_real_escape_string or mysql_escape_string (sometimes mysql_real_escape_string is not available). here are the links for more information on these functions (they don't work as i can't post active links yet ):
http://www.php.net/mysql_real_escape_string
http://www.php.net/mysql_escape_string

PS: I added some extra stuff to my previous post

 

man you have the patience of Job

but you truly are helping me alot

I see what you added. Does that take care of if they put the larger value in for the FROM etc?

Also I am curoius, I am tryng not to use a drop down for the To and From form fields

however if i use a text box, it always appears in my URL with a null , is there a way to prevent this or would i have to set some defualt values or something.

 

my pleasure

No, not atm, but there's an easy way to fix that. Just replace this:

if (isset($_GET['start_number']) && isset($_GET['end_number'])) {
  $sql_conditions[] = "field BETWEEN " . intval($_GET['start_number']) . " AND " . $_GET['end_number'];
}

PHP:

by this:

if (isset($_GET['start_number']) && isset($_GET['end_number'])) {
  $getValues = array($_GET['start_number'], $_GET['end_number']);
  sort($getValues);
  $sql_conditions[] = "field BETWEEN " . intval($getValues[0]) . " AND " . $getValues[1];
}

PHP:

You see, what it does is it creates an array which contains both the start and the end number. the function sort will sort the values from small to large, therefore the first number (array index = 0) will always be the smallest of the two, and the second number is the highest one (array index = 1)

what do you mean with "null": the string "null", or just an empty value. Like this:
www.my_url.com/page.php?to=&from=1
or
www.my_url.com/page.php?to=null&from=1
If it's the first one, than there's no problem. php converts from string to ints in no time, and an empty string is the same as the integer 0 for php. if it's the second one, you will have to add another if to check if the value doesn't equal "null"

 

i see, so the text entries will appear in the URL not matter whta
mine appears this way

www.my_url.com/page.php?to=null&from=1

I juts need to set defualt values, but my database is ever chaning so i am not sure what my maximum would be

 

are you submitting your form using javascript? cause if you just let html handle the submission, no value should be entered, just like the url i posted first:
www.my_url.com/page.php?to=&from=1

Don't ever trust default values . NEVER trust a visitor: you have to check the value in your form processing page, no matter what