从MYSQL数据列中提取URL有效或无效

Question

I have a column within my MYSQL database headline TEXT NOT NULL I would like to go through this column for each of my records and extract the URL if it has one. The URL will be stored in another column article_url VARCHAR (225) NULL. If there is no URL then it would just put NULL in the column.

Lastly, I would like this to update each time a new record is inserted into the database. Below is what I have thus far.

UPDATE: here is an example of the headline column of my data Drastic decline in Social Sciences intake *: http:// bit.ly/2vXzPhQ pic.twitter.com/PAZvG3tX17 as you can see there is a URL in the data set however the URL is broken, all URLs are broken with a space between http:// and the rest of the URL. So I need to get a function in PHP that can find the Valid or Invalid URL when it gets the Invalid URL it will fix it.

this is what I have thus far.

$result = $conn->query($sql);

$reg_exp = "/^(http(s?): \/\/)?(www\.)+[a-zA-Z0-9\.\-\_]+(\.[a-zA-Z]{2,3})+(\/[a-zA-Z0-9\_\-\s\.\/\?\%\#\&\=]*)?$/";

if ($result->num_rows > 0) 
{
    // output data of each row
    while($row = $result->fetch_assoc()) 
    {
        echo "id: " . $row["id"]. " headline: " . $row["headline"]. "<br>";
        if(preg_match($reg_exp, $row["headline"]) == TRUE)
        {
            echo "URL is valid format";
        }
        else
        {
            echo "URL is invalid format";
        }
    }
} 
else 
{
    echo "0 results";
}

Answer 1

For a MySQL only solution you have four parts to parts solve:

1. one to run as update on existing data entries

2. one to run as AFTER INSERT Trigger

3. For both 1+2 you need the correct REGEX-Syntax of MySQL. The php-regex might be a good starting point to do a SELECT headline, Headline REGEXP '(insert your regex here)' FROM table;. with that SELECT you can iterative check on your database, if your regex is correct.

4. In this answer you find a version of regexp_replace, which you can add to your database. This can be used in the UPDATE table SET artice_url = IF(...REGEXP...) regexp_replace(headline,...) (the ... needs also to be filled correctly) as well as in the Trigger.