pdo request insert problem

[bernouli]

can someone tell me what is problem with this application.
am trying to send a friend request but when i click add as friend link,
nothing is inserted into table friendship_requ ests and sO the request is not sent.
please i need correction

<?php

session_start();
//session_regenerate_id();

$db = new PDO (
	'mysql:host=localhost;dbname=test', // dsn
	'root', // username
	'' // password
);
//Login section start





if (!isset($_SESSION['logged'])) {

	if (isset($_POST['username']) && isset($_POST['password'])) {
		$statement = $db->prepare('
			SELECT id FROM site_members
			WHERE username = :username
			AND password = :password
		');
		$statement->execute(array(
			':username' => $_POST['username'],
			':password' => $_POST['password']
		));
//if ($statement->fetchColumn()) {


		if ($statement->rowCount()) {
			$row = $statement->fetchColumn();
			$_SESSION["logged"] = $row["id"];

//echo $row["id"];


			header("Location: " . $_SERVER["PHP_SELF"]);
			/* why are you redirecting to self? Just drop-through! */
		} 
}else {
			/* you should probably bomb for failed login here ! */
echo("<form method=\"POST\">
    <input type=\"text\" name=\"username\" value=\"Type username here\">
	<input type=\"text\" name=\"password\" value=\"Type username here\">
    <input type=\"submit\" name=\"submit\">  
    </form>");
		}
	}
else {
//end of login section
if (isset($_GET['add'])) {
		// make sure ID trying to be added exists
		$statement = $db->prepare('
			SELECT id FROM site_members
			WHERE id = :id
		');
		$statement->execute(array(
			':id' => $_GET['add']
		));
		/*
			Not sure I understand this, only add a record if the currently
			logged user has already added a user that exists? Just what
			are you trying to do here?!?
		*/
		if ($statement->rowCount()) {
			$leoJon = array(
				':sender' => $_SESSION['logged'],
				':recipient' => $_GET['add']
			);
			/*
				I'm pulling count instead of whole records as you don't seem
				 to be using the data for anything!
			*/
			$statement = $db->prepare('
				SELECT * FROM friendship_requests
				WHERE sender = :sender
				AND recipient = :recipient
			');
			$statement->execute($leoJon);
			if ($statement->fetchColumn()) {
				$statement = $db->prepare('INSERT INTO friendship_requests ( sender, recipient ) VALUES ( :sender, :recipient )');
                                //$statement = $db->prepare('INSERT INTO friendship_requests SET sender = :sender, recipient = :recipient');
				$statement->execute($leoJon);
			}
		}
	} // END

//Section for exceting friendship requests

	if (isset($_GET['accept'])) {
		
		$statement = $db->prepare('
			SELECT * FROM friendship_requests
			WHERE sender = :sender
			AND recipient = :recipient
		');
		$statement->execute(array(
			':sender' => $_GET['accept'],
			':recipient' => $_SESSION['logged']
		));

		if ($statement->fetchColumn()) {


			$selectRec = $db->prepare('
				SELECT * FROM site_members  
				WHERE id = :id                   
			');
                         $selectRec->execute(array(
				'id' => $_GET['accept']
			));



			$_row = $statement->fetchColumn();     
			
                         $friends = unserialize($_row["friends"]);
                        $friends[] = $_SESSION['logged'];

			$updateLogin = $db->prepare('
				UPDATE site_members
				SET friends = :friends
				WHERE id = :id
			');
		
			$updateLogin->execute(array(
				':friends' => serialize($friends),
				':id' => $_GET['accept']
			));

			$selectRec = $db->prepare('
				SELECT * FROM site_members  
				WHERE id = :id
			');
                         $selectRec->execute(array(
				':id' => $_SESSION['logged']
			));
                       
                        $_row = $statement->fetchColumn();
                         $friends = unserialize( $_row["friends"]);
                        $friends[] = $_GET['accept'];

			$updateLogin = $db->prepare('
				UPDATE site_members
				SET friends = :friends
				WHERE id = :id
			');
		
			$updateLogin->execute(array(
				':friends' => serialize($friends),
				':id' => $_SESSION['logged']
			));

		}

		$statement = $db->prepare('
			DELETE FROM friendship_requests
			WHERE sender = :sender
			AND recipient = :recipient
		');
		$statement->execute(array(
			':sender' => $_GET['accept'],
			':recipient' => $_SESSION['logged']
		));
	} // END

//Section for showing friendship requests

	$selectData = $db->prepare('
		SELECT * FROM friendship_requests
		WHERE recipient = :recipient
	');
	
	$selectData->execute(array(
		':recipient' => $_SESSION['logged']
	));
	if ($selectData->rowCount()) {
		$selectLogin = $db->prepare('
			SELECT * FROM site_members
			WHERE id = :id
		');
		while ($row = $selectData->fetch()) {
			$selectLogin->execute(array(
				':id' => $row['sender']
			));
			while ($_row = $selectLogin->fetch()) {
				// assuming you are echoing out something there.


			}
		}
	}//END

// showing result

	
	$selectLogin = $db->prepare('
		SELECT * FROM site_members
		WHERE id != :id
	');
	$selectLogin->execute(array(
		':id' => $_SESSION['logged']
	));
	$userList = '';      //200
	while ($row = $selectLogin->fetch()) {
		$alreadyFriend = false;
		$friends = unserialize($row['friends']);                            
		if (isset($friends[0])) {
			
			foreach ($friends as $recData) {
				if ($recData == $_SESSION["logged"]) $alreadyFriend = true;
				
			}
		}
		
     echo $row["id"];
		
		$selectData = $db->prepare('
			SELECT * FROM friendship_requests
//SELECT count(*) FROM friendship_requests
			WHERE sender = :sender
			AND recipient = :recipient
		');
		$selectData->execute(array(
			':sender' => $_SESSION['logged'],
			':recipient' => $row['id']
		));

if ($selectData->rowCount()) {    

		//if ($selectData->countRows()) {    
			echo " - Friendship requested.";
		} elseif ($alreadyFriend == false) {
			 echo " - <a href=\"" . $_SERVER["PHP_SELF"] . "?add=" . $row['id'] . "\">Add as friend</a>";
    
		} else {
			      echo " - Already friends.";
		}
		echo '<br />';
		





//END
}
?>

[Dormilich]

can someone tell me what is problem with this application.

I’d say, insufficient error handling. currently you do not know whether your conditions trigger or not. (you would need to add debug statements to find that out).

personally I would set PDO’s error handling as follows:

$db->setAttribute(PDO::ATTR_ERRMODE; PDO::ERRMODE_EXCEPTION);

/*
Not sure I understand this, only add a record if the currently
logged user has already added a user that exists? Just what
are you trying to do here?!?
*/

as I see it, the creator wanted to test, whether
a) the recipient exists
b) a friendship relation to that recipient already exists

the line of thought as I see it:
- line #68: check if the recipient is valid
- line #83: check if the intended sender-recipient relation exists
- then do the insert command

I believe the approach is due to an insufficient DB layout/normalisation. if the DB is properly designed, just the insert command would suffice since any of the above tested cases would throw an error (a constraint violation).

to explain that a bit more detailed, there are 2 tables concerned: a user table (site_members) and a friendship table (friendship_req uests).
the minimum requirement for the user table is

CREATE TABLE site_members
(
  id INTEGER AUTO_INCREMENT, -- the user id
  -- more user-related data
  PRIMARY KEY (id)
)

the friendship table is a sole table for member relations (called "friendship "), so you need (at least) 2 fields, each for a member (I am assuming that only members can have friendships)

CREATE TABLE friendship_requests
(
  sender INTEGER, -- to be extended
  recipient INTEGER -- to be extended
-- more request status properties, if necessary
)

then we have some constraints:
- both sender and recipient must be a member, i.e. they must exist in the user table => foreign keys
- the combination of sender and recipient must be unique => compound keys
hence we set the constraints

CREATE TABLE friendship_requests
(
  sender INTEGER,
  recipient INTEGER,
-- more request status properties, if necessary
  PRIMARY KEY (sender, recipient),
  FOREIGN KEY (sender) REFERENCES site_members (id),
  FOREIGN KEY (recipient) REFERENCES site_members (id)
)

the SQL to insert a new friendship reques is still the same: INSERT INTO friendship_requ ests (sender, recipient) VALUES (:sender, :recipient);.

what happens if:
- the sender is invalid: you get a foreign key violation
- the recipient is invalid: you get a foreign key violation
- the friendship request already exists: you get a unique key violation

except for the case that you can befriend yourself (which is a pretty cheap test in PHP), we have made all of the previous tests (the SELECTs) obsolete.

[bernouli]

i think the problem is that when click add as friend button,the sender and recipient id is not inserted via SQL INSERT statement and hence the request is not sent. let value be inserted into table friendship_requ ests and i think things will be okay.

here is the table and you run the code on your own

CREATE TABLE IF NOT EXISTS `friendship_requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sender` int(11) NOT NULL,
  `recipient` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `site_members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `password` varchar(11) NOT NULL,
  `friends` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;



INSERT INTO `site_members` (`id`, `username`, `password`, `friends`) VALUES (1, 'test', '1234', ''),
(2, 'bob', '1234', ''),
(3, 'chuck', '1234', '');

[bernouli]

i have added this below but it displays no error
$db->setAttribute(P DO::ATTR_ERRMOD E; PDO::ERRMODE_EX CEPTION);

[Dormilich]

here is the table and you run the code on your own

as I suspected, the tables lack proper constraints. besides that the tables don’t help if there are issues gathering the data beforehand.

[bernouli]

how do i make it worked

[Dormilich]

first check, which data are passed and which conditions trigger. if your logic doesn’t get to your insert, you do not insert even if the SQL is correct.