Jump to content


Photo

SELECT Multiple Values from 1 field?


  • Please log in to reply
13 replies to this topic

#1 Hilly_2004

Hilly_2004
  • Members
  • PipPipPip
  • Advanced Member
  • 63 posts

Posted 25 March 2006 - 01:51 AM

Is it possible?

Just that my current query is like this:

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

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:

$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')"; 

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

#2 AV1611

AV1611
  • Members
  • PipPipPip
  • Advanced Member
  • 997 posts

Posted 25 March 2006 - 03:36 AM

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

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

#3 Hilly_2004

Hilly_2004
  • Members
  • PipPipPip
  • Advanced Member
  • 63 posts

Posted 25 March 2006 - 01:56 PM

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.

#4 swatisonee

swatisonee
  • Members
  • PipPipPip
  • Advanced Member
  • 253 posts

Posted 25 March 2006 - 03:49 PM

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

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

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


Shishya

#5 Hilly_2004

Hilly_2004
  • Members
  • PipPipPip
  • Advanced Member
  • 63 posts

Posted 25 March 2006 - 04:05 PM

My coding is like:
      <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>

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.

#6 swatisonee

swatisonee
  • Members
  • PipPipPip
  • Advanced Member
  • 253 posts

Posted 25 March 2006 - 04:42 PM



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

HTH
Shishya

#7 Hilly_2004

Hilly_2004
  • Members
  • PipPipPip
  • Advanced Member
  • 63 posts

Posted 25 March 2006 - 04:50 PM

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

#8 swatisonee

swatisonee
  • Members
  • PipPipPip
  • Advanced Member
  • 253 posts

Posted 25 March 2006 - 04:57 PM

what error are you getting when you run

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

#9 Hilly_2004

Hilly_2004
  • Members
  • PipPipPip
  • Advanced Member
  • 63 posts

Posted 25 March 2006 - 05:00 PM

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).

#10 swatisonee

swatisonee
  • Members
  • PipPipPip
  • Advanced Member
  • 253 posts

Posted 25 March 2006 - 05:13 PM

<?
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


$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';

Shishya

#11 Hilly_2004

Hilly_2004
  • Members
  • PipPipPip
  • Advanced Member
  • 63 posts

Posted 25 March 2006 - 05:20 PM

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.

#12 Hilly_2004

Hilly_2004
  • Members
  • PipPipPip
  • Advanced Member
  • 63 posts

Posted 25 March 2006 - 11:34 PM

Still having problems with it, any help guys?

#13 toplay

toplay
  • Staff Alumni
  • Advanced Member
  • 973 posts

Posted 25 March 2006 - 11:52 PM

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.


#14 Hilly_2004

Hilly_2004
  • Members
  • PipPipPip
  • Advanced Member
  • 63 posts

Posted 26 March 2006 - 02:30 AM

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users