Create slug across the MYSQL database

Question

Create slug across the MYSQL database

Navigation

#1 by (1 votes)

0

I have an older MYSQL database and it has a news table and I needed to create a "slug" column to save the news slug. So, I would like to create an automatic slug for all records that are registered in this table.

Table:

Columns

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

Is there any mysql function that can run in phpmyadmin?

mysql sql phpmyadmin

asked by anonymous 16.08.2018 / 15:57

1 answer

Error in Recaptcha returns me an error in file_get_contents Date orders of a reference in the middle of other dates and references

score 1 · Accepted Answer

I was able to do the following. I created a file and circled 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