Extracting HREF values from SQL

[suklee]

I've got a single mysql table of content (~ 1600 rows). Am updating the site to a new structure so all of the internal links need to be updated. How can I extract all the href="xxx" values into a csv or another table?

PS. the same .sql can also be viewed from a PHP-powered website but I assume it'd be easier to deal with just one file rather than all the pages of a site.

 

[beginner99]

you need to explain further. Your post is not really understandable...

(beside the fact that storing links is IMHO a rather suboptimal solution)

 

[suklee]

Oops, let me try again. It's Joomla CMS's jos_content table. In one of this table's columns is where all of the articles' HTML reside, and I want to get the links in between the quotes:

<a href="......">

 

[KIAman]

Oops, let me try again. It's Joomla CMS's jos_content table. In one of this table's columns is where all of the articles' HTML reside, and I want to get the links in between the quotes:

<a href="......">

Do this in code instead of SQL.

If you must use SQL to get the links, then

1. Get the position of the first quote (use function like charindex)
2. Get the position of the second quote
3. Subtract 2nd from 1st to get length (use math)
4. Use substring (or substr) function

 

[suklee]

I see, so I should use PHP and parse (regex) the data that way?

 

[Cogman]

I see, so I should use PHP and parse (regex) the data that way?

Use an xml library to pull out the data you want ( http://php.net/manual/en/book.xml.php ) javascript even supports some good xml manipulation.
http://www.w3schools.com/Xml/xml_parser.asp

Either way, you won't want to use regex's for html manipulation. It is painful at best.

 

[jvroig]

Why even bother with xml libraries? If all he wants are the hrefs, he can just query the columns, and for each record retrieved, use substr() to find all "href=" within the text then the next nearest doublequote, and that's it.

From there, he can store them in an array for printing later to a file, or fwrite them directly, or echo them to the screen in a table which he can just ctrl+a then copy-paste to a spreadsheet, whatever. If he even wants to update them already, he can do so in the body of the loop, as long as he also retrieves the primary keys along with the content column.

Unless he starts wanting something fancy (he doesn't seem to from the opening post), the throw-away code for this will be simple and rather straightforward.