XOOPS 
Forum
Forum Index General Forums Community Support Modules Support Themes Support Development International Support Module Hack Reviews Theme Designer Talk
  1. Board index / 
  2. Xoops Development / 
  3. Xoops Development 
  4.  / General problem with SQL-query
Register
1
frankblack
General problem with SQL-query

  • 2008/7/22 14:57

  • frankblack

  • Just can't stay away

  • Posts: 830

  • Since: 2005/6/13


mySQL has a lot of nice features, but one "bad" thing (amongst others) is, that the results will be given sorted, even if you do NOT declare an order.

$query = "SELECT xfid, title, artist FROM ".$xoopsDB->prefix('debaser_files')." WHERE xfid IN (".implode(', ', array_map('intval', $playarray)).")";


This was my first shot and surprisingly while I printed out the results, the results were ordered. Instead of 3 2 1 it was 1 2 3. Not what I wanted! I wanted the exact order where it was in the database.

Second shot gave me finally what I wanted:

$query = "SELECT xfid, title, artist FROM ".$xoopsDB->prefix('debaser_files')." WHERE xfid IN (".implode(', ', array_map('intval', $playarray)).") ORDER BY FIND_IN_SET(xfid, '$kack')";


$kack is representing a comma-separated string of $playarray.

The solution was hiding deep on the web and MAYBE there is another solution?

Hints are appreciated!
2
ghia
Re: General problem with SQL-query

  • 2008/7/30 21:01

  • ghia

  • Community Support Member

  • Posts: 4953

  • Since: 2008/7/3 1


Quote:
This was my first shot and surprisingly while I printed out the results, the results were ordered. Instead of 3 2 1 it was 1 2 3. Not what I wanted! I wanted the exact order where it was in the database.
Since xfid is the auto increment field, 1 2 3 is the correct database order. If you want it backwards, specify
order by xfid desc
3
frankblack
Re: General problem with SQL-query

  • 2008/7/30 21:52

  • frankblack

  • Just can't stay away

  • Posts: 830

  • Since: 2005/6/13


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
4
ghia
Re: General problem with SQL-query

  • 2008/7/31 9:26

  • ghia

  • Community Support Member

  • Posts: 4953

  • Since: 2008/7/3 1


Quote:
The exact order has to be 2 3 1
Why do you want this arbitrary order?
5
frankblack
Re: General problem with SQL-query

  • 2008/7/31 10:39

  • frankblack

  • Just can't stay away

  • Posts: 830

  • Since: 2005/6/13


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.
6
trabis
Re: General problem with SQL-query

  • 2008/7/31 12:53

  • trabis

  • Core Developer

  • Posts: 2269

  • Since: 2006/9/1 1


I cannot see ithe entire picture($playarray structure) but take this example I made for you, I think it would work:

$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!
7
ghia
Re: General problem with SQL-query

  • 2008/7/31 14:34

  • ghia

  • Community Support Member

  • Posts: 4953

  • Since: 2008/7/3 1


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.
I understand you have a playlist table where an order is defined and you want your query to follow this order. What is the structure for this playlist file (see .sql)?
Normally you can do a join for this:
"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;"

Login

Register now!  |  Lost Password?

Search

Advanced Search

Recent Posts

Who's Online

416 user(s) are online (307 user(s) are browsing Support Forums)

Members: 0

Guests: 416

more...

Donat-O-Meter

Stats
Goal: $100.00
Due Date: Nov 30
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $100.00
Make donations with PayPal!

Latest GitHub Commits