Image rate system

Question

I'm developing a site, which has an image upload section, and members and like the image, but a member can like it only once.

I have a table, named uploads which has columns id, ...{more columns}..., like.

I identify members by a token(a 16 digit number)

Now, when a user likes an image, the code executed is:

public function imgLike($post) {

         $pgid = filter_var(trim($post['id']), FILTER_SANITIZE_STRING, FILTER_FLAG_STRIP_HIGH);

         if((isset($_SESSION['public'])) && (!empty($_SESSION['public']))) {

                $this->dbconnect();
           $name = filter_var(trim($_SESSION['public']), FILTER_SANITIZE_STRING, FILTER_FLAG_STRIP_HIGH);

           try {

             $query = $this->db->prepare("SELECT `like` FROM `uploads` WHERE id=?");

             $query->execute(array($pgid));

             while($row = $query->fetch(PDO::FETCH_ASSOC)){                 
                      $likes = json_decode($row['like']);
                    }

                    array_push($likes, $name);
                    $final = json_encode($likes);

           } catch (Exception $e) { 

                  die("There were some issues while processing your request, please refresh. Error code #55");

                }

                $queryUp = $this->db->prepare("UPDATE `uploads` SET `like`=? WHERE `id`=?");

           try {

             $queryUp->execute(array($final,$pgid));

           } catch (Exception $e) { 

                  die("There were some issues while processing your request, please refresh. Error code #55");

                }

                return $final;

              } else {

                return "not logged in";

              }

}

For few members it's ok, but if there are 1000 members then the length of the like column in the row goes up to 16,000.

Now if I need to check whether the member has liked the image or not, then I have to read a string as long as 16,000 characters.

So my main question is, how can this be done in a better way?

Answer 1

Yes this can be done better. Storing data in a serialized format in a database (be it JSON or XML) is a bad idea more often than not. The main issue is that you will have a hard time querying for specific items in the serialized blob (e.g. trying to find which member has liked which images).

The basic thing you can do is to normalize your data by introducing a UploadLike table:

| Name | UploadId |

where each row states which name likes which upload.

If you already have a members table then it might feasible to actually link the table to the member rather than a name:

| MemberId | UploadId |

So whenever a member likes an image you insert a row for that member and the associated upload id into that table.

Also when you get the likes for an upload you do this:

        while($row = $query->fetch(PDO::FETCH_ASSOC)){                 
                  $likes = json_decode($row['like']);
                }

Which seems weird. There should only be one upload with the given id anyway and I'd consider it an error to have multiple rows with the same upload id. So why do you loop here?