Jump to content

SELECT Multiple Values from 1 field?


Hilly_2004

Recommended Posts

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?
Link to comment
Share on other sites

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...
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.
Link to comment
Share on other sites

<?
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]
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.