Updates / Refreshes all URL titles in YOURLS database.

update-url-titles.php

<?php

// Updates all YOURLS' URL Page Titles

// 1) Change the database connection details below.
// 2) Update page titles from command line with: php -f update-url-titles.php

$db_host = 'localhost';
$db_name = 'database_name';
$db_user = 'username';
$db_pass = 'password';

$db = new PDO('mysql:host='.$db_host.';dbname='.$db_name, $db_user, $db_pass);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $db->prepare("SELECT * FROM yourls_url");
$stmt->execute();
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach($data as $d){
  $title = page_title($d['url']);
  if(!empty($title)){
    updateTitle($d['keyword'], $title);
  }else{
    echo '*** ERROR GETTING TITLE OF ['.$d['keyword'].'] '.$d['url'].' ***'.PHP_EOL;
  }
}


function updateTitle($keyword, $title){
  GLOBAL $db;
  $stmt = $db->prepare("UPDATE yourls_url SET `title` = :title WHERE `keyword` = :keyword");
  $stmt->bindParam(':keyword', $keyword, PDO::PARAM_STR);
  $stmt->bindParam(':title', $title, PDO::PARAM_STR);
  if($stmt->execute()){
    echo 'Updated: '.$keyword.' - '.$title.PHP_EOL;
  }else{
    echo '** ERROR Updating Keyword: '.$keyword.PHP_EOL;
  }
}


// http://stackoverflow.com/questions/399332/fastest-way-to-retrieve-a-title-in-php
function page_title($url) {
    $fp = file_get_contents($url);
    if (!$fp)
        return null;

    $res = preg_match("/<title>(.*)<\/title>/siU", $fp, $title_matches);
    if (!$res)
        return null;

    // Clean up title: remove EOL's and excessive whitespace.
    $title = preg_replace('/\s+/', ' ', $title_matches[1]);
    $title = trim($title);
    return $title;
}