0

Users should have the opportunity to follow other users whenever they visit a user's profile page. However, I am unsure as to how I should link it all together. I have a users table that includes two columns named following and followers:

table users
CREATE TABLE IF NOT EXISTS `users` (
  "I have more columns in this table"
  `followers` int(11) NOT NULL,
  `following` int(11) NOT NULL
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ;

My first question is if I should add another table to ease the process, something similar like this:

CREATE TABLE IF NOT EXISTS `user_follow` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `follower` int(11) NOT NULL,
  `following` int(11) NOT NULL,
  `subscribed` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `follow_unique` (`follower`,`following`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ;

and left join the columns from users table, or if this is not needed at all.

My next question is if the action form is valid considering that I will have to take the input values from the currently logged in user $_SESSION["userid"] as well as the user on the profile page $userurl = $_GET['user']; which maybe would mix post and get methods? And either way, I have a placeholder now for the form buttons and input but unsure how to make use of the subscriber text.

profile.php

<div class="userprofilecontainer">
<?php  

$userurl = $_GET['user']; 
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$stmt = $conn->prepare('SELECT * FROM users WHERE users_username = ?;');
$stmt->bind_param('s', $userurl);
$stmt->execute();
$result = $stmt->get_result();

while($row = $result->fetch_array()){
$username = $row['users_username'];
$registered = $row['create_datetime'];
$description = $row['users_about'];
$followers = $row['followers'];
$id = $row['users_id']; 
     
if(isset($_SESSION["userid"]) && $_SESSION["userid"] != $id) {
?>
<form action="<?php echo htmlspecialchars("includes/followuser.inc.php");?>" id="followform" method="post">
<input name="action" type="hidden" value="<?php echo $subscribe_status; ?>"/>
<button name="subject" id ="followbutton" type="submit" value="<?php echo $subscribe_text.' '.$username; ?>">       
<?php echo $subscribe_text.' '.$username; ?></button>
</form>
<?php
}

My third question is how I should go about making two queries for following and unfollowing, and as you already know it depends on the tables part as well as knowing when someone is following or not.

followuser.inc.php

<?php
require_once 'dbh.inc.php';
require_once 'functions.inc.php';
if ($_POST['action'] == "Follow"){
$sub = false;
//Perform Unfollow Query to Database
$subject = $_POST['following']; //Is this correct? Considering that I have following and followers column

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$stmt = $conn->prepare('UPDATE users SET following =following+? WHERE users_id = ?');
$following--;
$stmt->bind_param('s', $subject;
$stmt->execute();
$result = $stmt->get_result();
if ($result->num_rows > 0) {
$sub = true;
if($sub){
    $subscribe_status = "Follow";
    $subscribe_text = "Unfollow";
}else{
    $subscribe_status = "Unfollow";
    $subscribe_text = "Follow";
}
}
$stmt->close();

} else if ($_POST['action'] == "Unfollow"){
//Perform Follow Query to Database
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$stmt = $conn->prepare('UPDATE users SET following =following+? WHERE users_id = ?');
$following++;
$stmt->bind_param('s', $subject);
$stmt->execute();

$stmt->close();
}

Any help would be much appreciated as I'm sitting here confused trying to list out what approach I can go with.

Votum
  • 77
  • 6
  • for every N:N relationship you need a table for the "links". often called Many-to-Many Relationships. – hakre Dec 11 '22 at 23:20

0 Answers0