check a string for a number

[jasondavis]

Is there a more efficient way to do this? It checks to see if a user has set an option to auto-accept friend invites

The privacy filed in the DB can contain:
NULL
2
2,8
2,8,9
2,7,8,9

anywhere from no numbers, up to 4 number seperated by a comma , in any order it just checks to see for this particular function if the number 8 exist

function checkautoapprovfriend($userid) {
	// according to privacy setting
	$autofriend_arr_id1 = "8" . ",";
	$autofriend_arr_id2 = "," . "8" . ",";
	$autofriend_arr_id3 = "," . "8";
	$autofriend_arr_id4 = "8";
	$privacy = "select user_id,privacy from friend_acc_setting where (privacy like '$autofriend_arr_id1%' or privacy like '%$autofriend_arr_id2%' or privacy like '%$autofriend_arr_id3' or privacy='$autofriend_arr_id4') and  user_id='$userid'";
	$result_privacy = executequery($privacy);
	if (mysql_num_rows($result_privacy) > 0)
		return true;
	else
		return false;
}

when I echo the query here is what it looks like for friend ID 1

select user_id,privacy from friend_acc_setting where (privacy like '8,%' or privacy like '%,8,%' or privacy like '%,8' or privacy='8') and user_id='1'

[jasondavis]

here is new code I am trying but it returns false even when a number 8 is valid

function checkautoapprovfriendtest2($userid) {
	$privacy = "select privacy from friend_acc_setting where user_id='$userid'";
	$result_privacy = executequery($privacy);
	$temp_array = explode(',', $result_privacy);
	if (in_array('8', $temp_array, true)) {
		return true;
	}else{
		return false;
	}
} 

[troybtj]

Is it being interpreted as an integer 8 or a string 8?

if (in_array(8, $temp_array, true)) {
        return true;
    }else{
        return false;
    } 

[NogDog]

if(preg_match('/\b8\b/', $userid)) {
   // There is an "8" in the $userid string
}

[jasondavis]

here is what I tested

function checkautoapprovfriendtest2($userid) {
	$privacy = "select privacy from friend_acc_setting where user_id='$userid'";
	$result_privacy = executequery($privacy);
	//$temp_array = explode(',', $result_privacy);
	//if(preg_match('/\b8\b/', $result_privacy)) {
        //return true;
		echo $result_privacy;
   // }else{
       // return false;
//	}
}

and it prints out

Resource id #11

[jasondavis]

Ok this works, it is about same speed but smaller code 🙂 thanks, any room for improvement in it for performance, every milisecond ads up on my site

function checkautoapprovfriendtest2($userid) {
	$privacy = "select privacy from friend_acc_setting where user_id='$userid'";
	$result_privacy = getSingleResult($privacy);
	if(preg_match('/\b8\b/', $result_privacy)) {
       return true;
	}else{
       return false;
	}
}

[bradgrafelman]

Why not pass this additional search criteria on to MySQL?

$query = "SELECT 1 FROM friend_acc_setting WHERE user_id=$userid AND FIND_IN_SET('8', privacy) >= 1 LIMIT 1";
$exec = mysql_query($query);

return (mysql_num_rows($exec) == 1);

EDIT: Note that if 'privacy' is going to be a list of values separated by commas, you should make sure that this column is a SET column for optimization purposes.

[jasondavis]

bradgrafelman wrote:

Why not pass this additional search criteria on to MySQL?

$query = "SELECT 1 FROM friend_acc_setting WHERE user_id=$userid AND FIND_IN_SET('8', privacy) >= 1 LIMIT 1";
$exec = mysql_query($query);

return (mysql_num_rows($exec) == 1);

EDIT: Note that if 'privacy' is going to be a list of values separated by commas, you should make sure that this column is a SET column for optimization purposes.

Im not following what you are saying "if 'privacy' is going to be a list of values separated by commas, you should make sure that this column is a SET column for optimization purposes."

