3
\$\begingroup\$

After lot of research and couple of questions in https://stackoverflow.com/ i managed to create Favorite System in PDO mysql as database. Am not a developer so there may be security issue or better method with my below code.

config.php

try {
    //create PDO connection 
    $conn = new PDO("mysql:host=" . DBHOST . ";port=3306;dbname=" . DBNAME, DBUSER, DBPASS);
    $conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $conn->exec("SET CHARACTER SET utf8");
} catch (PDOException $e) {
    //show error
    echo '<p class="bg-danger">' . $e->getMessage() . '</p>';
    exit;
}

SQL

DROP TABLE IF EXISTS `allpostdata`;
CREATE TABLE IF NOT EXISTS `allpostdata` (
  `id` int(9) NOT NULL AUTO_INCREMENT,
  `pid` varchar(12) NOT NULL DEFAULT '',
  ....
  ....
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



DROP TABLE IF EXISTS `members`;
CREATE TABLE IF NOT EXISTS `members` (
  `memberID` int(11) NOT NULL AUTO_INCREMENT,
  `Oauth_pro` varchar(10) DEFAULT NULL,
  `username` varchar(255) NOT NULL,
  ....
  ....

  PRIMARY KEY (`memberID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `favorite` (
  `memberID` int(9) NOT NULL,
  `id` int(9) NOT NULL,
  PRIMARY KEY (`memberID`,`id`),
  KEY `fk_favorite_post1_idx` (`memberID`),
  CONSTRAINT `fk_favorite_user` FOREIGN KEY (`memberID`) REFERENCES `members` (`memberID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_favorite_post1` FOREIGN KEY (`id`) REFERENCES `allpostdata` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Post page

<?php
$email = 'user@mail.com';
// Query to get the user_id
$stmt = $conn->prepare('SELECT memberID FROM members WHERE email = :email AND active="Yes" ');
$stmt->execute(array(':email' => $email));
$row = $stmt->fetch();
$mbid = $row['memberID'];

$pid = '4';
// Query to Get the Director ID
$stmt = $conn->prepare('SELECT * FROM allpostdata WHERE id =:id');
$stmt->execute(array(':id' => $pid));
$result = $stmt->fetchAll();
foreach ($result as $row) {

    echo "<p>Director: " . $row['tit'] . "</p> ";
    $fav_image = checkFavorite($mbid, $pid, $conn);
    echo "Favorite? : " . $fav_image . "";
}

function checkFavorite($mbid, $pid, $conn) {
    $stmt = $conn->prepare("SELECT * FROM favorite WHERE memberID=:mid AND id=:id");
    $stmt->execute(array(':mid' => $mbid, ':id' => $pid));
    $count = $stmt->rowCount();
    if ($count == 0) {
        echo "<div class = 'button' method = 'Like'  data-user = " . $mbid . " data-post = " . $pid . "> <i class='mi mi_sml' id=" . $pid . ">favorite_border</i>Add Favorite</div>";
    } else {
        echo "<div class = 'button' method = 'Unlike'  data-user = " . $mbid . " data-post = " . $pid . "> <i class='mi mi_sml text-danger txt_sha' id=" . $pid . ">favorite</i>Remove Favorite </div>";
    }
}
?>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js" integrity="sha384-Q6E9RHvbIyZFJoft+2mJbHaEWldlvI9IOYy5n3zV9zzTtmI3UksdQRVvoxMfooAo" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js" integrity="sha384-wfSDF2E50Y2D1uUdj0O3uMBJnjuUD4Ih7YwaYd1iqfktj0Uod8GCExl3Og8ifwB6" crossorigin="anonymous"></script>
<script>

    $(document).ready(function ($) {
        $(".button").click(function (e) {
            e.preventDefault();
            const user_id = $(this).attr('data-user'); // Get the parameter user_id from the button
            const director_id = $(this).attr('data-post'); // Get the parameter director_id from the button
            const method = $(this).attr('method'); // Get the parameter method from the button
            if (method === "Like") {
                $(this).attr('method', 'Unlike'); // Change the div method attribute to Unlike
                $(this).html('<i class="mi mi_sml text-danger" id="' + director_id + '">favorite</i>Remove Favorite').toggleClass('button mybtn'); // Replace the image with the liked button
            } else {
                $(this).attr('method', 'Like');
                $(this).html('<i class="mi mi_sml" id="' + director_id + '">favorite_border</i>Add Favorite').toggleClass('mybtn button');
            }
            $.ajax({
                url: 'favs.php', // Call favs.php to update the database
                type: 'GET',
                data: {user_id: user_id, director_id: director_id, method: method},
                cache: false,
                success: function (data) {
                }
            });
        });
    });
</script>

Favs.php

<?php
include("$_SERVER[DOCUMENT_ROOT]/include/config.php");

$method = clean_input($_GET['method']);
$user_id = clean_input($_GET['user_id']);
$director_id = clean_input($_GET['director_id']);

if ($method == "Like") {
    $stmt = $conn->prepare('INSERT INTO favorite (memberID, id) VALUES (:mID, :pID)');
    $stmt->bindParam(':mID', $user_id, PDO::PARAM_INT, 12);
    $stmt->bindParam(':pID', $director_id, PDO::PARAM_INT, 12);
    $stmt->execute();
} elseif ($method == "Unlike") {
    $stmt = $conn->prepare('DELETE FROM favorite WHERE memberID=:mID and id=:pID');
    $stmt->bindParam(':mID', $user_id, PDO::PARAM_INT, 12);
    $stmt->bindParam(':pID', $director_id, PDO::PARAM_INT, 12);
    $stmt->execute();
} else {
    //do nothing
}

function clean_input($data) {
    $data = trim($data);
    $data = stripslashes($data);
    $data = htmlspecialchars($data);
    return $data;
}

Correct me if there are any security flaw

\$\endgroup\$
  • \$\begingroup\$ I noticed you don't use the success function when the AJAX request succeeds, nor does your Favs.php give any feedback. It's there, but it's empty. Normally you first call the AJAX function, and only when it returns successfully you would give the appropriate feedback to the user. This means you could also give the user feedback when the AJAX request fails. \$\endgroup\$ – KIKO Software Mar 4 at 9:37
  • \$\begingroup\$ But what about this cache: false, success: function (data) { } or how should i do ? \$\endgroup\$ – sanoj lawrence Mar 4 at 9:42
  • 1
    \$\begingroup\$ checkFavorite definitely doesn't work as expected 3v4l.org/0UebU \$\endgroup\$ – Your Common Sense Mar 9 at 6:26
  • \$\begingroup\$ @YourCommonSense it's strange, i didn't notice until you pointed it. what would be wrong ? any advise \$\endgroup\$ – sanoj lawrence Mar 9 at 8:58
3
+25
\$\begingroup\$

I noticed you don't use the success function when the AJAX request succeeds, nor does your Favs.php give any feedback. It's there, but it's empty. Normally you first call the AJAX function, and only when it returns successfully you would give the appropriate feedback to the user. This means you could also give the user feedback when the AJAX request fails.

Let me try and add that to your code. I'll start with the middle bit of Favs.php:

switch ($method) {
    case "Like" : 
        $query = 'INSERT INTO favorite (memberID, id) VALUES (:mID, :pID)';
        break;
    case "Unlike" :
        $query = 'DELETE FROM favorite WHERE memberID=:mID and id=:pID';
        break;
}
$feedback = 'Fail'; // start with pessimistic feedback
if (isset($query)) {
    $stmt = $conn->prepare($query);
    $stmt->bindParam(':mID', $user_id, PDO::PARAM_INT, 12);
    $stmt->bindParam(':pID', $director_id, PDO::PARAM_INT, 12);
    if ($stmt->execute()) $feedback = $method; // feedback becomes method on success
}
echo json_encode(['id'       => $director_id,
                  'feedback' => $feedback]);

I got rid of duplicated code and I give Like, Unlike or Fail as the feedback to the AJAX call. This code now returns a JSON string containing the director_id and the feedback. This makes sense because it tells you which director was changed and how. You can leave the beginning, and the clean_input() function, in place.

Now in your Javascript you need to respond to what the AJAX call returns.

$(document).ready(function () {
    $('.button').click(function (e) {
        e.preventDefault();
        $.getJSON('favs.php', 
                  {user_id: $(this).attr('data-user'), 
                   director_id: $(this).attr('data-post'), 
                   method: $(this).attr('method')}) 
         .done(function(json) {
                   switch (json.feedback) {
                       case 'Like'   :
                           $(this).attr('method', 'Unlike'); 
                           $(this).html('<i class="mi mi_sml text-danger" id="' + json.id + '">favorite</i>Remove Favorite').toggleClass('button mybtn'); // Replace the image with the liked button
                           break;
                       case 'Unlike' :
                           $(this).html('<i class="mi mi_sml" id="' + json.id + '">favorite_border</i>Add Favorite').toggleClass('mybtn button');
                           $(this).attr('method', 'Like');
                           break;
                       case 'Fail'   : 
                           alert('The Favorite setting could not be changed.');
                           break;
                   }
         })
        .fail(function(jqXHR,textStatus,error) {
           alert("Error Changing Favorite: " + error);
        }); 
    });
});

I changed your basic AJAX request to a JSON-encoded AJAX request with better fault detection. I moved the changes you make to the button inside the .done() method.

To recap:

  • Call the AJAX PHP script with all the parameters needed.
  • The AJAX PHP script return a JSON string containing what it has done.
  • Change the UI according to what was returned.

This way your code is more fault tolerant.

There is still a problem though. Suppose a user has opened two browser windows with exactly the same content. First the favorite button in one is used, and then the exact same button is used in the other window. If I am correct your database will now contain two identical rows in the favorite table. You can prevent this by making the favorite.id column unique. The query will then fail and the feedback in the second window should become: "The Favorite setting could not be changed.".

Also note: Because you supply the user_id as a parameter to the AJAX call I can easily change the favorites of other users by changing this id. It is better to store the user_id in a session and use that in the AJAX PHP script.

I hope this helps you. I haven't tested the code, so I don't exclude the possibility of some errors.

| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ AJAX not changing UI but i can see {id: "36", feedback: "Unlike"} id: "36" feedback: "Unlike" in inspect->XHR->preview, \$\endgroup\$ – sanoj lawrence Mar 18 at 18:27
  • 1
    \$\begingroup\$ As I said: I haven't tested the code. Here we review your code and help you to improve it. The debugging is up to you. \$\endgroup\$ – KIKO Software Mar 19 at 7:20
  • \$\begingroup\$ any help would be appreciated. stackoverflow.com/questions/60759461/… \$\endgroup\$ – sanoj lawrence Mar 19 at 14:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.