SELECT Multiple Values from 1 field?

[Hilly_2004]

Is it possible?

Just that my current query is like this:

[code]
$query_count = "SELECT count(*) as cnt FROM Items WHERE (Items.Age_Range = '$Age') AND (Items.Gender_Range = '$Gender') AND (Items.Price_Range = '$Price')";
[/code]

However for the Gender Range field I want it also to select any items that has 1 in there. I thought it would appear like:

[code]
$query_count = "SELECT count(*) as cnt FROM Items WHERE (Items.Age_Range = '$Age') AND (Items.Gender_Range = '$Gender' AND Items.Gender_Range = '1') AND (Items.Price_Range = '$Price')";
[/code]

However it's just saying no records found, any idea where Im going wrong?

[AV1611]

How can this statement ever be true unless $Gender = '1'?
[code]
(Items.Gender_Range = '$Gender' AND Items.Gender_Range = '1')
[/code]

This would be valid, but it may not be what you want...
[code]
(Items.Gender_Range = '$Gender' AND Items.Gender_Range like '%1%')
[/code]
This will find records where Gender_Range = $Gender AND $Gender contains a 1 in it...

[Hilly_2004]

There are 3 states.

1 - Is ALL GENDERS
2 - Is MALES
3 - Is FEMALES

If they select '1' (All Genders) information comes up for All Genders, however if they select '2' you would expect any items for Males to come up which it does however it doesn't include the All Genders as well.

Thats why Im having to do this and as you say the code you suggested doesn't work.

[swatisonee]

but how have you coded this variable ? if its like below then it should work else not.

[code]
<select name="gender">
<option value="">[Select One]
<option value="1">1: All
<option value="2">2: Males
<option value="3">3: Females
</select> </td>[/code]

and then make sure $result = mysql_query($query) or die (mysql_error()) ; so that you can see where the error is coming

[Hilly_2004]

My coding is like:
[code]
      <select name="Gender" size="1" id="Gender">
        <option selected value="1">All Genders</option>
        <option value="2">Male</option>
        <option value="3">Female</option>
      </select>
[/code]

So, yes very similar.

The only error that comes up is the one I made saying "Sorry no items found" there is no error in the query.

[swatisonee]

<option value="">[Select One]
<option selected value="1"> should be <option value="1">

HTH

[Hilly_2004]

I don't think that would change things, all its saying is thats the one thats selected when the form loads up.

[swatisonee]

what error are you getting when you run

$result = mysql_query($query) or die (mysql_error()) ;

[Hilly_2004]

Like I said. None at all.

The only error Im getting is one I produce myself saying that this isn't anything that matches the query (which I know for a fact there is).

[swatisonee]

<?
ini_set('error_reporting', E_ALL);
?>

put that at the top of your page and see if any error shows up. also try putting all the field names in backticks like `Items.Gender_Range ` and see if it helps.

Have you tried


[code]$result = mysql_query($sql) or die ($sql . '<br />' . mysql_error());

// check the query returned a valid result, which could be empty but still valid
if (!$result) {
   echo ' there was a problem running this query<br />' . $sql . '<br />it did not return a valid result';
  //break;
}

// now find out how many rows it returned
$count = mysql_num_rows($result);
echo $sql . '<br />this query returned ' . $count . ' rows';[/code]

[Hilly_2004]

As much as I appreciate your help mate, I think we're off track.

Ive been thinking it could be like:

AND Items.Gender_Range = 1
or
AND Items.Gender_Range in ( 2, 1 )
or
AND Items.Gender_Range in ( 3, 1 )

Notice no quotes. However because Im using a variable I have to use them and that doesn't work.

[Hilly_2004]

Still having problems with it, any help guys?

[toplay]

From what I understand of your post you would simply use OR instead of AND. Change this part:

(Items.Gender_Range = '$Gender' AND Items.Gender_Range = '1')

to this:

(Items.Gender_Range = '$Gender' OR Items.Gender_Range = '1')

or use the "in" like you show, but you can still use your variable like so:

AND Items.Gender_Range in (1, $Gender)


The title of your topic is a bit misleading. I'm assuming you're not really talking about having multiple values in one column, like Gender_Range can have "1, 2" as it's content. But rather you're asking how to access data specifying multiple choices for a column where the column can only contain one value.

Always try queries in mysql command prompt, phpmyadmin, or similar tools before putting them in PHP.


hth.

[Hilly_2004]

That works a treat mate, seems simple looking back on things. Sorry if the topic title was misleading, didn't know how to word it properly.