What do you mean by this?

thanks

[troybtj]

MySQL has a "SET" data type that holds a comma delimited list of up to 64 elements. So instead of using a VARCHAR type for the field that is going to contain the options, make the datatype SET.

The use of the "SET" Datatype is for the FIND_IN_SET function.

I just looked it up again, too much stuff to remember. :eek:

[bradgrafelman]

I guess it really depends on what values "privacy" stores... if the values are based on other things and aren't part of a defined set, then a SET type wouldn't be appropriate.

[jasondavis]

thanks for clarifying that I have changed the table to SET now
can you help me with this function it confuses me because there is another query in it

// process for online status
function update_online_status($location) {
	// according to privacy setting
	$online_hide1 = "3" . ",";
	$online_hide2 = "," . "3" . ",";
	$online_hide3 = "," . "3";
	$online_hide4 = "3";
	$privacy = "select COUNT(*) as cnt from friend_acc_setting where user_id='$temp[auto_id]' AND (privacy like '$online_hide1%' or privacy like '%$online_hide2%' or privacy like '%$online_hide3' or privacy='$online_hide4')";
	$result_privacy = executequery($privacy);
	$row = mysql_fetch_assoc($result_privacy);

if ($row['cnt'] == 0) {
	$temp = $_SESSION['info'];
	$LOCALIP = $_SERVER['REMOTE_ADDR'];
	$lastactive = time();

	$sql_online = "select userid from friend_online where userid='$temp[auto_id]'";
	$result_online = executeQuery($sql_online);

	if ($line_online = mysql_fetch_assoc($result_online)) {
		$sql_update_online = "update friend_online set lastactive=NOW(), loc='$location' where userid='$temp[auto_id]' and ipaddress='$LOCALIP'";
		executeQuery($sql_update_online);
	} else {
		if ($temp[auto_id] != '') {
			$unid = uniqid(rand());
			$sql_insert_online = "insert into friend_online (userid,onlineid,ipaddress,lastactive,loc) values ('$temp[auto_id]','$unid','$LOCALIP',NOW(),'$location')";
			executeQuery($sql_insert_online);
			$sql_online2="UPDATE friend_reg_user SET online='1' where auto_id='$temp[auto_id]'";
			executeQuery($sql_online2);
		}
	}
}
}

[jasondavis]

I think I got it, since this loads on every page load of my site, maybe I should put the hide online status setting into a sessions instead of checking on every single page load

function update_online_status($location) {
	//check privacy to see if hiding online status
	$privacy = "SELECT 1 FROM friend_acc_setting WHERE user_id=$userid AND FIND_IN_SET('3', privacy) >= 1 LIMIT 1";
	$result_privacy = executequery($privacy);
	$row = mysql_fetch_assoc($result_privacy);
	//not hiding so continue
	if ($row['cnt'] == 0) {
		$temp = $_SESSION['info'];
		$LOCALIP = $_SERVER['REMOTE_ADDR'];
		$lastactive = time();
		//see if we are in online table already
		$sql_online = "select userid from friend_online where userid='$temp[auto_id]'";
		$result_online = executeQuery($sql_online);
		if ($line_online = mysql_fetch_assoc($result_online)) {
			$sql_update_online = "update friend_online set lastactive=NOW(), loc='$location' where userid='$temp[auto_id]' and ipaddress='$LOCALIP'";
			executeQuery($sql_update_online);
		//we are not so lets add instead of update
		} else {
			if ($temp[auto_id] != '') {
				$unid = uniqid(rand());
				$sql_insert_online = "insert into friend_online (userid,onlineid,ipaddress,lastactive,loc) values ('$temp[auto_id]','$unid','$LOCALIP',NOW(),'$location')";
				executeQuery($sql_insert_online);
				$sql_online2="UPDATE friend_reg_user SET online='1' where auto_id='$temp[auto_id]'";
				executeQuery($sql_online2);
			}
		}
	}
}