Error message alert

You have to count the results of the query within the loop:

<?php
    $total_sucesso = 0;
    $total_erros = 0;

    for($i=1; $i<=$total; $i++) {
        // Insere os dados no banco de dados
        if ($MySQLiconn->query("INSERT INTO 'medias' SET 'cat'='".$subcat["cat"]."', 'subcat'='".$subcat["id"]."'")) {
            $total_sucesso++;
        }
        else {
            $total_erros++;
        }
    }

    ?><script>
        alert('Total de envios com sucesso <?=$total_sucesso; ?> e erros <?=$total_erros;?>.');
        window.location.href='index.php';
        </script>
    <?
?>  

It even gives you a list of the items that gave an error:

    if ($MySQLiconn->query("INSERT INTO 'medias' SET 'cat'='".$subcat["cat"]."', 'subcat'='".$subcat["id"]."'")) {
        $total_sucesso++;
    }
    else {
        $total_erros.= $subcat["cat"] . "\n";
    }

And then, in the message:

alert('<?=$total_sucesso; ?> envios com sucesso. Erros: <?=$total_erros;?>.');

query("INSERT INTO 'medias' SET 'cat'='".$subcat["cat"]."', 'subcat'='".$subcat["id"]."'");
  if ($sql) {
  $total_envios++;
  } else {
  $total_erros++;
  }
}

if ($sql) {
    echo "
        alert('".$total_envios." Itens inseridos com sucesso !!!\n
        ".$total_erros." Itens com falhas no cadastro !!!"."');
        window.location.href='index.php';
        ";
 } else {         
    echo "
        alert('error Erro ao tentar inserir os itens tente nova mente');
        ";
 }

?>

Mixing PHP opening tags inside the JavaScript string is not a good idea because it makes it difficult to read the code.

I suggest simplifying it as follows:

<?php
// salvar os dados em variáveis
$mensagem = 'error Erro ao tentar inserir os itens tente novamente';
$location = false;
if ($sql) {
    $mensagem =  $total . ' Itens inseridos com sucesso';
    $location = 'index.php';
}

// criar e exibir o javascript
echo '<script>';
printf("alert('%s');\n", $mensagem); // <- atenção para as aspas simples
if (!empty($location)) {
    printf("window.location.href = '%s'\n", $location);
}
echo '</script>';

I used the printf() function on some lines to display the strings formatted. The %s symbol will be replaced by the following parameter. View the documentation for more details.

The most correct way to do this is to use Ajax.

Let's go to the problem:

A Query was run in the Back-End and the result was reported. As the javascript can not see this response, so far we do not have a solution.

In order to arrive at a solution, you will need to use Ajax, which will trigger a request to your file, and in that request, you will say that you need a response, using HTTP semantics that in this case will be GET. >

The response will come in the body of your request, and in javascript you will then receive this response, and you can evaluate the result. So you can post a message to the user.

Practical example using JQuery

$.get('/pagina_do_meu_site&ajax="verifica"').success(function(response){
    if(response>0){
      alert('Itens inseridos com sucesso');
    }else{
      alert('Houve falha na inserção de dados');
    }
});

In the PHP backend (I'll use the simpler way)

<?php
  if($_GET['ajax']=='verifica'){
      echo '1';  //aqui o php retornará o valor 1 para o javascript
      exit;
  }
?>