SQL Select Query - Array Help

[stuprniq]

The Table is named ItemTable. The columns are "key" and "value".

Row: "feeds" has the following information:

[{"title":"Maddow Blog","url":"

http://maddowblog.msnbc.msn.com/_feeds/rss2/author","maxitems":"100","order":"1"}

]

What I'm trying to do is construct a query that either gets me to:

Title URL Max Items Order
Maddow Blog

http://maddowblog.msnbc.msn.com/_feeds/rss2/author

100 1
All Things Considered

http://www.npr.org/rss/rss.php?id=2

100 2

or

URL

http://maddowblog.msnbc.msn.com/_feeds/rss2/author

http://www.npr.org/rss/rss.php?id=2

Either would work for me.

I'm new to SQL programming. Currently using Firefox Sqlite Manager addon.

Best I can figure, it's an array, but, beyond that to structuring the appropriate query - I'm just guessing, and nothing's working.

Anyone?

Thanx

 

[stuprniq]

Clarification:

"key" - "feeds"

has the "value" stated.

 

[vacunita]

MYSQL lacks the complex string manipulation that this would require, which makes it very complex to split strings like above to get specific values.

This should be better done on your front end, as any programming language you may be using to connect to mysql has better and more robust string handling functions. Or you might want to reconsider your table structure if its still possible.

However if you must do this in MYSQL, then you'l have to make use of the SUBSTRING_INDEX function. Note though that with many records this can be very very slow.

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(thevalue,",",2),",",-1),":",-2) AS URL, SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(thevalue,",",3),",",-1),":",-1) AS maxitems FROM itemtable;

This should return the URL part of the string, and the maxitems part as 2 columns. You would need to do similar things for the other values you wish to extract. Which as you can see will get very complex very fast.

SUBSTRING_INDEX usage:

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index

BTW both "key" and "value" are reserved words in mysql so if at all possible I strongly suggest you change those. Otherwise you'll have to make sure to surround them with back ticks in your queries.



----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Behind the Web, Tips and Tricks for Web Development.

http://behindtheweb.blogspot.com/

 

[stuprniq]

Thanx Phil,

I didn't write the code. It comes from the Google Chrome Browser extension,Slick RSS.

https://chrome.google.com/extensions/detail/ealjoljnibpdkocmldliaoojpgdkcdob

Currently there is no way to export feeds from the extension to OPML. After that, it then becomes a challenge achieving portability. Where the data is stored is typed as a Sqlite3 database. That's where this journey began. Through Firefox, I was able to export the "feeds" data and through some transpose and parse gymnastics as well as discovering the function "hyperlink", I can achieve portability, but, as I mentioned in a review of the extension, this is WAY too much effort to have to go through for what ought to be a built in function.

I also understand, it's free, and can't complain too much for the price.

And there's nothing else, that I know of, for Google Chrome that comes close to comparable functionality. So, ingenuity and innovation kick in.

Or, what have you?

Thanx

 

[vacunita]

Sorry, I don't do RSS feeds at all so can;t help there.

As I said if it was possible to modify, it would be a god thing. Otherwise, it can be done n MYSQL as shown, though don't expect it to be very optimal.

now this relies on the data in th value column to always have the same format. As it relies on the locations of the data rather than their names. If the string changes, you'll have to restructure the query accordingly.





----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Behind the Web, Tips and Tricks for Web Development.

http://behindtheweb.blogspot.com/