I have a MySQL field which stores an array with 3 values:
array(item1=>1123, item2=>5454, item3=>23432)
How can I query the database with PHP so that I get only distinct values of item2 and then arrange those results by the values of item3?
-
The short answer is: You can't. Not if you are going to try to query it from the DB. The only possible way is to do this programatically. For educational purposes however, can you explain why you would store this as an array instead of putting each item in it's own field? Clearly you need them in their own fields if you need to select by DISTINCT. But I am curious as to the design path chosen.Chuck Burgess– Chuck Burgess2010-03-08 23:32:00 +00:00Commented Mar 8, 2010 at 23:32
-
Basically it is a user's account with 10 tasks. Each task has a row in the "users" table. The array store the name of the task, when the user started the task and when they finished the task. I assumed this would be the best way to approach this scenario versus having 30 fields or another table.ssergei– ssergei2010-03-09 18:44:38 +00:00Commented Mar 9, 2010 at 18:44
Add a comment
|
2 Answers
A lot more information is needed - like, how your database is structured.
Look into the DISTINCT() function and the ORDER BY clause of SQL
Comments
It much easier to store your array into text and something you can separate later. Using php you can do this. I'll try to work with your data here into something you can use. says you have the field items. If instead you had an array such as.
$items = array(1123,5454,23432);
You can then implode it with a symbol such as:
$items = implode('|',$items);
and then store this in the database under a fields such as items that would look like this:
1123|5454|23432
Then when you want to grab the data you just need to explode it with the same symbol:
$items = explode('|',$row['items']);
and you are back with your dataset:
$items[0] = 1123 $items[1] = 5454 $items[2] = 23432
and then can access the second item by grabbing element one of the array:
$items[1]
Hopefully that gives you a better understanding of it.
1 Comment
ssergei
What would be the benefit of that vs storing the array? Wouldn't I get more ability to manipulate the data by storing arrays?