PDO error: Can't initialize character set utf8_general_ci

Question

PHP on my web host was recently updated and now my old MySQL queries are showing the "deprecated" error message. I need to convert mysql_query code to PDO. Here's what I am starting with:

<?

    $con = mysql_connect("localhost", "username", "password");
    if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("database_name", $con);

        $result = mysql_query("select * from event_calendar where event_year >= '2017' order by event_datenumber asc")
        or die(mysql_error());

     while($row = mysql_fetch_array($result)) {

                $event_date = $row['event_date'];
                $event_month = $row['event_month'];
                $event_monthname = $row['event_monthname'];
                $event_year = $row['event_year'];
                $event_datenumber = $row['event_datenumber'];
                $event_starttime = $row['event_starttime'];
                $event_location = $row['event_location'];
                $event_city = $row['event_city'];
                $event_state = $row['event_state'];
                $event_directions = $row['event_directions'];

    $testmonth = date("F");
    $testmonth2 = date("m");
    $testdate = date("d");
    $testyear = date("Y");
    $testdatenumber = $testyear.$testmonth2.$testdate;

    ?>

    <?

    if ($testdatenumber <= $event_datenumber){

    ?>

    <p>
    <b><? echo $event_monthname.' '.$event_date ?></b> - <? echo $event_starttime ?><br>
    <? echo $event_location ?><br>
    <? echo $event_city ?>, <? echo $event_state ?><br>
    <a href="<? echo $event_directions ?>" target="_blank"><b>Directions</b></a>
    </p>

    <? } else {} ?>

<?  } ?>

I'm totally new to this and don't know where to start. I also noticed that today it is now saying "No database selected" as well.

I've been atempting to rewrite this myself in PDO line by line and so far I am getting:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [2019] Can't initialize character set utf8_general_ci (path: /usr/share/mysql/charsets/)' in /my_file_location/test-this.php:21 Stack trace: #0 /my_file_location/test-this.php(21): PDO->__construct('mysql:host=localhost', 'username', 'password') #1 {main} thrown in /my_file_location/test-this.php on line 21

My line 21, referenced in the error above, is as follows:

$db = new PDO('mysql:host=localhost;dbname=database_name;charset=utf8_‌​general_ci', 'username', 'password');

I've now changed utf8_general_ci to just utf8 and now have the following message:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000] [1044] Access denied for user 'username'@'localhost' to database 'database_name'' in /my_file_location/test-this.php:21 Stack trace: #0 /my_file_location/test-this.php(21): PDO->__construct('mysql:host=localhost', 'username', 'password') #1 {main} thrown in /my_file_location/test-this.php on line 21

My web host is using a Windows operating system, I believe, and the PHP version it was just upgraded to is 5.6.30 but I have no idea about the mySQL database.

UPDATE: OK, after a few days reading and poking around, I have managed to come up with a working rewrite. I now have the following working:

<?php
    $db = new PDO('mysql:host=localhost;dbname=database_name;charset=utf8mb4', 'username', 'password', array(PDO::ATTR_EMULATE_PREPARES => false, 
                                                                                                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

    foreach($db->query('SELECT * FROM event_calendar WHERE event_year >= 2017 ORDER BY event_datenumber ASC') as $row) {

    $testmonth = date("F");
    $testmonth2 = date("m");
    $testdate = date("d");
    $testyear = date("Y");
    $testdatenumber = $testyear.$testmonth2.$testdate;

if ($testdatenumber <= $row['event_datenumber']) {

    echo "<p>";
    echo "<u><h4>".$row['event_monthname']." ".$row['event_date'].", ".$row['event_year']."</h4></u>";
    echo "<b>What:</b> ".$row['event_title']."<br/>";
    echo "<b>Where:</b> ".$row['event_location']." - ".$row['event_address1'].", ".$row['event_city'].", ".$row['event_state']." ".$row['event_zip']."<br/>";
    echo "<b>When:</b> ".$row['event_starttime']."<br/>";
    echo "<b>When:</b> <a href='".$row['event_directions']."' target='_blank'><b>Click here</b></a>";
    echo "</p>";

    } else {}

    }

?>

Now I just wish I could figure out how to limit results to only the first three rows that meet the criteria. I've tried several things so far but have not yet been successful. I guess it's back to reading and testing until I find something that works.

Answer 1

The error message is clear: there is no such charset. And never has been.

utf8_general_ci is a collation, while you need a character set here, which is called as just utf8 or, rather, nowadays it should be utf8mb4.

Let me recommend you my PDO tutorial which will help you to avoid a lot of confusions like this.

Answer 2

UPDATE: OK, after a few days reading and poking around, I have managed to come up with a working rewrite. I now have the following working:

<?php
    $db = new PDO('mysql:host=localhost;dbname=database_name;charset=utf8mb4', 'username', 'password', array(PDO::ATTR_EMULATE_PREPARES => false, 
                                                                                                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

    foreach($db->query('SELECT * FROM event_calendar WHERE event_year >= 2017 ORDER BY event_datenumber ASC') as $row) {

    $testmonth = date("F");
    $testmonth2 = date("m");
    $testdate = date("d");
    $testyear = date("Y");
    $testdatenumber = $testyear.$testmonth2.$testdate;

if ($testdatenumber <= $row['event_datenumber']) {

    echo "<p>";
    echo "<u><h4>".$row['event_monthname']." ".$row['event_date'].", ".$row['event_year']."</h4></u>";
    echo "<b>What:</b> ".$row['event_title']."<br/>";
    echo "<b>Where:</b> ".$row['event_location']." - ".$row['event_address1'].", ".$row['event_city'].", ".$row['event_state']." ".$row['event_zip']."<br/>";
    echo "<b>When:</b> ".$row['event_starttime']."<br/>";
    echo "<b>When:</b> <a href='".$row['event_directions']."' target='_blank'><b>Click here</b></a>";
    echo "</p>";

    } else {}

    }

?>

Now I just wish I could figure out how to limit results to only the first three rows that meet the criteria. I've tried several things so far but have not yet been successful. I guess it's back to reading and testing until I find something that works.

Answer 3

The correct immediate solution is to disable the showing of errors on your live site. This should be done anyway, since you never want to expose that information to users:

ini_set('display_errors', false);

Do not do this on your non-live (test) sites, as you want to see errors there.

Once you have put this in place on your current site, your task to migrate away from this old database extension will be less urgent. My guess is that you're now on PHP 5.6, which still supports the mysql_ functions, but you will still need to prepare for 7.x, where they have been removed.