Blank web page when converting mysql to PDO

Question

Hi I have started to change my mysql to PDO before my code starts getting large and risk of causing problems i have tried changing a section of my code but the screen shows as white this is my original code:

class SelectList
{
    protected $conn;

        public function __construct()
        {
            $this->DbConnect();
        }

        protected function DbConnect()
        {
            include "db_config.php";
            $this->conn = mysql_connect($host,$user,$password) OR die("Unable to connect to the database");
            mysql_select_db($db,$this->conn) OR die("can not select the database $db");
            return TRUE;
        }
         public function ShowPrinciple()
        {
            $sql = "SELECT principle.principle_id,principle.description,principle.section_id,COUNT(media.principle_id) as media_count
                    FROM principle 
                    LEFT OUTER JOIN media ON principle.principle_id = media.principle_id 
                    AND principle.section_id = media.section_id 
                    WHERE principle.section_id={$_POST['id']}
                    GROUP BY principle.principle_id,principle.description";
            $res = mysql_query($sql,$this->conn);
            $principle = '<option value="%">choose...</option>';
            while($row = mysql_fetch_array($res))
            {
                $principle .= '<option value="' . $row['principle_id'] . '">' . $row['description'].  '...('.$row['media_count'].') </option>';
            }
            return $principle;
        }
}
$opt = new SelectList();

I have tried a simple query to start off with to change but I seem to have done something wrong could some one point out to me I'm positive it will be something I have changed.

This is the code that I have currently got:

class SelectList
{

$host = '127.00.00.00';
$user = 'user';
$password = 'password';
$db =  'database'; 

 try{ 
    $conn = new PDO("mysql:host=$host;dbname=$db", $user, $password);
    echo 'connected to the database<br />';

    public function ShowPrinciple()
    {

        $stmt = $conn -> prepare(
                    "SELECT principle.principle_id,principle.description,principle.section_id,COUNT(media.principle_id) as media_count
                    FROM principle 
                    LEFT OUTER JOIN media ON principle.principle_id = media.principle_id 
                    AND principle.section_id = media.section_id 
                    WHERE principle.section_id={$_POST['id']}
                    GROUP BY principle.principle_id,principle.description");
        $q = $conn->query($stmt) or die("failed!)";
        $principle = '<option value="%">choose...</option>';
        while($row = fetchAll($q))
        {
            $principle .= '<option value="' . $row['principle_id'] . '">' . $row['description'].  '...('.$row['media_count'].') </option>';
        }
        return $principle;
    }catch(PDOException $e){
        echo $e->getMessage();
    }
}
$opt = new SelectList();

Any help would be much appreciated.

Answer 1

So you don 't know how to write classes, right? First: check your error reporting! Turn it up if it 's not turned up. PHP should output some fatal errors here. The following code can def not work:

class Bla {
    $var1 = 'foo';
    $var2 = 'bar';

    try {
        public function doSth() { ... }
    } catch (SomethingException $e) {
        doSthWthExcpetion();
    }
}

That 's not proper PHP. Make yourself comfortable with dependency injection or something else, that work 's as this:

class Bla {
    protected $dbHandle = null;

    public function __construct() {
        $this->dbConnect();
    }

    public function dbConnect() {
        $this->dbHandle = new PDO(...);
    }

    public function doSthDatabseish($variable) {
        try {
            $stmt = $this->dbHandle->prepare("SLECET something FROM myTable WHERE variable = :variable");
            $stmt->bindValue(':variable', $variable);
            $stmt->execute();

            foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
                $value[] = $row;
            }
        } catch (PDOException $e) {
            // errorhandling
        }

        return $value;
    }
}

Do you see the difference? Never write code between functions. Code belongs in the function definition. As your first example class shows, you establish the database connection in a connectDB() function. So why don 't you in the PDO Version of your class?

Answer 2

your $conn->prepare() call is missing the closing parenthesis. (ie. change $conn->prepare(... to $conn->prepare(...); (note te ")" character at the end).

Same thing goes for your $conn->query() or die(... part.

The reason you're seeing a white page is because your server is configured not to show error on screen. Adjust your php.ini file to display_errors=On and error_reporting=E_ALL to actually see the errors.

Answer 3

Your main error is with

or die("failed!)"; 

which is redundant and with the parenthesis wrong.

To set error mode . After

$conn = new PDO(...

Add

$conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); 

Also your use of PDO is wrong.You either use

query() Without parameters (Your case)

$query = ( "SELECT ....");
$stmt = $conn->query($query);  
// setting the fetch mode  
 $stmt->setFetchMode(PDO::FETCH_ASSOC);  
$principle = '<option value="%">choose...</option>';
while($row = $stmt->fetch()) { 

etc...

OR prepare() execute() With parameters

$stmt = $conn -> prepare("SELECT....")
//Bind parameters here or as an array in execute()
$stmt->execute();
$stmt->setFetchMode(PDO::FETCH_ASSOC); 
   while($row = $stmt->fetch()){