| Re: General problem with SQL-query |
| by ghia on 2008/7/31 14:34:16 Quote: The database has one table for holding the file information and there is another table holding user specific information like playlist, flashupload etc. For displaying the playlist in the specific order I have to query the database, but I don't want to loop database queries for every single file, so I came up with this code. ATM it take me two queries for getting all file information from the user specific playlist, which should be enough. Normally you can do a join for this: le="color: #000000"><?php "SELECT p.xfid, d.title, d.artist FROM ".$xoopsDB->prefix('debaser_playlist')." p LEFT JOIN ".$xoopsDB->prefix('debaser_files')." d ON p.xfid = d.xfid WHERE p.user = userid and p.playlist = playlistid ORDER BY p.orderfld;"
|
| Re: General problem with SQL-query |
| by trabis on 2008/7/31 12:53:46 I cannot see ithe entire picture($playarray structure) but take this example I made for you, I think it would work: le="color: #000000"><?php $playarray = (15, 30, 2, 536, 12); $sorted = array(); $ret = array(); $query = "SELECT xfid, title, artist FROM ".$xoopsDB->prefix('debaser_files')." WHERE xfid IN (".implode(', ', $playarray).")"; $result = $xoopsDB->query($sql); while ( $myrow = $xoopsDB->fetchArray($result) ) { $ret[$myrow['xfid']]['xfid'] = $myrow['xfid']; $ret[$myrow['xfid']]['title'] = $myrow['title']; $ret[$myrow['xfid']]['artist'] = $myrow['artist']; } foreach ($playarray as $playid) $sorted[] = $ret[$playid]; } // Sorted should have it now!
|
| Re: General problem with SQL-query |
| by frankblack on 2008/7/31 10:39:15 This order is not arbitrary. So I explain this now detailed. Step 1: The user adds files to a playlist Step 2: Perhaps the user wants to sort his playlist, so he/she changes the order Step 3: Playlist will be saved. User adds other files to the playlist and want to sort them again The database has one table for holding the file information and there is another table holding user specific information like playlist, flashupload etc. For displaying the playlist in the specific order I have to query the database, but I don't want to loop database queries for every single file, so I came up with this code. ATM it take me two queries for getting all file information from the user specific playlist, which should be enough. I hope this is more clear? Or maybe I am not able to see a better solution right now. |
| Re: General problem with SQL-query |
| by ghia on 2008/7/31 9:26:47 Quote: The exact order has to be 2 3 1 |
| Re: General problem with SQL-query |
| by frankblack on 2008/7/30 21:52:23 unfortunately the example of the order did not describe the problem properly. The exact order has to be 2 3 1, instead the query delivers 1 2 3. So this was my initial problem. thx anyway for posting |
Search
Recent Posts
Who's Online
Donat-O-Meter
| Stats | |
| Goal: | $15.00 |
| Due Date: | Jul 31 |
| Gross Amount: | $0.00 |
| Net Balance: | $0.00 |
| Left to go: | $15.00 |
|
|