이미지 URL 주소 변경 소소한 팁

https://sir.kr/g5_plugin/2739

위 플러그인 사용 시 더 편하게 사용하기
img_url.php
[code]

<?php
$host = G5_MYSQL_HOST;
$db = G5_MYSQL_DB;
$user = G5_MYSQL_USER;
$pass = G5_MYSQL_PASSWORD;
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

// 1. 데이터베이스 내의 모든 테이블을 찾습니다.
$stmt = $pdo->query("
    SELECT table_name 
    FROM information_schema.columns 
    WHERE column_name = 'wr_content' 
    AND table_schema = DATABASE()
");

$tables = $stmt->fetchAll(PDO::FETCH_COLUMN);

if (empty($tables)) {
    echo "No tables found with 'wr_content' column.";
    exit;
}

// 2. 각 테이블의 wr_content 열에서 URL을 추출하는 동적 SQL 쿼리를 생성합니다.
$sqlPartsData = [];
$sqlPartsNoData = [];

foreach ($tables as $table) {
    $sqlPartsData[] = "
        SELECT 
            '$table' AS table_name,
            CASE 
                WHEN wr_content REGEXP 'https://[^ ]+/data/' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(wr_content, 'https://', -1), ' ', 1)
                WHEN wr_content REGEXP 'http://[^ ]+/data/' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(wr_content, 'http://', -1), ' ', 1)
            END AS url,
            CASE 
                WHEN wr_content REGEXP 'https://[^ ]+/data/' THEN 'https://'
                WHEN wr_content REGEXP 'http://[^ ]+/data/' THEN 'http://'
            END AS protocol
        FROM $table 
        WHERE wr_content REGEXP 'https?://[a-zA-Z0-9./?=_-]+/data/'
    ";

    $sqlPartsNoData[] = "
        SELECT 
            '$table' AS table_name,
            CASE 
                WHEN wr_content REGEXP 'https://' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(wr_content, 'https://', -1), ' ', 1)
                WHEN wr_content REGEXP 'http://' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(wr_content, 'http://', -1), ' ', 1)
            END AS url,
            CASE 
                WHEN wr_content REGEXP 'https://' THEN 'https://'
                WHEN wr_content REGEXP 'http://' THEN 'http://'
            END AS protocol
        FROM $table 
        WHERE wr_content REGEXP 'https?://[a-zA-Z0-9./?=_-]+' AND wr_content NOT REGEXP '/data/'
    ";
}

$sqlData = implode(' UNION ALL ', $sqlPartsData);
$sqlNoData = implode(' UNION ALL ', $sqlPartsNoData);

// 3. 추출한 URL을 결과로 표시합니다.
$stmtData = $pdo->query($sqlData);
$resultsData = $stmtData->fetchAll();

$stmtNoData = $pdo->query($sqlNoData);
$resultsNoData = $stmtNoData->fetchAll();

$G5_URL = G5_URL;  // G5_URL 문자열을 지정합니다.

echo "<h2 style='color:#fa0'>URLs containing /data/</h2>";
if ($resultsData) {
    foreach ($resultsData as $row) {
        $full_url = $row['protocol'] . $row['url'];
        if (strpos($full_url, $G5_URL) === false) {  // G5_URL이 포함되지 않은 경우만 출력
            echo "<span style='color:#fff'>" . htmlspecialchars($row['table_name']) . "</span><br>";
            echo "<span style='color:#fff'>" . htmlspecialchars($full_url) . "</span><br><br>";
        } else {
            echo "<span style='color:#aaa'>" . htmlspecialchars($row['table_name']) . "</span><br>";
            echo "<span style='color:#aaa'>" . htmlspecialchars($full_url) . "</span><br><br>";
        }
    }
}

echo "<h2 style='color:#f00'>URLs not containing /data/</h2>";
if ($resultsNoData) {
    foreach ($resultsNoData as $row) {
        $full_url = $row['protocol'] . $row['url'];
        if (strpos($full_url, $G5_URL) === false) {  // G5_URL이 포함되지 않은 경우만 출력
            echo "<span style='color:#fff'>" . htmlspecialchars($row['table_name']) . "</span><br>";
            echo "<span style='color:#fff'>" . htmlspecialchars($full_url) . "</span><br><br>";
        } else {
            echo "<span style='color:#aaa'>" . htmlspecialchars($row['table_name']) . "</span><br>";
            echo "<span style='color:#aaa'>" . htmlspecialchars($full_url) . "</span><br><br>";
        }
    }
}

include_once('../admin.tail.php');

[/code]

in_url.php

[code]

<?php
$host = G5_MYSQL_HOST;
$db = G5_MYSQL_DB;
$user = G5_MYSQL_USER;
$pass = G5_MYSQL_PASSWORD;
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

