Don't know whats wrong with my add friend query... Add all pending friends...

[Eggzorcist]

Hi there,

 

I have been making a friend relationship script for my website. The code works very well when there is only one friend request, and you select that friend to become friends. But the problem lays when there is more than 1 friend request. When there is more than one friend request, the friend selected becomes your friend but also the other unselected friend becomes a friend all at once.

 

Not sure why this is happening. My code seems logical and well constructed.

 

// checks if get works where the user ID from the friend we want to add is stored.
if(isset($_GET['au'])){
$au = $_GET['au'];
//check if the datatype is correct.
if(is_numeric($au)){
//loops through all pending friend requests
	foreach ($penfListData as $row){

      // looks through each row if the row id corresponds to the $_Get value to make sure they really have pending friend requests.
	       if($au == $row['id']){
			$addFQuery = $dbh->prepare("UPDATE friendlist SET status = ? WHERE (friend1 = ? or friend2 = ?) OR (friend1 = ? or friend2 = ?)");
			$addFQuery->execute(array(1, $userID, $au, $au, $userID));
			$addedFID = $row['id'];

		}

	}
}

}

 

Added a few comments to make it easier to dissect.

 

any help would be greatly appreciated! Thanks!

[darkfreaks]

your bind_param logic is abit off you need to bind the parameters after your prepare query then execute them.

 

$dbh->bind_param(1,$variable,PDO::PARAM_STR); // binding a string with ? also you can use PDO::PARAM_INT for numbers 
$dbh-> execute();

 

 

[Eggzorcist]

Oh, must I use bind param always? This is just the way I learned it via a video.

 

But I have a minor problem with this method. I get an error.

$addFQuery = $dbh->prepare("UPDATE friendlist SET status = ? WHERE (friend1 = ? or friend2 = ?) OR (friend1 = ? or friend2 = ?)");
			$addFQuery->bindParam(1, 1, PDO::PARAM_INT);
			$addFQuery->bindParam(2, $userID, PDO::PARAM_INT);
			$addFQuery->bindParam(3, $au, PDO::PARAM_INT);
			$addFQuery->bindParam(4, $au, PDO::PARAM_INT);
			$addFQuery->bindParam(5, $userID, PDO::PARAM_INT);
			$addFQuery->execute();

 

error message: cannot pass parameter 2

[Eggzorcist]

I have also tried the :type bind param. Still same error...

[Eggzorcist]

After some research have tried the following without error. But again, I get the same error I had at first:

 

	$addFQuery = $dbh->prepare("UPDATE friendlist SET status = ? WHERE (friend1 = ? or friend2 = ?) OR (friend1 = ? or friend2 = ?)");
			$addFQuery->bindValue(1, 1);
			$addFQuery->bindValue(2, $userID);
			$addFQuery->bindValue(3, $au);
			$addFQuery->bindValue(4, $au);
			$addFQuery->bindValue(5, $userID);
			$addFQuery->execute();

[darkfreaks]

where  is $penlistdata defined

 

something tells me it has to be looping over and over againupdating every single ID just not one.

 

 

however if it is a SELECT statement i would suggest doing something like this...

 

 

 

 

SELECT  values FROM DISTINCT table

 

to remove duplicate entries.

[Eggzorcist]

Here is the script that makes the $penfListData array.

 

$penfQuery = $dbh->prepare("SELECT users.id, users.username, users.firstname, users.lastname  FROM friendlist, users WHERE (friendlist.friend1 = ? && friendlist.status = ?) && friendlist.friend2 = users.id && (friendlist.requested = ?)");
$penfQuery->bindValue(1, $userID);
$penfQuery->bindValue(2, 0);
$penfQuery->bindValue(3, $userID);
$penfQuery->execute();
$penfListData = $penfQuery->fetchAll(PDO::FETCH_ASSOC);

 

 

For whomever suggested the DISTINCT parameter, I have also tried that as:

 

$penfQuery = $dbh->prepare("SELECT DISTINCT users.id, users.username, users.firstname, users.lastname  FROM friendlist, users WHERE (friendlist.friend1 = ? && friendlist.status = ?) && friendlist.friend2 = users.id && (friendlist.requested = ?)");

[darkfreaks]

your using bindvalue versus bindparam change it and tell me if it works then.

[Eggzorcist]

I have tried that. I had gotten the error: Fatal error: Cannot pass parameter 2 by reference in...line**

[Eggzorcist]

I've found the problem. It was actually with a faulty UPDATE statement. I was using or instead of AND. That's why too many rows were getting played with. "UPDATE friendlist SET status = ? WHERE (friend1 = ? && friend2 = ?) OR (friend1 = ? && friend2 = ?) " worked like a charm.