PHP - Fetching data that contains single quotes

Question

In my mysql database, I have some text that contains single quotes ’ and when I try to fetch that data, it shows as null. How can I fix this issue.

Thanks

Here is what I am doing

$products =   "SELECT * FROM products ";

$result = @mysql_query($products, $connection) or showSQLError();
    if($result) {
        $results_array = array();
        while($row = mysql_fetch_array($result)) {
            $results_array[] = array(
                'name' => $row['name'],
                'description' => mysql_real_escape_string(nl2br($row['description']))
            );
        }
    }

and my string is

Puppy’s dental health.

and result is

"description":null

`single quotes ’` or `single quotes '`? two different animals. — Funk Forty Niner, Dec 16 '15 at 13:09
i'll bet the problem is the insert, not the fetch. did you verify that the single quotes are actually in the db? — I wrestled a bear once., Dec 16 '15 at 13:10
Please start using `mysqli` or `PDO` as `mysql` is deprecated and removed in PHP 7 — SuperDJ, Dec 16 '15 at 13:13
`mysql_real_escape_string()` is for inserting to the database. Remove that. `'description' => nl2br($row['description'])` — I wrestled a bear once., Dec 16 '15 at 13:13
`"description":null` I think your `description` column is failing you, not the one with the quotes. It's hard to say what's in what here really. — Funk Forty Niner, Dec 16 '15 at 13:16
Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Dec 16 '15 at 13:19
There is nothing in your shown code that would cause that. If you `var_dump($results_array);` immediately after the while loop, what do you see? — I wrestled a bear once., Dec 16 '15 at 13:32
@Pamblam you are right. I can see that.. so the problem is `json_encode` ? — Tim Tuffley, Dec 16 '15 at 13:37
No. json_encode can handle your single quotes just fine. The issue is somewhere else. show more code. — I wrestled a bear once., Dec 16 '15 at 13:38

M1ke · Answer 1 · 2015-12-16T15:36:31.493

-1

Here's some fixed code:

uses mysqli rather than mysql so you're using techniques that are more secure and well supported
Removes use of @ to suppress error messages; supressing errors is a silly ability of PHP, it means anyone using the code could be getting PHP level warnings and be unaware, which in turn can lead to confusion.

Removes mysql_real_escape_string() which is likely removing single quotes from your output

$connection = mysqli_connect('host', 'user', 'password', 'database');

$result = mysqli_query($connection, $products) or die(mysqli_error($connection));
if ($result){
    $results_array = [];
    while ($row = mysqli_fetch_array($result)){
        $results_array[] = [
            'name' => $row['name'],
            'description' => nl2br($row['description']),
        ];
    }
}

edited Dec 16 '15 at 15:36

answered Dec 16 '15 at 13:20

M1ke

6,166
4
32
50

true, but doesn't answer the question. this should have been a comment. – I wrestled a bear once. Dec 16 '15 at 13:33
It both attempts to answer the question and does the job of correcting use of deprecated code - despite the number of comments suggesting it, people tend not to alter their code unless they can see a clear example of an alternative. Additionally, comments are not good places for blocks of code as they don't allow line breaks. – M1ke Dec 16 '15 at 15:34
you're right about all that, but there are plenty of places on Stack that already explain mysqli/pdo. you should have linked to them in the comments instead of posting an answer that's not an answer. I know it was after you posted this, but we've already established that his problem was not in the code he gave.\ – I wrestled a bear once. Dec 16 '15 at 15:38
If the answer's wrong then it won't be accepted; with the information in the question there was only one area that could leave an output without quotes in it. Looking at subsequent comments I see people are now discussing `json_encode` but that's not even in the question. Additionally, there are plenty of places across the web explaining mysqli/pdo but having code re-written is far more useful to an individual. – M1ke Dec 16 '15 at 15:45
What you're doing here is against SO regulations. That's all I'm saying. I won't downvote you but I can think of several SO user that would if they saw this. Take a peek at this, near the bottom and have a nice day. http://stackoverflow.com/help/how-to-answer – I wrestled a bear once. Dec 16 '15 at 15:53
I'm pretty sure attempting to answer a question that's not well asked is not against regulations. For example they quote "the answer can be 'don't do that' but should include 'try this instead'" which is what I have done. Since I tried to answer there's been discussion in the comments and edits to the question. I've been on the site a while and I'm confident that my contributions add value; you're welcome to disagree. – M1ke Dec 16 '15 at 16:43

PHP - Fetching data that contains single quotes

1 Answers1