Create Slug across MYSQL database

Question

I have an older MYSQL database and in it I have a news table and I needed to create a "Slug" column to save Slug from the news. Therefore, I would like to create an automatic Slug for all records registered in this table.

Table:

Columns

Titulo ------------------ Slug
aqui vem o titulo-------- Preciso criar o slug baseado no titulo

Is there any mysql function q can run in phpmyadmin q do that?

Answer 1

This is how I did it. I created a file and put it on the server.

<?php
include "chama_bd.php";

    $query = $mysqli->prepare("SELECT `titulo`, `id` FROM `noticias`");
    $query->execute();
    $slug= get_result($query);
    $query->store_result();

    while ( $r = array_shift( $slug) ) {
    $titulo_a=$r['titulo'];
    $id_a=$r['id'];

    //Cria o slug
    $slug = slugify($titulo_a);

    //Checa se o slug existe no banco de dados, se existir adiciona um número sequencial a frente
    $CheckSlug = $mysqli->query("SELECT * FROM `noticias` WHERE `slug` LIKE '".$slug."%'");
    $numHits = $CheckSlug->num_rows;
    if($numHits > 0){
        $slug = $slug.'-'.$numHits;
    }

    $query = $mysqli->prepare('UPDATE `noticias` SET `slug`=? WHERE `id`=?')or die($mysqli->error);
    $query->bind_param('si', $slug, $id_a);
    $query->execute();

    }
?>

// FUNÇÃO PARA CRIAR SLUGS
function slugify($string) {
    $string = preg_replace('/[\t\n]/', ' ', $string);
    $string = preg_replace('/\s{2,}/', ' ', $string);
    $list = array(
        'Š' => 'S',
        'š' => 's',
        'Đ' => 'Dj',
        'đ' => 'dj',
        'Ž' => 'Z',
        'ž' => 'z',
        'Č' => 'C',
        'č' => 'c',
        'Ć' => 'C',
        'ć' => 'c',
        'À' => 'A',
        'Á' => 'A',
        'Â' => 'A',
        'Ã' => 'A',
        'Ä' => 'A',
        'Å' => 'A',
        'Æ' => 'A',
        'Ç' => 'C',
        'È' => 'E',
        'É' => 'E',
        'Ê' => 'E',
        'Ë' => 'E',
        'Ì' => 'I',
        'Í' => 'I',
        'Î' => 'I',
        'Ï' => 'I',
        'Ñ' => 'N',
        'Ò' => 'O',
        'Ó' => 'O',
        'Ô' => 'O',
        'Õ' => 'O',
        'Ö' => 'O',
        'Ø' => 'O',
        'Ù' => 'U',
        'Ú' => 'U',
        'Û' => 'U',
        'Ü' => 'U',
        'Ý' => 'Y',
        'Þ' => 'B',
        'ß' => 'Ss',
        'à' => 'a',
        'á' => 'a',
        'â' => 'a',
        'ã' => 'a',
        'ä' => 'a',
        'å' => 'a',
        'æ' => 'a',
        'ç' => 'c',
        'è' => 'e',
        'é' => 'e',
        'ê' => 'e',
        'ë' => 'e',
        'ì' => 'i',
        'í' => 'i',
        'î' => 'i',
        'ï' => 'i',
        'ð' => 'o',
        'ñ' => 'n',
        'ò' => 'o',
        'ó' => 'o',
        'ô' => 'o',
        'õ' => 'o',
        'ö' => 'o',
        'ø' => 'o',
        'ù' => 'u',
        'ú' => 'u',
        'û' => 'u',
        'ý' => 'y',
        'ý' => 'y',
        'þ' => 'b',
        'ÿ' => 'y',
        'Ŕ' => 'R',
        'ŕ' => 'r',
        '/' => '-',
        ' ' => '-',
        ',' => '-',
        '.' => '-',
        "'" => '-',
        ';' => '',
        '"' => '',
        '!' => '',
        '?' => '',
        'º' => 'o',
        'ª' => 'a',
        '\'' => '',
        '(' => '',
        ')' => '',
        'R$' => '',
        '|' => '',
        '%' => '',
        '¨' => '',
        '&' => '',
        '*' => '',
        '@' => '',
        '=' => '',
        '+' => '',
    );

    $string = strtr($string, $list);
    $string = preg_replace('/-{2,}/', '-', $string);
    $string = strtolower($string);

    return $string;
}// FIM DA FUNÇÃO QUE CRIA SLUGS