Random row from db where not equal to....

[pointsplat]

$query2 = mysql_query ("SELECT * FROM $db_tablename WHERE username != '$nouser[0]' AND username != '$nouser[1]' AND
	username != '$nouser[2]' AND username != '$nouser[3]' AND username != '$nouser[4]' AND username != '$nouser[5]' AND
	username != '$nouser[6]' AND username != '$nouser[7]' AND username != '$nouser[8]' AND
	username != '$nouser[9]' ORDER BY RAND() LIMIT 1");

I am not sure why this code is returning an error it is prolly something blatantly wrong with the SQL but as I have taught myself SQL and only know what I read im not sure how to execute this command.

All I need to do is select a random row from the database where username!=$nouser[0-9]. I tried the code below and will be searching the net once again for help but if anyone in the meantime can help me with this I get an SQL error.

	$query2 = mysql_query ("SELECT * FROM $db_tablename WHERE username != '$nouser[0]',username != '$nouser[1]',
	username != '$nouser[2]',username != '$nouser[3]',username != '$nouser[4]',username != '$nouser[5]',
	username != '$nouser[6]',username != '$nouser[7]',username != '$nouser[8]',
	username != '$nouser[9]' ORDER BY RAND() LIMIT 1");

[laserlight]

You shouldnt be using '!=' in SQL, use '<>'

[stolzyboy]

in this instance if $nouser is a normal array, not a mysql_fetched array, i would use php's

array_rand()

to get a random value before you select from the table

example:

<?
$array = array(0,5,10,15,20,25,30,35,40,45,50,55,60,65,70,75,80,85,90,95,100); 
$i = array_rand($array); 
echo $array[$i]; 
?>

then select whatever from tablename where fieldname != $array[$i]

[stolzyboy]

!= works just fine in mysql

[pointsplat]

Yea I thought I saw on mysql's website that != worked but anway here is my function if anyone can offer insite 🙂

The cookie last10pics holds the usernames in this fashion
johndoe1,johndoe2,johndoe3...that part of the code works I have verified that it takes the usernames and parses them correctly into the array nouser[] Its the SQL that is not cooperating, or maybe my brain needs a kickstart this morning 🙂

If there is a better or more efficient way of doing this please let me know.

function get_random_user () {
	global $db_tablename;
	$db_id = connect_db();
	$last10pics = $_COOKIE['last10pics'];

	$comma1 = -1;
	$comma2 = 1;
	for($a=1;$a<=10;$a++)
	{
	$comma2 = strpos($last10pics,",",$comma1+1);
	$nouser[$a-1] = substr($last10pics,$comma1+1,$comma2-strlen($last10pics));
	$comma1=$comma2;
	}


$db_tablename WHERE username <> '$nouser[0]' AND username <> '$nouser[1]' AND
	username <> '$nouser[2]' AND username <> '$nouser[3]' AND username <> '$nouser[4]' AND username <> '$nouser[5]' AND
	username <> '$nouser[6]' AND username <> '$nouser[7]' AND username <> '$nouser[8]' AND
	username <> '$nouser[9]' ORDER BY RAND() LIMIT 1");
	$userrow = mysql_fetch_row($query2);
	mysql_close($db_id);
	return $userrow;
}

[laserlight]

My apologies, I was reciting it from experience rather than checking the docs, and in this case my experience was incorrect.

What error do you receive?

[pointsplat]

Warning: Supplied argument is not a valid MySQL result resource in ............./hotornot/includes/functions.inc.php on line 142

Line 142 is the $userrow = mysql_fetch_row($query2);

[laserlight]

Could try:

function get_random_user () {
	global $db_tablename;
	$db_id = connect_db();
	$last10pics = $_COOKIE['last10pics'];

$comma1 = -1;
$comma2 = 1;
for($a = 1; $a <= 10; $a++)
{
	$comma2 = strpos($last10pics,",",$comma1+1);
	$nouser[$a-1] = substr($last10pics,$comma1+1,$comma2-strlen($last10pics));
	$comma1=$comma2;
}

$query = "SELECT username FROM $db_tablename WHERE
	username <> '$nouser[0]' AND
	username <> '$nouser[1]' AND
	username <> '$nouser[2]' AND
	username <> '$nouser[3]' AND
	username <> '$nouser[4]' AND
	username <> '$nouser[5]' AND
	username <> '$nouser[6]' AND
	username <> '$nouser[7]' AND
	username <> '$nouser[8]' AND
	username <> '$nouser[9]'
	ORDER BY RAND() LIMIT 1";
$query = mysql_query($query);
$userrow = mysql_fetch_row($query);
mysql_close($db_id);
return $userrow;
}

[pointsplat]

aye coromba still getting the not a valid resource error on the $userrow line, I even tried changing the query string name to fix that and no such luck.

[laserlight]

Well, let's check $db_tablename then:

function get_random_user () {
	global $db_tablename;
	if (!isset($db_tablename)) {
		exit("Error: db_tablename is NULL!");
	}
	$db_id = connect_db();
	$last10pics = $_COOKIE['last10pics'];

$comma1 = -1;
$comma2 = 1;
for($a = 1; $a <= 10; $a++)
{
	$comma2 = strpos($last10pics,",",$comma1+1);
	$nouser[$a-1] = substr($last10pics,$comma1+1,$comma2-strlen($last10pics));
	$comma1=$comma2;
}

$query = "SELECT * FROM $db_tablename WHERE
	username <> '$nouser[0]' AND
	username <> '$nouser[1]' AND
	username <> '$nouser[2]' AND
	username <> '$nouser[3]' AND
	username <> '$nouser[4]' AND
	username <> '$nouser[5]' AND
	username <> '$nouser[6]' AND
	username <> '$nouser[7]' AND
	username <> '$nouser[8]' AND
	username <> '$nouser[9]'
	ORDER BY RAND() LIMIT 1";
$query = mysql_query($query);
$userrow = mysql_fetch_row($query);
mysql_close($db_id);
return $userrow;
}

[pointsplat]

hehe I already know it exists I use it in another function right above this one, globally of course 🙂 oy! I need a lunch break and a ride on the bike hopefully when I return things will cooperate.

[Weedpacket]

Let's say that $nouser contains only unwanted usernames...

One could try simplifying the query a bit:

$query = "SELECT username from $db_tablename WHERE username NOT IN (";
foreach($nouser as $no)
  $query .= "'$no',";
//Oops, MySQL probably won't like that extra comma
$query = substr($query,0,-1);
$query .= ") ORDER BY RAND() LIMIT 1";

Produces something along the lines of "SELECT username from usertable WHERE username NOT IN ('spammer','moron','luser','idiot') ORDER BY RAND() LIMIT 1"

As a bonus, this will work for any number of unwanted users > 0.

If there are errors; use mysql_error() to find out what MySQL thinks the problem is:

$userrow = mysql_query($query) or die(mysql_error());

Note too that if no results are returned (i.e., all of the users were excluded!), that would probably make the mysql_fetch_row() call fail as well.