WHERE IN does not work with LIMIT

[JKG]

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.

[Muddy_Funster]

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?

[JKG]

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

[Muddy_Funster]

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

[JKG]

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

[fenway]

You want a random 2 of those 4 values?

[JKG]

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

 

thanks

[fenway]

10? You said 2.

[JKG]

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.

[mikosiko]

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()";

 

[JKG]

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

[JKG]

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.