1

Well i got this code that grabs data from Steam Web Api into variables, it print the data and then insert it into the database. My problem is that the name ("personaname" from the web api) can have special characters like russian characters, greek, etc. I configured php with utf8 so i got no problem with these characters when i print them directly from the api to my php code, but when i try to insert them into the database they got corrupted into "????? ?????" or "??o??f???". I configured the PDO with utf8 utf8_unicode_ci and same for the mysql. I tried using htmlentities(), htmlspecialchars() and utf8_encode() with no luck. Sorry for my broken english.

$steamkey = "x";
$id_banuser_final = "76561198088987175";


   $api = "http://api.steampowered.com/ISteamUser/GetPlayerSummaries/v0002/?       key=".$steamkey."&steamids=".$id_banuser_final;


//  Initiate curl
$ch1 = curl_init();
// Disable SSL verification
curl_setopt($ch1, CURLOPT_SSL_VERIFYPEER, false);
// Will return the response, if false it print the response
curl_setopt($ch1, CURLOPT_RETURNTRANSFER, true);
// Set the url
curl_setopt($ch1, CURLOPT_URL,$api);
// Execute
$json=curl_exec($ch1);
// Closing
curl_close($ch1);


$decoded = json_decode($json);

$nombre = $decoded->response->players[0]->personaname;
    print_r($nombre);
    echo "<br/>";

    if(isset($decoded->response->players[0]->loccountrycode)) {
        $loccountrycode = $decoded->response->players[0]->loccountrycode;
        print_r($loccountrycode);
        echo "<br/>";
    }

    $steam_id_sixtyfour = $decoded->response->players[0]->steamid;
    print_r($steam_id_sixtyfour);
    echo "<br/>";

    $avatarfull = $decoded->response->players[0]->avatarfull;
    echo "<img src='".$avatarfull."' alt='Avatarfull' />";
    echo "<br/>";

    $avatarmedium = $decoded->response->players[0]->avatarmedium;
    echo "<img src='".$avatarmedium."' alt='Avatarmed' />";
    echo "<br/>";

    $avatar = $decoded->response->players[0]->avatar;
    echo "<img src='".$avatar."' alt='Avatar' />";
    echo "<br/>";

    $profileurl = $decoded->response->players[0]->profileurl;
    print_r($profileurl);
    echo "<br/>";

    $datenow = date("Y-m-d");

    echo $datenow;

    echo "<br/>";
    echo "............................................";
    echo "<br/>";

require_once('incluidos/connectdb.php');

$db = connect_db();

$q ="INSERT INTO `d2bd`.`profiles`(`steamid`,`fecha_cons`,`loccountrycode`,`personaname`,`avatar`,`avatarmedium`,`avatarfull`,`profileurl`) VALUES(?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE fecha_cons=?, loccountrycode=?, personaname=?, avatar=?, avatarmedium=?, avatarfull=?"; 

            $nombrebind = utf8_encode($nombre);

            $statement = $db->prepare($q);

            $statement->bindParam(1, $steam_id_sixtyfour);

            $statement->bindParam(2, $datenow);

            $statement->bindParam(3, $loccountrycode);

            $statement->bindParam(4, $nombrebind);

            $statement->bindParam(5, $avatar);

            $statement->bindParam(6, $avatarmedium);

            $statement->bindParam(7, $avatarfull);

            $statement->bindParam(8, $profileurl);

            $statement->bindParam(9, $datenow);

            $statement->bindParam(10, $loccountrycode);

            $statement->bindParam(11, $nombrebind);

            $statement->bindParam(12, $avatar);

            $statement->bindParam(13, $avatarmedium);

            $statement->bindParam(14, $avatarfull);

            $statement->execute();



echo "-.-.-.--.-.-.-.-..--.-.-";
echo "<br/>";

        $q2 ="SELECT * FROM profiles WHERE steamid = ?"; 

        $statement2 = $db->prepare($q2);

        $statement2->bindParam(1, $steam_id_sixtyfour);

        $status = $statement2->execute();

        if(($status) && ($statement2->rowCount() > 0)) {
            while($row=$statement2->fetch()){
                $fecha_dato=$row['personaname']; 
                $fecha2= utf8_decode($fecha_dato);
                print_r($fecha2);
            }
        }

code from the "connectdb.php" of the PDO

function connect_db()
{
    try
    {
        $connection = new PDO('mysql:host=localhost;dbname=d2bd;charset=utf8', 'root', '123123');
        $connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
        $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $connection->setAttribute(PDO::ATTR_PERSISTENT, true);
        $connection->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAMES 'utf8' COLLATE     'utf8_unicode_ci'");
    }
    catch (PDOException $e)
    {
        // Proccess error
        echo 'Cannot connect to database: ' . $e->getMessage();
    }

    return $connection;
}
holyknight
  • 315
  • 1
  • 3
  • 13
  • How are you viewing the *"corrupt"* database entries? Also, your `connect_db` function should exit if it can't connect – Phil Sep 09 '14 at 01:24
  • I check the entries from SQLyog and then i added a SELECT in the php for comparing the "personaname" from the API and the one from the database. About the connect_db you mean i need to add a break in the catch ? ty – holyknight Sep 09 '14 at 01:29
  • That's cool. I only ask as whatever you use to view the entries needs to be UTF compatible but I can see you've already done that by displaying the results directly from the API then via the `SELECT`. Regarding your function, IMO I just wouldn't catch the exception. – Phil Sep 09 '14 at 01:32
  • what about convert your variable to utf8 before inserting to db: http://stackoverflow.com/questions/910793/detect-encoding-and-make-everything-utf-8 – Teddybugs Sep 09 '14 at 01:33
  • What character set / collation is your `profiles` table? What about its columns? This may be worth a read ~ http://stackoverflow.com/questions/279170/utf-8-all-the-way-through – Phil Sep 09 '14 at 01:34
  • All the database is utf8, utf8_unicode_ci. I tried htmlentities(), htmlspecialchars(), utf8_encode() but they didnt work. – holyknight Sep 09 '14 at 02:17

0 Answers0