How to add url slug to existing data in php mysql

Question

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

mysql_query("insert into blog(title,body,url) values('$title','$body','$url')"); — user3061787
– user3061787, Commented Jun 28, 2014 at 14:37
I'm not sure I understand your question. Are you asking if SQL has a statement to update existing rows? — Álvaro González
– Álvaro González, Commented Jun 28, 2014 at 14:39
yes of course I want to update my url field which is empty with the help of above code — user3061787
– user3061787, Commented Jun 28, 2014 at 14:41

Johnny Dew · Accepted Answer · 2014-06-28 14:57:40Z

1

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
}

edited Jun 28, 2014 at 14:57

answered Jun 28, 2014 at 14:43

Johnny Dew

9912 gold badges13 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3061787 Over a year ago

absolutely wanted this please provide me little more help in sql query to update url slug

Golu · Accepted Answer · 2014-06-28 15:12:39Z

1

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

answered Jun 28, 2014 at 15:12

Golu

4145 silver badges17 bronze badges

Collectives™ on Stack Overflow

How to add url slug to existing data in php mysql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related