Change utf8mb4 to utf8

Question

I'm trying to make a script that changes my encoding from utf8mb4 to utf8.

My PHP knowlege is a bit outdated and i can't make the script work with mysqli.

this is the base script i had :

<?php
$con = mysql_connect('localhost','user','password');
if(!$con) { echo "Cannot connect to the database ";die();}
  mysql_select_db('dbname');
  $result=mysql_query('show tables');
  while($tables = mysql_fetch_array($result)) {
    foreach ($tables as $key => $value) {
      mysql_query("ALTER TABLE $value CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");
    }}
  echo "The collation of your database has been successfully changed!";
  ?>

it does not work so i tried to update it to mysqli and now i have this :

<?php
$mysqli = new mysqli("localhost", "root", "", "test");

if (mysqli_connect_errno()) {
    printf("connexion error : %s\n", mysqli_connect_error());
    exit();
}

if ($result = $mysqli->query("SELECT DATABASE()")) {
    $row = $result->fetch_row();
    printf("The database is : %s.\n", $row[0]);
    $result->close();
}

  $result=mysqli_query('show tables');
  while($tables = mysqli_fetch_array($result)) {
    foreach ($tables as $key => $value) {
      mysqli_query("ALTER TABLE $value CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");
    }}
  echo "The collation of your database has been successfully changed!";
  ?>

I think the problem is in the last part, the errors i get are :

Thank you for your help ! :)

mysqli_query() need first parameter as your connection. Use it as `$result=mysqli_query($mysqli,'show tables'); ` Read http://php.net/manual/en/mysqli.query.php — Saty, Jul 15 '16 at 12:51
I tried this but now i get 50 times the same error : `mysqli_query() expects at least 2 parameters, 1 given ` — Relisora, Jul 15 '16 at 12:54
My bad i missed a point, works fine, thank's a lot ! you can post your solution i'll mark it as resolved — Relisora, Jul 15 '16 at 12:56
Converting _from_ utf8mb4_ to _utf8_ could lose data. Emoji and some of Chinese cannot be represented in utf8. — Rick James, Jul 15 '16 at 22:20

score 1 · Accepted Answer · answered Jul 15 '16 at 12:57

mysqli_query() need first parameter as your connection. Use it as

$result = mysqli_query($mysqli, 'show tables');
while ($tables = mysqli_fetch_array($result)) {
    foreach ($tables as $key => $value) {
        mysqli_query($mysqli, "ALTER TABLE $value CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");
    }
}

Change utf8mb4 to utf8

1 Answers1