0

I have a website (link) where user stories are shown on screen taken from a MySql database. My issue is that apostrophes are not always shown in the end presented data. I use htmlentities($row['MessageText'],ENT_COMPAT|ENT_IGNORE, "UTF-8") and then echo json_encode($data).

The below example comes out without the apostrophes (So it's is its and I'm is Im):

Sounds crazy, but it’s everything; home, work, what people think of me. If I sit still its worse, that’s why I’m always busy

var_dump:

array(5) { ["ID"]=> int(65) ["UserIDLikeChk"]=> string(1) "0" ["MessageText"]=> string(1263) "Sounds crazy, but it�s everything; home, work, what people think of me. If I sit still its worse, that�s why I�m always busy, always moving. Its things in my head that are out of my control. I can but quite irrational, like the worst thing is going to happen and it gets worse when I�m tired. When I�m exhausted I get irrational. When I was young I got panic attacks. I can control them now, but when I came back from Orlando recently, I was very tired and I could feel I was spiralling down a bit, like a panic attack was coming. When that happens all I can see is what is in my head and no one can talk sense to me. I can have a period of a couple of weeks, where I can feel the anxiety underlying, a knot in my stomach and I know if I�m not careful I could have a panic attack. I�m not a very confident person, so that makes the worry worse I think. I try to rationalise things and that makes things better. I use the Calm app, I speak to my mum, I remove myself from people and that calms me down. I also go to the gym to get the kick from the endorphins. I also avoid alcohol now because that makes things much worse. I think I was drinking after the Orlando trip, when I was really tired, I had a heavy night when I went out, and that made it so much worse." ["CntLikes"]=> int(0) ["Type"]=> int(0) }

Code:

$userid = session_id();
$stmt = $con->prepare(

            'SELECT
              a.ID, ? as UserID, IFNULL(b.UserID,0) as UserIDLikeChk, a.MessageText, a.CntLikes, IFNULL(b.Type,0) as Type
            FROM
              (
                 SELECT m.ID, m.MessageText,count(l.ID) as CntLikes
                 FROM MessageMain m 
                 LEFT OUTER JOIN Likes l ON m.ID = l.PostID
                 WHERE Valid = 1
                 GROUP BY m.ID, m.MessageText ORDER BY RAND() LIMIT 10
              )a

            LEFT OUTER JOIN

             (
               SELECT PostID, UserID, COUNT(*) AS Type 
               FROM Likes 
               WHERE UserID = ?
               GROUP BY PostID, UserID
              )b

            ON a.ID = b.PostID'
      );

      $stmt->bind_param('ss', $userid, $userid);
      $stmt->execute();
      $result = $stmt->get_result();

      $data = array();

     if(mysqli_num_rows($result) > 0) {

        While($row = $result->fetch_assoc()) {   

        $data[] = array ( 
            'ID' => $row['ID'],
            'UserID' => $row['UserID'],
            'UserIDLikeChk' => $row['UserIDLikeChk'], 
            'MessageText' => nl2br(htmlentities($row['MessageText'],ENT_COMPAT|ENT_IGNORE, "UTF-8") ), 
            'CntLikes' => $row['CntLikes'],
            'Type' => $row['Type'] 
        );

      }
    }
        echo json_encode($data);
