Hi Gaev, I have provided some answers to your questions further down in my post, as always thanks for your input and guidance.
Hi stu, thanks for such a detailed response. However I don't understand something. You say to create a view, but I am not clear on this. What do I create a view of? Do you mean (in NeoBook DBPro speak) to "dbpShowGrid" as I can't find a command that says "CreateView". Do I need to create a new table for the view?, if so, do I create the fields as strings to display the data or as integers to hold the lookup value? If I don't use the NeoBook DBPro "dbpShowGrid" then how do I go about doing it?
Please forgive my ignorance or stupidity, I am certainly not an expert and know just a little about databases and even less about sql.
I have a table named 'songs'
- Code: Select all
--------------------------------------------------------------------------------
| ID | songtitle | artist | genre | songfile |
--------------------------------------------------------------------------------
| 1 | 3 | 4 | 4 |\\192.168.0.1\Music\song1.mp3 |
| 2 | 1 | 2 | 3 |C:\Users\Temp\Music\song2.mp3 |
| 3 | 1 | 3 | 3 |\\192.168.0.1\Music\song3.mp3 |
| 4 | 4 | 2 | 4 |C:\Users\Temp\Music\song2.mp3 |
| 5 | 2 | 3 | 2 |\\192.168.0.1\Music\song5.mp3 |
| 6 | 1 | 4 | 3 |\\192.168.0.1\Music\song6.mp3 |
--------------------------------------------------------------------------------
Then I have multiple tables that are similar to the following (each has only an ID and a string field. The ID in each table is the primary key, auto-increment, and unique).
An example of the 'songtitle' table
- Code: Select all
--------------------------------
| ID | songtitle |
--------------------------------
| 1 | hot in here |
| 2 | there she goes |
| 3 | shake it up |
--------------------------------
An example of the 'Artist' table
- Code: Select all
--------------------------------
| ID | artist |
--------------------------------
| 1 | marky tom |
| 2 | the deadly cats |
| 3 | Roxanne Clint |
--------------------------------
The database file is located on the local drive, however the music files can be located somewhere on the local network (the network location is not relevant as the file is only read when it get loaded to be played. The data to be displayed always comes from the database file located on the local hard drive.
The time it takes is 1 to 2 seconds. The actual query to show a specific CD is very fast, it is the looping through the quantity of tracks on the CD and populating the temporary table that takes the time.
Example actual code below;
- Code: Select all
Loop "1" "[music.songs.$RecCount]" "[lpy]"
dbpGotoRecord "music" "songs" "[lpy]"
dbpAddRecord "music" "temptable"
dbpFind "music" "cdname" "ID" "[music.songs.cdname]" "ExactMatch=No;CaseSensitive=No"
SetVar "[music.temptable.cdname]" "[music.cdname.cdname]"
dbpFind "music" "songtitle" "ID" "[music.songs.songtitle]" "ExactMatch=No;CaseSensitive=No"
SetVar "[music.temptable.songtitle]" "[music.songtitle.songtitle]"
dbpFind "music" "artists" "ID" "[music.songs.artist]" "ExactMatch=No;CaseSensitive=No"
SetVar "[music.temptable.artist]" "[music.artists.artist]"
dbpFind "music" "genre" "ID" "[music.songs.genre]" "ExactMatch=No;CaseSensitive=No"
SetVar "[music.temptable.genre]" "[music.genre.genre]"
dbpFind "music" "yearreleased" "ID" "[music.songs.yearreleased]" "ExactMatch=No;CaseSensitive=No"
SetVar "[music.temptable.yearreleased]" "[music.yearreleased.yearreleased]"
dbpFind "music" "copyright" "ID" "[music.songs.copyright]" "ExactMatch=No;CaseSensitive=No"
SetVar "[music.temptable.copyright]" "[music.copyright.copyright]"
dbpFind "music" "tracktype" "ID" "[music.songs.tracktype]" "ExactMatch=No;CaseSensitive=No"
SetVar "[music.temptable.tracktype]" "[music.tracktype.tracktype]"
SetVar "[music.temptable.filesong]" "[music.songs.filesong]"
SetVar "[music.temptable.filecover]" "[music.songs.filecover]"
EndLoop
I really do appreciate all your help (stu and Gaev) it is invaluable and helps me learn. I have spent several hours trying to find a solution with Google but haven't succeeded. This could be because I am not sure how to ask the right question (search term) or the fact that I know what I want to do, just not how to ask the question.
Thanks again.