In MySQL, how do I extract a URL from a long text string?
I am learning MySQL and using phpMyAdmin to make changes to my Wordpress database for a store with thousands of products.
I am trying to do a quick update when I pull the url and paste it into the shortcode I want to add.
Currently every record in my table has this somewhere inside a bunch of other text.
<input type="hidden" name="prtks" value="http://domainname/folder/filename.mp3"/>
I want to keep this and add to every record within the same field
[sc_embed_player_template1 fileurl="url from above"/"]
This is quite tricky as I know I can find the substring by counting characters, but is there a way to pull the full url from http to the last .mp3?
Thank you so much!
source to share
This is not in MySQL, but if I understand your question correctly, it seems to you that you are talking about it in a php file by doing something like the following:
<?php
$url = '<input type="hidden" name="prtks" value="http://domainname/folder/filename.mp3"/>';
$parts = explode('"', $url);
echo 'URL: '.$parts[5];
?>
This uses php explode()
to split the string into an array based on "characters".
Then you can just respond to the 6th part ( $parts[5]
)
source to share
Why not just use a simple regex to fetch the URL?
$str = '<input type="hidden" name="prtks" value="http://domainname/folder/filename.mp3"/>';
preg_match_all('/\b(?:(?:https?|ftp|file):\/\/|www\.|ftp\.)[-A-Z0-9+&@#\/%=~_|$?!:,.]*[A-Z0-9+&@#\/%=~_|$]/i', $str, $match, PREG_PATTERN_ORDER);
var_dump($match[0][0]);
// string 'http://domainname/folder/filename.mp3' (length=37)
source to share
<?php
function urltxt($string)
{
$regex = "/\b(?:(?:https?|ftp):\/\/|www\.)[-a-z0-9+&@#\/%?=~_|!:,.;]*[-a-z0-9+&@#\/%=~_|]/i"; ///(http|https|ftp|ftps)\:\/\/[a-zA-Z0-9\-\.]+\.[a-zA-Z]{2,3}(\/\S*)?/";
preg_match_all($regex, $string, $matches);
return $matches[0];
}
$url = urltxt($sqlresult);
echo $url[0];
//this way its more easier i guess
?>
source to share
If the file extensions are all three characters, you can use a regular expression, for example http://*\.{3}
, to find the URL, but if they don't have some sort of delimiter it would be hard to find. you can try http://*\.(mp3|html|gif|png|exe|php|aif|wav)
and list all possible extensions if they are different. You can also just try to list the extensions with more than three characters: http://*\.({3}|html|jpeg|aiff|torrent)
.
source to share