1

I've kept a spreadsheet of Polish words on google docs and I'd like to import a CSV download of this file into my SQL database using PHPMyAdmin.

The database will then be queried via my website and display the Polish words.

I've exported the CSV file from google docs, imported it as a utf-8 document into my spreadsheet. In the database I can see that characters such as ż and ę etc. are being replaced with '?'. The site also shows this.

In the header I am setting the charset to utf-8. Below is the code I use to connect to the DB.

    <?php
$host_name = 'xxxxx';
$database = 'xxxxx';
$user_name = 'xxxxx';
$password = 'xxxxx';
$connect = mysqli_connect($host_name, $user_name, $password, $database);
// mysqli_query("SET NAMES utf8");
if (mysqli_connect_errno()) {
    die('<p>Failed to connect to MySQL: '.mysqli_connect_error().'</p>');
} else {
     '<p>Connection to MySQL server successfully established.</p >';
}


?>

Is there something I'm obviously doing wrong?

Owen Garfield
  • 35
  • 7
  • 1
    Did you set the table in the database to expect utf8 char set? – RiggsFolly Jul 27 '18 at 12:12
  • 1
    If you did the upload to the database using PHP did you set the connection to the database to be UTF8? – RiggsFolly Jul 27 '18 at 12:13
  • Are you using PHP or PHPMyAdmin? – user3783243 Jul 27 '18 at 12:21
  • I've set the collation to utf_unicode_ci in PHPMyAdmin. I'm using PHP to query the database on my site. – Owen Garfield Jul 27 '18 at 12:32
  • Hi, show us the connection to mysqli or pdo – RiggsFolly Jul 27 '18 at 12:34
  • Failed to connect to MySQL: '.mysqli_connect_error().''); } else { '

    Connection to MySQL server successfully established.

    '; } ?>     – Owen Garfield Jul 27 '18 at 12:35
  • Please edit the question to include the code, https://stackoverflow.com/posts/51557676/edit. Also remove `I'd like to import a CSV download of this file into my SQL database using PHPMyAdmin.` since you aren't using PHPMyAdmin to import, that will mislead people. – user3783243 Jul 27 '18 at 12:36
  • Uncomment this line `// mysqli_query("SET NAMES utf8");` – RiggsFolly Jul 27 '18 at 12:37
  • Apologies. I am using the import tab within PHPMyAdmin to upload the CSV file so it felt like the logical way to describe it. – Owen Garfield Jul 27 '18 at 12:38
  • It looks like you commented out the line that sets the character set. I think this http://php.net/manual/en/mysqli.set-charset.php is the preferred approach anyway though. – user3783243 Jul 27 '18 at 12:38
  • // mysqli_query("SET NAMES utf8"); was originally uncommented but didn't appear to be working anyway. – Owen Garfield Jul 27 '18 at 12:39
  • When you say you can see `?` is that when you view the tables using `phpMyAdmin` or just when you view data in the browser gathered by your PHP code – RiggsFolly Jul 27 '18 at 12:39
  • How does this code relate to the import? It sounds like you are importing via PHPMyadmin and the characters are being lost there. If that is the case I'd guess the table is not UTF8. If the issue is retrieving the chars that could be with the DB connection you have here. Try the function I linked previously if the latter. – user3783243 Jul 27 '18 at 12:41
  • As the manual states about your current character set method... `Using mysqli_query() to set it (such as SET NAMES utf8) is not recommended.` – user3783243 Jul 27 '18 at 12:42
  • I can see the '?'s in the table in PHPMyAdmin, so I assume the issue is perhaps what google docs spits out as a CSV file?. EDIT - Although, opening the CSV file in a text editor shows the correct characters, – Owen Garfield Jul 27 '18 at 12:43
  • Well what does the DB say the encoding is? `SELECT CCSA.character_set_name FROM information_schema.TABLES T, information_schema.COLLATION_CHARACTER_SET_APPLICABILITY CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = "DatabaseName" AND T.table_name = "TableName";` (Replace the values in the double quotes) – user3783243 Jul 27 '18 at 12:49
  • That returns character_set_name of ut8 – Owen Garfield Jul 27 '18 at 12:56
  • Please add the `create table` statement, and a sample string. The create table can be found with `show create table ....`. (... is the table name) – user3783243 Jul 27 '18 at 12:59
  • I am an idiot. I had the wrong character set on one of the columns. It's now working perfectly. Thank you all for your time. I truly appreciate it! – Owen Garfield Jul 27 '18 at 13:03

0 Answers0