CODEWITHSUNDEEP
phpmysqlurlslug
user3061787
user3061787

Reputation: 36

How to add url slug to existing data in php mysql

I have a site of books. My books table contains fields id,title,pdf and body and I hav posted about 4000 books and my books show the url like https://www.bookspk.site/2017/12/barre-sagheer-pak-o-hind-me-ilam-e-hadith.html. but now I am created url field and able to use url slug with this code

function string_limit_words($string, $word_limit) {
$words = explode(' ', $string);
return implode(' ', array_slice($words, 0, $word_limit));
}
$blog='';

if($_SERVER["REQUEST_METHOD"] == "POST")
{
$title=mysql_real_escape_string($_POST['title']);
$body=mysql_real_escape_string($_POST['body']);
$title=htmlentities($title);
$body=htmlentities($body);
$date=date("Y/m/d");

$newtitle=string_limit_words($title, 6);
$urltitle=preg_replace('/[^a-z0-9]/i',' ', $newtitle);

$newurltitle=str_replace(" ","-",$newtitle);
$url=$date.'/'.$newurltitle.'.html';

but the problem is that how can I insert url slug my already posted data thanks in anticipation for kindness and answer this stupid questionand

Upvotes: 0

Views: 2129

Answers (2)

Golu
Golu

Reputation: 414

Here is an version of Johnny Dew's code , and since i cannot comment yet , I am posting this as a answer ..


    $res = $mysqli->query('SELECT id, title FROM table WHERE slug = ""');
    while($obj = $res->fetch_object()){
       $newtitle=string_limit_words($obj->title, 6);
       $urltitle=preg_replace('/[^a-z0-9]/i',' ', $newtitle);

       // AND HERE UPDATE YOUR TABLE WITH A WHERE ID = $obj->id
        $mysqli->query('UPDATE table SET slug=$urltitle WHERE ID = $obj->id');
    }

You probably will want to use a slug class/function to generate better slugs from title , other than by just replacing alphanumeric characters with space

Upvotes: 1

Johnny Dew
Johnny Dew

Reputation: 981

I am not sure I understand the question, but if you are trying to get slugs for all the previous records that were inserted in your database before you added the code that now creates your slug. You have to create a query that selects all the records that don't have a slug, call the function that creates your slug and update each record with the correct value.

(Edit)
First off, you should not use mysql_query anymore because it has been deprecated since 5.5.0. Here is example of the code you could use :

$res = $mysqli->query('SELECT id, title FROM table WHERE slug = ""');
while($obj = $res->fetch_object()){
   $newtitle=string_limit_words($obj->title, 6);
   $urltitle=preg_replace('/[^a-z0-9]/i',' ', $newtitle);
   // AND HERE UPDATE YOUR TABLE WITH A WHERE ID = $obj->id
}

Upvotes: 1

Related Questions