export for sql

[Guest]

is there a simple way to get orange to export the data to a file which can be uploaded to an sql database? (like a tool to run on the actual dbase files) or is it easiest to create a template to do this? I basically want a series of rows of data like this: (but including all the fields possible)

INSERT INTO xxx (id, artist, album, year, genre, live, format, bitrate, picture, genre, notes, length) VALUES('', '$artist', '$album', '$year', '$genre', '$live', '$format', '$bitrate', '$picture', '$genre', '$notes', '$length');

where $genre = rock etc.
Ill want one of each of these for each album - is there a template anyone has made to do this? i cant remember all the orange tags now, been such a long time since i played with this.

Cheers
-V15-

[Guest]

Start Page

<!---->
insert into music_albums (id,artist_name,album_name,album_time, format, bitrate, bootleg, genre, rare, soundtrack, rel_year, live, comment, trackcount, date_added) VALUES ("","<!--[artistsort/]-->", "<!--[title/]-->", "<!--[albumtime/]-->", "<!--[format/]-->", "<!--[bitrate/]-->", "<!--[bootleg/]-->", "<!--[genre/]-->", "<!--[rare/]-->", "<!--[soundtrack/]-->", "<!--[year/]-->", "<!--[live/]-->", "<!--[comment/]-->", "<!--[trackcount/]-->", "<!--[dateadded/]-->");
<!---->


Hi,

this is the code which im using so far, and it seems to be working for all the field which I think Ill need: the only exception is the code for the cover image.

If i include this code
<!----><!--[cover(170)/]--><!---->

then i get

<a href="9bbf8273.jpeg"><img src=""...> etc. which is nearly what im after, but what would be much more useful for me would be to have the output as simply "9bbf8273.jpeg" - then i can modify the php files in order to get the desired result.

can this be done directly with orange, or do i need to create some system of doing it?

thanks

-V15-

btw: lost my login details ages ago - i know i should reregister, but its easier to use guest mode, cos Firefox stores all the name email etc. info for me :)

[andrei_c]

Hi V15,

the cover file name exactly corresponds to the record ID, so can replace your code with


<!----><!--[cddbid/]-->.jpeg<!---->
and I think it will just work.

Andrei

[Guest]

Thanks for getting back to me Andrei,

Unfortunately I can't get this to work.

I'm using Daxedit, and the only code which I included was on the Start page:


<!---->


<!----><!--[cddbid/]-->.jpeg<!---->

<!---->

I get an error:

Unknown tag or section "cddbid"

Im guessing it doesnt like that its on the start page, inside the <!--[album]--> tags, but Im not sure how I can get it to work?

I could put it outside the <!--album--> tags, but then it doesnt output anything, or i can put it onto the album page - which isnt ideal, cos id end up with a load of pages to upload, whereas when its on the start page, i end up with a single page to upload in mysql, in order to get all the data uploaded.

Any ideas how i can get this?

Cheers again :)

-V15-

[andrei_c]

The cddbid tag has to be inside volume clause:


<!---->

<!---->
<!----><!--[cddbid/]-->.jpeg<!---->
<!---->

<!---->
That means, covers will be displayed separately for each item of multiple-item-sets.

Regards
Andrei

[Guest]

Thanks again Andrei,

When I do it this way, I inevitably end up with multiple entries for each album (where there is a 2cd set) which isnt quite what im after:
I could now change the sql code to "insert this row if one doesnt exist already" so that

2 Pac - All Eyez on Me - 9bbf8273.jpeg
2 Pac - All Eyez on Me - ef31f572.jpeg

gets inserted once - then I upload the covers which are all stored externally.

Or the other way is to use a "find and replace" in Word to change

<img class=cover width=??? height=??? src="covers/ to nothing and then the same for ">".

The second method is a tad more labour intensive (yes im a lazy bugger) but i think it might be easier to work.

Anyhow, Im gonna play with method 1 and see what I can make happen - will keep you posted on how its going, and ill post my final code so that others can use it if they do so wish :)

Cheers again mate

[Guest]

Wow, k...i finally got some time away from the new job to sort this out, and heres my result:


DAX file:
================
=====Start Page====
CREATE TABLE if not exists music_albums
(
id int(5),
artist_name varchar(100),
album_name varchar(100),
album_time varchar(10),
format varchar(10),
bitrate varchar(5),
bootleg char(3),
genre varchar(15),
rare char(3),
soundtrack char(3),
rel_year int(4),
live char(3),
comment longtext,
trackcount int(3),
date_added date,
cover varchar(100)
);

TRUNCATE TABLE `music_albums`;

<!---->
insert into music_albums (id, artist_name, album_name, album_time, format, bitrate, bootleg, genre, rare, soundtrack, rel_year, live, comment, trackcount, date_added, cover) VALUES ("", "<!--[artist/]-->", "<!--[title/]-->", "<!--[albumtime/]-->", "<!--[format/]-->", "<!--[bitrate/]-->", "<!--[bootleg/]-->", "<!--[genre/]-->", "<!--[rare/]-->", "<!--[soundtrack/]-->", "<!--[year/]-->", "<!--[live/]-->", "<!--[comment/]-->", "<!--[trackcount/]-->", "<!--[dateadded/]-->","<!----><!----><!--[cddbid/]-->.jpeg <!----><!---->");
<!---->

This creates the sql table (music_album) and then adds all of the data to it...the only exception is this cover problem, but theres a nice solution:
The field "cover" is 100chars long, and in it there are all of the album covers listed. When you come to use the row "cover", you need to replace the code

$row["cover"] with
substr($row["cover"], 0, 13) which extracts the first 13 chars from it - i.e. the actual cover to use and not any duplicates.

I think this was the easiest way, so it is just a case of exporting to html in orange, make the extension .txt and then use mysql to upload the text file as a query.

Its pretty simple to adapt the insert line to add fields, but i think i got all those which ill use for now.

Next step = to add a table of all the tracks...keep watching this space.

btw: check out www.ianrhodgson.co.uk/music for the story so far :)

[Guest]

www.ianrhodgson.co.uk/music

check out the site - ive now added the database entries for each album page - the tracklistings were a pain but ive managed (through a somewhat convaluted method) to get it all working - the only thing to sort out is where an artist/album has a strange char. like a "+" in it, as with Mike + the mechanics, which screws the whole lot up.

If you would like the script for exporting etc. then let me know here or via. my forum/contact page...though i will try to sort myself to post it sometime anyway (i dont often check hotmails)

Ian