// 1. 데이터베이스 내의 모든 테이블을 찾습니다.
$stmt = $pdo->query("
    SELECT table_name 
    FROM information_schema.columns 
    WHERE column_name = 'wr_content' 
    AND table_schema = DATABASE()
");

$tables = $stmt->fetchAll(PDO::FETCH_COLUMN);

if (empty($tables)) {
    echo "No tables found with 'wr_content' column.";
    exit;
}

// 2. 각 테이블의 wr_content 열에서 URL을 추출하는 동적 SQL 쿼리를 생성합니다.
$sqlPartsData = [];
$sqlPartsNoData = [];

foreach ($tables as $table) {
    $sqlPartsData[] = "
        SELECT 
            '$table' AS table_name,
            CASE 
                WHEN wr_content REGEXP 'https://[^ ]+/data/' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(wr_content, 'https://', -1), ' ', 1)
                WHEN wr_content REGEXP 'http://[^ ]+/data/' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(wr_content, 'http://', -1), ' ', 1)
            END AS url,
            CASE 
                WHEN wr_content REGEXP 'https://[^ ]+/data/' THEN 'https://'
                WHEN wr_content REGEXP 'http://[^ ]+/data/' THEN 'http://'
            END AS protocol
        FROM $table 
        WHERE wr_content REGEXP 'https?://[a-zA-Z0-9./?=_-]+/data/'
    ";

    $sqlPartsNoData[] = "
        SELECT 
            '$table' AS table_name,
            CASE 
                WHEN wr_content REGEXP 'https://' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(wr_content, 'https://', -1), ' ', 1)
                WHEN wr_content REGEXP 'http://' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(wr_content, 'http://', -1), ' ', 1)
            END AS url,
            CASE 
                WHEN wr_content REGEXP 'https://' THEN 'https://'
                WHEN wr_content REGEXP 'http://' THEN 'http://'
            END AS protocol
        FROM $table 
        WHERE wr_content REGEXP 'https?://[a-zA-Z0-9./?=_-]+' AND wr_content NOT REGEXP '/data/'
    ";
}

$sqlData = implode(' UNION ALL ', $sqlPartsData);
$sqlNoData = implode(' UNION ALL ', $sqlPartsNoData);

// 3. 추출한 URL을 결과로 표시합니다.
$stmtData = $pdo->query($sqlData);
$resultsData = $stmtData->fetchAll();

$stmtNoData = $pdo->query($sqlNoData);
$resultsNoData = $stmtNoData->fetchAll();

$G5_URL = G5_URL;
if ($resultsData) {
    foreach ($resultsData as $row) {
        $full_url = $row['protocol'] . $row['url'];
        if (strpos($full_url, $G5_URL) === false) {
           $string = htmlspecialchars($full_url);
           $pattern = '/(https?:\/\/.*?)\/data/';
           preg_match($pattern, $string, $matches);

           if (isset($matches[1])) {
               $url = $matches[1];
               echo $url;
               break;
           }
        }
    }
}

[/code]
img_rename.php

[code]

<?php
$sub_menu = "999100";
include_once('./_commonf.php');

auth_check($auth[$sub_menu], 'r');

if ($is_admin != 'super')
    alert('최고관리자만 접근 가능합니다.');

$g5['title'] = '이미지 주소 변경';
include_once('../admin.head.php');

$pg_anchor = '<ul class="anchor">
    <li><a href="#img_rename">이미지 주소 변경</a></li>
</ul>';

$frm_submit = '<div class="btn_confirm01 btn_confirm">
    <input type="submit" value="확인" class="btn_submit" accesskey="s">
    <a href="'.G5_URL.'/">메인으로</a>
</div>';
?>

<div class="local_desc01 local_desc">
    <p>
        이미지 주소 변경 시 게시판, 내용관리에 등록된 이미지의 주소가 현재 사이트 주소로 변경됩니다.
        <br><strong>그누보드5 , 영카드5</strong> 에서 사용 가능합니다.
    </p>
</div>

<form name="fconfigform" id="fconfigform" method="post" onsubmit="return fconfigform_submit(this);" enctype="MULTIPART/FORM-DATA">
<input type="hidden" name="token" value="" id="token">

<section id="img_rename">
    <div class="tbl_frm01 tbl_wrap">
       <table>
        <caption>이미지주소변경</caption>
        <colgroup>
            <col class="grid_4">
            <col>
            <col class="grid_4">
            <col>
        </colgroup>
        <tbody>
        <tr><lii id="btn_2" class="btn_03 btn" style="cursor:pointer;padding:5px">TABLES URL 보기</lii>
            <th scope="row"><label for="previous_site">이전 사이트 주소<strong class="sound_only">필수</strong></label></th>
            <td>
                <?php echo help('이전 사이트의 이미지 주소를 정확하게 입력해주세요.');?>
                <?php echo help('ex) http://test.co.kr') ?>
                <input type="text" name="previous_site" value="<?php include_once('./in_url.php');?>" id="previous_site" class="frm_input required" size="30" required>
            </td>
            <th scope="row"><label for="now_site">현재 사이트 주소<strong class="sound_only">필수</strong></label></th>
            <td>
                <?php echo help('수정불가.') ?>
                <input type="text" name="now_site" value="<?php echo G5_URL ?>" id="now_site" class="frm_input" size="30" readonly>
            </td>
        </tr>
        </tbody>
        </table>
    </div>
</section>
<?php echo $frm_submit; ?>

</form>
<div id="modal_2" style="position:relative;display:none;width:100%;height:auto;background-color:blue;color:white;padding:10px;box-sizing:border-box">
<?php include_once('./img_url.php');?>
</div>
<script>
function fconfigform_submit(f)
{
    f.action = "./img_rename_update.php";
    return true;
}

document.getElementById("btn_2").onclick = function() {
    var modal = document.getElementById("modal_2");
    var button = document.getElementById("btn_2");
    if (modal.style.display === "none" || modal.style.display === "") {
        modal.style.display = "block";
        button.textContent = "TABLES URL 닫기";
    } else {
        modal.style.display = "none";
        button.textContent = "TABLES URL 보기";
    }
};
</script>
<?php
include_once('../admin.tail.php');

[/code]