store gujarati data from csv to MySQL database using php

Question

Following code which is used to browse csv file. Fatch data from csv and store in a MySQL database.

<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=UTF-8">
</head>

<body>
    <form name="import" method="post" enctype="multipart/form-data">
        કેમ છે: <input type="file" name="file" /><br />
        <input type="submit" name="submit" value="Submit" />
    </form>
<?php
    include ("connection.php");

    if(isset($_POST["submit"]))
    {
        $file = $_FILES['file']['name'];
        $handle = fopen($file, "r");
        $c = 0;
        while(($filesop = fgetcsv($handle, 1000, ",")) !== false)
        {
            $name = $filesop[0];
            $marks = $filesop[1];
            mysql_query("set name utf8");
            $query = "INSERT INTO temp (name, marks) VALUES ('".$name."','".$marks."')";
            echo $query;
            $sql = mysql_query($query);
        }
    }
?>

    </div>
</body>
</html>

Following code is used connect database.

<?php

    $hostname = "localhost";
    $username = "root";
    $password = "";
    $database = "test";
    $conn = mysql_connect("$hostname","$username","$password") or die(mysql_error());
    mysql_select_db("$database", $conn) or die(mysql_error());

?>

When I run code its stored data like "????????".

My CSV file like below

Following phpmyadmin version information

Following is my table structure

See this answer http://stackoverflow.com/a/6488070/946170 – Imdad Oct 31 '15 at 19:54 — Imdad, Oct 31 '15 at 19:54
@imdad i want to import using php programming not directly – Hardik Visa Nov 01 '15 at 10:05 — Hardik Visa, Nov 01 '15 at 10:05

Mubin · Answer 1 · 2015-11-01T11:00:46.000

1

if you're getting stored ???????? in database, then,

You need to check the database collation first, change it to utf8_general_ci or utf8mb4_general_ci if simple utf_general_ci doesn't work.

Second, if collation is fine then it could also be possible that string is already in utf8 format, and you're converting it forcefully again.

Use following function to convert string to utf8.

function convToUtf8($str){ 
    if( mb_detect_encoding($str,"UTF-8, ISO-8859-1, GBK")!="UTF-8" ) 
        return  iconv("gbk","utf-8",$str);
    else
        return $str; 
}

Then in your code

while(($filesop = fgetcsv($handle, 1000, ",")) !== false)
{
    $name = convToUtf8($filesop[0]);
    $marks = convToUtf8($filesop[1]);
    //mysql_query("set name utf8");   No need to do this now
    $query = "INSERT INTO temp (name, marks) VALUES ('$name','$marks')";
    echo $query;
    $sql = mysql_query($query);
}

and look here and set db collation

edited Nov 01 '15 at 11:00

answered Oct 31 '15 at 20:17

Mubin

4,325
5
33
55

what is your database `collation`? – Mubin Nov 01 '15 at 10:46
and specifically, what is table's `collation`? – Mubin Nov 01 '15 at 10:46
some problem regarding in CSV when i save and close CSV file with UTF-8 format and reopen CSV it display "????" data – Hardik Visa Nov 01 '15 at 12:26
problem with csv file than, ot with DBMS, you need to store it in excel and get it from there – Mubin Nov 01 '15 at 12:28
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/93911/discussion-between-magento-learning-phase-and-mubin). – Hardik Visa Nov 01 '15 at 12:31

store gujarati data from csv to MySQL database using php

1 Answers1