CGarden
  • 169
  • 17
  • **`ENT_COMPAT` Will convert double-quotes and leave single-quotes alone.** [sic](https://www.php.net/manual/en/function.htmlspecialchars.php) – Will B. Apr 05 '21 at 20:50
  • Please [edit] your question to include the output/result you get from your `json_encode()` call. Also, neither `nl2br()` or `htmlentities()` is needed in JSON. – Progman Apr 05 '21 at 20:50
  • @WillB thanks. I have tried both and I get the same outcome. – CGarden Apr 05 '21 at 21:02
  • @Progman if I don't use those then I get no output in my browser or nothing happens when I press the submit button on the form. The column in MySql is quite dirty data. It's stories that people have sent in e-mails or other formats and has been pasted into that column in MySql. I'll try to get the output from the ```json_encode()```. Still a newb. Thank you. – CGarden Apr 05 '21 at 21:07
  • 1
    @CGarden The problem might be at the consuming side, where the JSON data is read and parsed. But check the result of `json_encode()` or do a `var_dump($data);` to check, if the quotes are there or already missing. When the single quotes aren't there then they weren't saved in the database in the first place. – Progman Apr 05 '21 at 21:11
  • @Progman thank you, I'll take a look, that's helpful. I've checked in my database and the single quotation marks are there. – CGarden Apr 05 '21 at 21:14
  • @Progman I did what you recommended. The JSON extract shows no apostrophes for the example in my question. The database field shows apostrophes (’) but if I copy and past that into a text file the apostrophes are dropped. However, the JSON is displaying single quotation marks (') as \' and these are coming through correctly onto my website. So the issue is with apostrophes. – CGarden Apr 06 '21 at 20:29
  • @CGarden Please [edit] your question to include the result of the `json_encode()` call and add the content of the output of `var_dump($data);` to your question as well. – Progman Apr 06 '21 at 20:33
  • @Progman thank you. In what format should I add that data? As a file? – CGarden Apr 06 '21 at 20:36
  • 1
    @CGarden Use the code block format for the JSON content as well as for the output of `var_dump()`. – Progman Apr 06 '21 at 20:38
  • @Progman I've made the edit as recommended. I like the way the Question body formats the JSON data. Neat and readable. – CGarden Apr 06 '21 at 20:52
  • 1
    @CGarden Do you still have the `nl2br()` and `htmlentities()` lines in your code? Please add a `var_dump($row);` inside the `while()` loop to get the actual content returned from the database via the `fetch_assoc()` and add the content to your question. – Progman Apr 06 '21 at 20:53
  • @Progman these are still turned on yes. I'll make the amendment. Can I not just remove these functions from the code? – CGarden Apr 06 '21 at 20:56
  • @CGarden You should remove them, they are not needed in this context. – Progman Apr 06 '21 at 21:03
  • @Progman I've made the requested amendment. Thank you. Regarding the nl2br() and htmlentities(), if I don't use them, then the majority of stories do not populate in the web browser. Thank you for all the guidance so far. It's been very helpful. – CGarden Apr 06 '21 at 21:17
  • That 48-line snippet is completely irrelevant to your question and only makes unnecessary eye strain for volunteers and researchers. It is safe to remove that code block. There is a different between single quotes and curly single quotes. Which is it? I'd unconditionally replace the curly quotes with standard quotes and see if that made any difference (`str_replace()`). Also, `$result` can be instantly passed to a `foreach()`. https://stackoverflow.com/a/66775416/2943403 – mickmackusa Apr 06 '21 at 21:25
  • [UTF-8 All the way through?](https://stackoverflow.com/q/279170/2943403) – mickmackusa Apr 06 '21 at 21:41
  • @mickmackusa thank you for your very direct talking. – CGarden Apr 06 '21 at 22:16
  • @mickmackusa the ```str_replace()``` changing a curly quote to a standard quote worked. Thank you. Happy to accept this as the answer, if you write one. – CGarden Apr 08 '21 at 20:30
  • @CGarden If your question is resolved, it would be better to simply self-delete this page. The [mcve] isn't very clear so it will not be very useful to researchers / Stack Overflow. My philosophy on question quality and answering has tightened over the years. I used to answer every question that I read, but now I have learned that only the best, clear, complete, and unique questions should be answered and retained here. Stack Overflow is not a Help Desk. More accurately it is meant to be a Researcher's Paradise. Paradise only happens by answering clear, unique questions. Happy coding. – mickmackusa Apr 08 '21 at 21:49

0 Answers0