onedumbcoder Posted April 4, 2008 Share Posted April 4, 2008 i was wondering if there is anyway i can do this lets say i have the string $e = "1, 3, 4, 5, 6"; then can i do this? "SELECT * FROM user WHERE id !='" . $e . "'"? If not, is there a way? Quote Link to comment Share on other sites More sharing options...
ansarka Posted April 4, 2008 Share Posted April 4, 2008 "SELECT * FROM user WHERE id not in (" . $e . ")"? Quote Link to comment Share on other sites More sharing options...
onedumbcoder Posted April 4, 2008 Author Share Posted April 4, 2008 does that actually work? if so thanks, i just need confirmation because of your "?" at the end :'( Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted April 4, 2008 Share Posted April 4, 2008 For numeric values that should work (you could always try it and see). From the mysql manual - expr IN (value,...) Returns 1 if expr is equal to any of the values in the IN list, else returns 0. If all values are constants, they are evaluated according to the type of expr and sorted. The search for the item then is done using a binary search. This means IN is very quick if the IN value list consists entirely of constants. Otherwise, type conversion takes place according to the rules described in Section 10.2.2, “Type Conversion in Expression Evaluation”, but applied to all the arguments. mysql> SELECT 2 IN (0,3,5,7); -> 0 mysql> SELECT 'wefwf' IN ('wee','wefwf','weg'); -> 1 You should never mix quoted and unquoted values in an IN list because the comparison rules for quoted values (such as strings) and unquoted values (such as numbers) differ. Mixing types may therefore lead to inconsistent results. For example, do not write an IN expression like this: SELECT val1 FROM tbl1 WHERE val1 IN (1,2,'a'); Instead, write it like this: SELECT val1 FROM tbl1 WHERE val1 IN ('1','2','a'); The number of values in the IN list is only limited by the max_allowed_packet value. To comply with the SQL standard, IN returns NULL not only if the expression on the left hand side is NULL, but also if no match is found in the list and one of the expressions in the list is NULL. IN() syntax can also be used to write certain types of subqueries. See Section 11.2.8.3, “Subqueries with ANY, IN, and SOME”. expr NOT IN (value,...) This is the same as NOT (expr IN (value,...)). Quote Link to comment Share on other sites More sharing options...
onedumbcoder Posted April 4, 2008 Author Share Posted April 4, 2008 awesome, so then this should work right, if i want to grab a random user who has not been seen yet. $query = "SELECT * FROM user WHERE id not in (" . $seenIds . ") ORDER BY RAND() LIMIT 1"; $result = mysql_query($query); if(mysql_affected_rows() == 0) { $seenIds = ''; $query = "SELECT * FROM user WHERE 1 ORDER BY RAND() LIMIT 1"; $result = mysql_query($query); } $user = mysql_fetch_array[$result]; $seenIds .= $userinfo['id'] . ", "; Also if i know im only getting back one element is there something else i can use besides mysql_fetch_array() that is more efficient? Thanks! Quote Link to comment Share on other sites More sharing options...
ansarka Posted April 4, 2008 Share Posted April 4, 2008 $query = "SELECT count(*) as counter FROM user WHERE id not in (" . $seenIds . ") ORDER BY RAND() LIMIT 1"; $result = mysql_query($query); $row_number= mysql_fetch_array($result); if($row_number['counter'] > 0) { $seenIds = ''; $query = "SELECT * FROM user WHERE 1 ORDER BY RAND() LIMIT 1"; $result = mysql_query($query); } while($row=mysql_fetch_array($result)) { echo $row['name']; echo $row['age']; } Quote Link to comment Share on other sites More sharing options...
onedumbcoder Posted April 4, 2008 Author Share Posted April 4, 2008 Thank you ansarka for the help. Can you please translate this in english: "count(*) as counter" is that saying create a variable counter that equals to how many elements this query gets back? Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted April 4, 2008 Share Posted April 4, 2008 Pretty much - you're selecting the number of rows that match, and your giving that the name 'counter' in your record set. Personally i'd just use the one query: <?php $query = "SELECT * FROM user WHERE id not in (" . $seenIds . ") ORDER BY RAND() LIMIT 1"; $result = mysql_query($query); $num = mysql_num_rows($result); if($num > 0) { $row = mysql_fetch_assoc($result); echo $row['name']; echo $row['age']; } ?> Quote Link to comment Share on other sites More sharing options...
onedumbcoder Posted April 4, 2008 Author Share Posted April 4, 2008 hmm for some reason its still not working: SELECT * FROM picture WHERE id not in (0, 41, 34, 28, 21, 13, 40, 12, 42, 12, 16, 10, 42) ORDER BY RAND() LIMIT 1 ---- UPDATE --- NM, i had "id" instead of "user_id". Thanks everyone for the help! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.