CODEWITHSUNDEEP
phpmysqlpdo
Media219
Media219

Reputation: 11

PHP, MySQL and PDO - results from database missing apostrophes

I am pulling out my hair so hope someone can help.

I have changed my Query function from mysql to mysqli to PDO and still cannot get the results to display apostrophes - I can see them in the database field so know they are there.

I am using a class and a function within where I feed the query for another page:

My query: 

SELECT sum(quantity) as Total, sum(earnings) as Revenue, title 
from `sales` 
WHERE name = 'john smith' 
AND paid = 'y' 
AND earnings >0 
GROUP BY title

My function:

   $db = new PDO("mysql:host=" . $db_host .";dbname=" . $db_database . ";charset=UTF-8",$db_user,$db_password,array(PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

               $SQL = $query; 
               $stmt = $db->prepare($SQL);             
               $stmt->execute();

            $stmt = $db->query($SQL);

            while($row = $stmt->fetch(PDO::FETCH_ASSOC)) 
            {
                    $result_array[] = $row;                      

            }  

            $db = null;

        return $result_array;

The data is being pulled from the database without problem, except the apostrophe is missing where it should be. This causes me problems as I later use this data to search another table and it fails to match.

I really hope someone can help me before I go totally bald.

All help gratefully received, please be kind :D

Upvotes: 0

Views: 456

Answers (1)

Lawrence Cherone
Lawrence Cherone

Reputation: 46602

In your current code your actually querying the database twice, there is also PDO::fetchAll which will return exactly what your trying do with the loop. Plus for better code initialize your PDO connection outside of your functions and use the global keyword to get scope of $db within the function or pass $db to the function as an argument. 

<?php 
$db = new PDO("mysql:host=" . $db_host .";dbname=".$db_database, $db_user, $db_password,
    array(
        PDO::ATTR_EMULATE_PREPARES => false,
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    )
);

function select_sales($name='', $paid='y'){
    global $db;

    $query = "SELECT sum(quantity) as Total,
                 sum(earnings) as Revenue, 
                 title 
              FROM `sales` 
              WHERE `name` = :name AND paid = :paid AND earnings >0 
              GROUP BY `title`";

    $stmt = $db->prepare($query);

    $stmt->bindParam(':name', $name);
    $stmt->bindParam(':paid', $paid);

    $stmt->execute();

    return $stmt->fetchAll();
}
?>

Upvotes: 2

Related Questions