| Author |
Order By a list? |
ranma
Member
Posts: 269
Location: Behind a sphere
Joined: 27.08.05
Rank: HBH Guru |
|
I am making a gallery scblockedript and need to retrieve image info.
Can you make an sql statement similar to:
SELECT * FROM photos WHERE collection='something' ORDER BY ID (4,6,2,7,3,5)
Here is my database structure:
photos - ID, name, descblockedription, collection (that the photo belongs to)
collections table - ID, name, order (the order of the images in it as a string with some delimiter between each image ID)
For the page where the user edits the collection I need to get the order of the images in the collection (which I do by selecting the specific collection from collections) and then get all the info for the images according to their ID.
As I mentioned above, what I want to do is select the images by collection and then order them by the array which PHP has created from the order string.
So if the array is {4,6,2,7,3,5}, then I need the photos selected to be order by ID in that order.
Help appreciated.
I was thinking some sort of view?
Wisdom spared is wisdom squared. |
|
| Author |
RE: Order By a list? |
stealth-
Member
Posts: 999
Location: Eh?
Joined: 10.04.09
Rank: God |
|
Unfortunately, I can't think of any way to do this with pure SQL. Someone else here might know. An alternate solution would be to use the SQL to pull all of the rows you need, and then create a PHP function called sortto(mysqlarray, sortby); that takes the mysqlarray and maps each ID into a new array in the order designated by the array sortby. While it's not a pure SQL solution, it would easily do the trick with just a few lines.
The irony of man's condition is that the deepest need is to be free of the anxiety of death and annihilation; but it is life itself which awakens it, and so we must shrink from being fully alive.
http://www.stealth-x.com |
|
| Author |
RE: Order By a list? |
ranma
Member
Posts: 269
Location: Behind a sphere
Joined: 27.08.05
Rank: HBH Guru |
|
I am thinking of doing that right now. Thanks.
I was also sort of thinking if there is a better way to store the order. I was thinking of having each photo in the photo table have the order in a column, but updating the order would require having a query for each photo, and if the photos go into the hundreds, the program becomes not scalable.
Wisdom spared is wisdom squared. |
|
|
| Author |
RE: Order By a list? |
spyware
Member
Posts: 4190
Location: The Netherlands
Joined: 14.04.07
Rank: God
Warn Level: 90
|
|
You'll have to create a third table that links the image ID and the place it has in the list. How are you storing your pictures btw? base64?
"The chowner of property." - Zeph Widespread intellectual and moral docility may be convenient for leaders in the short term,
but it is suicidal for nations in the long term. - Carl Sagan Since the grid is inescapable, what were the earlier lasers about? Does the corridor have a sense of humor? - Ebert |
|
|
| Author |
RE: Order By a list? |
ranma
Member
Posts: 269
Location: Behind a sphere
Joined: 27.08.05
Rank: HBH Guru |
|
But even if I make that third table, it would need to update all the photos in a specific collection once the order is changed? Wouldn't that require hundreds of queries if there are hundreds of photos?
Also, I store the photos as files, if that's what you're asking. Should I be doing it otherwise?
Wisdom spared is wisdom squared. |
|
|
| Author |
RE: Order By a list? |
spyware
Member
Posts: 4190
Location: The Netherlands
Joined: 14.04.07
Rank: God
Warn Level: 90
|
|
|
ranma wrote:
But even if I make that third table, it would need to update all the photos in a specific collection once the order is changed? Wouldn't that require hundreds of queries if there are hundreds of photos?
What?
"The chowner of property." - Zeph Widespread intellectual and moral docility may be convenient for leaders in the short term,
but it is suicidal for nations in the long term. - Carl Sagan Since the grid is inescapable, what were the earlier lasers about? Does the corridor have a sense of humor? - Ebert |
|
|
| Author |
RE: Order By a list? |
ranma
Member
Posts: 269
Location: Behind a sphere
Joined: 27.08.05
Rank: HBH Guru |
|
For example, you have this table
ID | Name | Order
___________________
1 | cat | 3
2 | tree | 2
3 | everest | 1
To update the order, you would need a separate query for each item to change its order to the new value.
EDIT:
I found this, which might work, but makes for long queries:
$array = array(3,5,4,2,1);
$list = implode(',', $array);
$orderbylist = 'ORDER BY '; // initialize
foreach ($array as $id) {
$orderbylist .= "id=$id DESC, ";
}
$orderbylist = rtrim($orderbylist, ', ' ;
$sql = "SELECT * FROM table WHERE id IN ($list) $orderbylist";
Wisdom spared is wisdom squared.
Edited by ranma on 05-07-11 16:50 |
|
|
| Author |
RE: Order By a list? |
ranma
Member
Posts: 269
Location: Behind a sphere
Joined: 27.08.05
Rank: HBH Guru |
|
EDIT 2:
Found a solution:
I can use mysql_data_seek() to go to the correct ID.
But still, I'd like to ask why keep the image in a DB and not in the file system.
Wisdom spared is wisdom squared. |
|
|
| Author |
RE: Order By a list? |
stealth-
Member
Posts: 999
Location: Eh?
Joined: 10.04.09
Rank: God |
|
|
ranma wrote:
EDIT 2:
Found a solution:
I can use mysql_data_seek() to go to the correct ID.
But still, I'd like to ask why keep the image in a DB and not in the file system.
Unless Spyware is aware of something regarding this that I am not, I don't necessarily think there is a benefit to storing in the DB. It's actually a huge performance hit on both the DB and the server instead, as compared to direct filesystem access. On top of requiring additional coding, it can get messy when/if you encounter scalability issues. I really think the best method is to simply store file paths in the DB instead of the actual images.
The irony of man's condition is that the deepest need is to be free of the anxiety of death and annihilation; but it is life itself which awakens it, and so we must shrink from being fully alive.
http://www.stealth-x.com |
|
|
| Author |
RE: Order By a list? |
spyware
Member
Posts: 4190
Location: The Netherlands
Joined: 14.04.07
Rank: God
Warn Level: 90
|
|
It's almost never a good idea to store the images in the database as base64, I was just asking.
"The chowner of property." - Zeph Widespread intellectual and moral docility may be convenient for leaders in the short term,
but it is suicidal for nations in the long term. - Carl Sagan Since the grid is inescapable, what were the earlier lasers about? Does the corridor have a sense of humor? - Ebert
Edited by spyware on 11-07-11 23:30 |
|
|
| Author |
RE: Order By a list? |
ranma
Member
Posts: 269
Location: Behind a sphere
Joined: 27.08.05
Rank: HBH Guru |
|
Whew, ok, then, thanks 
Wisdom spared is wisdom squared. |
|
|
Main:
