Mysqli-preapre select from table1 join table 2

singharsh74 · April 8, 2014, 8:54am

hi sitepoint users, so the first thing i let you know , that im not completly expert in the mysqli statements, im just learning. My question is (or problem) that im trying to make join statement in mysqli->prepare with no succes. let me explain:

I have table : members which contains all the information of members of site (id,username,user_level,profilepic,email,cover) , and table : follow_user which contains only three columns (fid,userid,follow_id). this function is user following function , when user clicks on follow button (on anybody’s profile), the insert statement will add ; current loggedin user’s id (because only loggedin users can click on follow button) into follow_user table’s column : follow_id and the user that bieng followed in userid column. now when someone visits profile of that user(e.g jack has followed mark and when someone visit mark’s profile he will see jack’s name in followers list, so my following code is not working at all int his case , please help please please please :

&lt;?php 
		if(isset($_GET['id'])) {
		$currentid =$_GET['id'];
		$fol =$mysqli-&gt;prepare("select `id`,`username`,`profilepic` from `members` outer join `follow_user` on `members`.`id` = `follow_user`.`follow_id` where `follow_user`.`userid` = ?");
		$fol-&gt;bind_param('s', $currentid);
		$fol-&gt;execute();
		$fol-&gt;store_result();
		if($fol-&gt;num_rows == 1) {
		$fol-&gt;bind_result($follow_id,$follow_username,$followpic); // get variables from result.
        $fol-&gt;fetch();
		
 ?&gt;
			&lt;a href="profile?id=&lt;?php echo $follow_id;?&gt;"&gt;&lt;?php echo $followusername; ?&gt;&lt;/a&gt;
 &lt;?php
 }
}
 ?&gt;

singharsh74 · April 8, 2014, 11:23am

nobody is able to answer???

Drummin · April 8, 2014, 9:37pm

Ah for the fun of it, I made a little interpretation of your project. It does NOT automatically add to follow if user views a profile, but instead has follow/unfollow links. It does show both users following the user and who the user is following. I’m sure you can find some of it useful.

&lt;?php
//include DB connect and session start
//include("config.php");
//session_start();

// Make sure user is logged in. Adjust as needed
if(!isset($_SESSION['user_id'])){
	header("location: login.php");
	exit;
}
 
//Set user ID to variable.   Adjust as needed
$user_id = $_SESSION['user_id'];

// Add following
if(isset($_GET['follow'])){
	$followid = $_GET['follow']; 
	$sql = "INSERT INTO follow_user (`userid`,`follow_id`) VALUES (?,?)";
	$query = $mysqli->prepare($sql);
	$query->bind_param('ii', $user_id,$followid);
	$query->execute();
}

// Remove following
if(isset($_GET['unfollow'])){
	$followid = $_GET['unfollow']; 
	$sql = "DELETE FROM follow_user WHERE `userid` = ? AND `follow_id` = ?";
	$query = $mysqli->prepare($sql);
	$query->bind_param('ii', $user_id,$followid);
	$query->execute();
}
// Build profiles array
$profiles = array(); 
if(isset($_GET['view'])){
	$currentid = $_GET['view'];
	$sql = "SELECT 
	`m`.`id`,
	`m`.`username`,
	`m`.`profilepic`,
	`mf`.`id` AS fid,
	`mf`.`username` as fusername,	
	`mf`.`profilepic` as fprofilepic,	
	`mhf`.`id` AS hfid,
	`mhf`.`username` as hfusername,	
	`mhf`.`profilepic` as hfprofilepic		 
	FROM `members` AS m 
		LEFT JOIN `follow_user` AS f 
			ON `f`.`follow_id` = `m`.`id`				 
		LEFT JOIN `members` AS mf 
			ON `mf`.`id` = `f`.`userid`
		LEFT JOIN `follow_user` AS hf 
			ON `hf`.`userid` = `m`.`id`		  				 
		LEFT JOIN `members` AS mhf 
			ON `mhf`.`id` = `hf`.`follow_id` 
	WHERE `m`.`id` = ?";
	$fol =$mysqli-&gt;prepare($sql);
	$fol-&gt;bind_param('s', $currentid);
	$fol-&gt;execute();
	$fol-&gt;store_result();
	if($fol-&gt;num_rows &gt;= 1) {
		$fol-&gt;bind_result($id,$username,$profilepic,$follow_id,$follow_username,$followpic,$hfollow_id,$hfollow_username,$hfollowpic); // get variables from result.
		while ($fol-&gt;fetch()) {
			$profiles[$id]['username'] = $username; 
			$profiles[$id]['profilepic'] = $profilepic;   
			$profiles[$id]['followers'][$follow_id]['username'] = $follow_username;
			$profiles[$id]['followers'][$follow_id]['profilepic'] = $followpic;
			$profiles[$id]['hasfollowed'][$hfollow_id]['username'] = $hfollow_username;
			$profiles[$id]['hasfollowed'][$hfollow_id]['profilepic'] = $hfollowpic;
		}
	}
}else{
	$sql = "SELECT 
	`m`.`id`,
	`m`.`username`,
	`m`.`profilepic`,
	`mf`.`id` AS fid,
	`mf`.`username` as fusername,	
	`mf`.`profilepic` as fprofilepic,	
	`mhf`.`id` AS hfid,
	`mhf`.`username` as hfusername,	
	`mhf`.`profilepic` as hfprofilepic			 
	FROM `members` AS m 
		LEFT JOIN `follow_user` AS f 
			ON `f`.`follow_id` = `m`.`id`				 
		LEFT JOIN `members` AS mf 
			ON `mf`.`id` = `f`.`userid`
		LEFT JOIN `follow_user` AS hf 
			ON `hf`.`userid` = `m`.`id`		  				 
		LEFT JOIN `members` AS mhf 
			ON `mhf`.`id` = `hf`.`follow_id`";
	$fol =$mysqli-&gt;prepare($sql);
	$fol-&gt;execute();
	$fol-&gt;store_result();
	if($fol-&gt;num_rows &gt;= 1) {
		$fol-&gt;bind_result($id,$username,$profilepic,$follow_id,$follow_username,$followpic,$hfollow_id,$hfollow_username,$hfollowpic); // get variables from result.
		while ($fol-&gt;fetch()) {
			$profiles[$id]['username'] = $username; 
			$profiles[$id]['profilepic'] = $profilepic;   
			$profiles[$id]['followers'][$follow_id]['username'] = $follow_username;
			$profiles[$id]['followers'][$follow_id]['profilepic'] = $followpic;
			$profiles[$id]['hasfollowed'][$hfollow_id]['username'] = $hfollow_username;
			$profiles[$id]['hasfollowed'][$hfollow_id]['profilepic'] = $hfollowpic;
		}
	}
}

//echo "&lt;pre&gt;";
//print_r($profiles);	
//echo "&lt;/pre&gt;";

$display = "";
$imgdir = "images";
					  
$display .= '&lt;div class="wrapper"&gt;'."\\r";
$display .= '&lt;div class="title"&gt;Profile&lt;/div&gt;'."\\r";
	foreach($profiles as $id =&gt; $arry):
		if(!empty($id)):
			
				//Show Username		
				$display .= '&lt;div class="name"&gt;'."\\r";
					$display .= "{$arry['username']}\\r";
				$display .= '&lt;/div&gt;'."\\r";	
				//Show Image
				$display .= '&lt;div class="imageholder"&gt;'."\\r";
					$image = (!empty($arry['profilepic']) ? "{$arry['profilepic']}" : "noimage.gif");
					$display .= '&lt;img src="' . $imgdir . '/' . $image . '" class="image" /&gt;'."\\r";
				$display .= '&lt;/div&gt;'."\\r";
				//Links	  
				
				$FollowLink = (array_key_exists($user_id,$arry['followers']) ? '&lt;a href="?unfollow=' . $id . '"&gt;UnFollow&lt;/a&gt;' : '&lt;a href="?follow=' . $id . '"&gt;Follow&lt;/a&gt;');
				$display .= '&lt;div class="links"&gt;'."\\r";
				
					if(!isset($_GET['view'])):
						$display .= '&lt;a href="?view=' . $id . '"&gt;View&lt;/a&gt;';
					endif;				
					if($id != $user_id):
						$display .= $FollowLink;
					endif;				
					if(isset($_GET['view'])):
						$display .= '&lt;br /&gt;&lt;a href="?"&gt;Show All&lt;/a&gt;';				
					endif;
					 
				$display .= '&lt;/div&gt;'."\\r";
						
				if(isset($_GET['view'])):	
					
					// Followers Display	
					$display .= '&lt;div class="followers"&gt;'."\\r";	
					$display .= '&lt;div class="title"&gt;Followers&lt;/div&gt;'."\\r";								 
					foreach($arry['followers'] as $fid =&gt; $farry):
						if(!empty($fid)):						
								//Show Username		
								$display .= '&lt;div class="name"&gt;'."\\r";
									$display .= "{$farry['username']}\\r";
								$display .= '&lt;/div&gt;'."\\r";	
								//Show Image
								$display .= '&lt;div class="imageholder"&gt;'."\\r";
									$image = (!empty($farry['profilepic']) ? "{$farry['profilepic']}" : "noimage.gif");
									$display .= '&lt;img src="' . $imgdir . '/' . $image . '" class="image" /&gt;'."\\r";
								$display .= '&lt;/div&gt;'."\\r";
								//Links	
								$FollowLink = (array_key_exists($user_id,$arry['followers']) ? '&lt;a href="?unfollow=' . $fid . '"&gt;UnFollow&lt;/a&gt;' : '&lt;a href="?follow=' . $fid . '"&gt;Follow&lt;/a&gt;');
								
								$display .= '&lt;div class="links"&gt;'."\\r";
									
									if($fid != $user_id):
										$display .= '&lt;a href="?view=' . $fid . '"&gt;View&lt;/a&gt;'; 
									endif;
									if($fid != $user_id):
										$display .= $FollowLink;
									endif;
									
								$display .= '&lt;/div&gt;'."\\r";			
								
						endif;
					endforeach;	
							$display .= '&lt;/div&gt;'."\\r";
							
					// Has Followed Display
						
					$display .= '&lt;div class="hasfollowed"&gt;'."\\r";	
					$display .= '&lt;div class="title"&gt;Has Followed&lt;/div&gt;'."\\r";								 
					foreach($arry['hasfollowed'] as $hfid =&gt; $hfarry):
						if(!empty($hfid)):						
								//Show Username		
								$display .= '&lt;div class="name"&gt;'."\\r";
									$display .= "{$hfarry['username']}\\r";
								$display .= '&lt;/div&gt;'."\\r";	
								//Show Image
								$display .= '&lt;div class="imageholder"&gt;'."\\r";
									$image = (!empty($hfarry['profilepic']) ? "{$hfarry['profilepic']}" : "noimage.gif");
									$display .= '&lt;img src="' . $imgdir . '/' . $image . '" class="image" /&gt;'."\\r";
								$display .= '&lt;/div&gt;'."\\r";
								//Links	
								$FollowLink = (array_key_exists($user_id,$arry['followers']) ? '&lt;a href="?unfollow=' . $hfid . '"&gt;UnFollow&lt;/a&gt;' : '&lt;a href="?follow=' . $hfid . '"&gt;Follow&lt;/a&gt;');
								
								$display .= '&lt;div class="links"&gt;'."\\r";
									
									if($hfid != $user_id):
										$display .= '&lt;a href="?view=' . $hfid . '"&gt;View&lt;/a&gt;'; 
									endif;
									if($hfid != $user_id):
										$display .= $FollowLink;
									endif;
									
								$display .= '&lt;/div&gt;'."\\r";			
								
						endif;
					endforeach;	
							$display .= '&lt;/div&gt;'."\\r";
							
				endif;
						
		endif;
	endforeach;
$display .= '&lt;/div&gt;'."\\r";	
?&gt; 
&lt;!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"&gt;
    &lt;html xmlns="http://www.w3.org/1999/xhtml"&gt;
&lt;head&gt; 
&lt;title&gt;Profile&lt;/title&gt;
&lt;style type="text/css"&gt;
.wrapper {
width: 450px;
overflow: hidden;
}
.title {
width: 120px;
font-size: 18px;
font-weight: bold;
text-align:center;
clear: left;
float: left;
white-space: nowrap;
}
.name {
width: 120px;
font-size: 14px;
text-align:center;
font-weight: bold;
clear: left;
float: left;
white-space: nowrap;
}

.imageholder {
width: 120px; 
height: 120px;
text-align:center;
clear: left;
float: left;
}
.image {
width: 120px; 
height: 120px;
}

.links {
width: 120px;
text-align:center;
clear: left;
float: left;
}

.links a{
font-size: 14px;
text-align:center;
margin: 0 auto;
padding: 0 5px;
text-decoration: none;
}
.followers {
margin-top: 30px;
margin-left: 150px;
width: 120px;
text-align:center;
overflow: hidden;
clear: left;
float: left;
}
.hasfollowed {
margin-top: 30px;
width: 120px;
text-align:center;
overflow: hidden;
clear: right;
float: right;
}
&lt;/style&gt; 
&lt;/head&gt;
&lt;body&gt;
&lt;?php if(isset($display)){ echo $display; }?&gt;
&lt;/body&gt;
&lt;/html&gt;

singharsh74 · April 8, 2014, 9:41pm

Thanks for the reply buddy , this code works

singharsh74 · April 8, 2014, 9:48pm

I have also figured out how to make that in some tricky way , but this is usefull and working

Topic		Replies	Views
Struggling With 'Social Network' Script PHP	24	1933	October 8, 2014
Mysqli problem PHP	2	1037	October 8, 2014
Last prepare statement not working PHP	2	533	October 8, 2014
MySQLi and prepared statements PHP	5	5619	March 3, 2013
Help with JOIN in place of query in while loop Databases	3	865	March 25, 2015

Mysqli-preapre select from table1 join table 2

Related topics