WHERE IN does not work with LIMIT

SELECT `name` FROM `users` WHERE `id` in (2, 3, 4, 6) LIMIT 2

does not work. it displays all 4. any ideas why or how to make it work?

thanks.

Strange....works fine for me

SELECT set1 FROM sample WHERE id IN (1,2,3,4,5,6) LIMIT 2

Returned only 2 values.

What context are you running this query in?

thanks for looking into this with me.

im not sure what you are asking, do you want surrounding code?

i declare $query to grab all suitable rows

then do a postcode search, which returns the ID's of the rows that are within the postcode area

then a redeclare $query with the ids in the stated format:

SELECT `name` FROM `users` WHERE `id` in (2, 3, 4, 6) LIMIT 2

yeah, could you fling up said code?  Also - is your limit hard set to 2 or is it coming from a variable?

			$query = "SELECT * FROM `users` WHERE {$refine_price} `Level_access`=2 AND `active`=1 AND `business_type` LIKE '%1%' AND `unavailable_dates_array` NOT LIKE '%$trimmed%' AND `unavailable_dates_array` !='' AND ID !='".mysql_real_escape_string($_SESSION['user_id'])."' ORDER BY RAND()";

		if($_POST['u_postcode'] != ''):
			$result = mysql_query($query) or die ("Error ".mysql_errno().": ".mysql_error()."\nQuery: $query");
			//this int is to help with the comma spacing later on EDIT LOOK TO LINE 46
			//$i = 1;
			//loop the query
			while ($row = mysql_fetch_array($result)) {
				//get the photographers postcode and split it into 2 pieces
				$p_postcode = explode(' ',$row['address_post_code']);
				//run the two postcodes throught the function to determin the deistance
				$final_distance = return_distance($u_postcode[0], $p_postcode[0]);
				//start the variable so we can add to it later on
				$photographers_inrange = "";
				//declare this variable to help with the comma spacing too EDIT LOOK TO LINE 46
				//$i2 = 1;
				//if the distance is smaller or equal to the distance the photographer covers
				if($final_distance <= $row['cover_distance']){
					//get their id
					$photographers_inrange .= $row['ID'].',';
					//EDIT: this method does not work when just one result is returned. now i use substr -1.
					//if this isnt the last result
					//if($i++ <= $i2++){
					//then add a comma for the sql statement
					//$photographers_inrange .= ',';
					//}
				}
				//declare the variables the if statement made into one
				$query_inrange .= $photographers_inrange;
			}
			if(!empty($query_inrange)){
			//get all fields relating to the photographers that are in range
			$query = "SELECT * FROM `users` WHERE `id` in (".substr($query_inrange, 0, -1).") LIMIT 10";}else{
			//return blank //not very elegant, will work on a solution
			$query = "SELECT * FROM `users` WHERE `id`=0";};
		endif;

the limit bit is hardcoded.

ps. i know order by rand() is not great, nor is select * thanks.

You want a random 2 of those 4 values?

well, i want a random 10 of all the values yes.

thanks

10? You said 2.

i said 2 initially cos i didnt want to write out over 10 fake id's in the inital post.

but now you have the source code you can see i may (and do) return over 10 in the postcode match.

which is the value of $query_inrange before this lines?

                               // ECHO $query_inrange here
			if(!empty($query_inrange)){
			//get all fields relating to the photographers that are in range

also... I guess that $refine_price variable is ending in a valid operator here right?... or you have a typo?

$query = "SELECT * FROM `users` WHERE {$refine_price} `Level_access`=2 AND `active`=1 AND `business_type` LIKE '%1%' AND `unavailable_dates_array` NOT LIKE '%$trimmed%' AND `unavailable_dates_array` !='' AND ID !='".mysql_real_escape_string($_SESSION['user_id'])."' ORDER BY RAND()";

thanks for your reply.

$query_inrange could be anything but will always follow this format: 1, 2, 3, 4, 5, 6,

$refine_price ends with AND - that works fine...

duuuurrrrrr.

got it. it was because i wasnt entering a postcode so it obviously wasnt going through the if statement.

thanks guys... and sorry!

Joe.