CODEWITHSUNDEEP
phpmysqlmysqli
ajax1515
ajax1515

Reputation: 310

Include duplicate results in MySQL result

I have a query of this form:

$query = "SELECT `id`, `value` FROM `table` 
WHERE `id` IN "."('".$mod."') ORDER BY FIELD (`id`,'".$mod."')";

$mod is an Array of values that gets transformed into a string like this:

$mod = implode("','", $mod);

The values in $mod are simple non-negative integers but they sometimes repeat... I need the query to return a result that has all rows including duplicates in order i.e.

when $mod contains the following set of id's {35,21,34,14,35,21} the query should return these rows:

$result    id       value
row1       35       "value35"
row2       21       "value21"
row3       34       "value34"
row4       14       "value14"
row5       35       "value35"
row6       21       "value21"

Upvotes: 1

Views: 78

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562951

You need a temporary table containing your set of id's. Use an incrementing primary key for the order.

CREATE TEMPORARY TABLE setofids ( setorder int auto_increment primary key, id int);
INSERT INTO setofids (id) VALUES (35),(21),(34),(14),(35),(21);

Then join to your table:

SELECT table.id, table.value FROM table
JOIN setofids USING (id)
ORDER BY setofids.setorder

Upvotes: 2

Related Questions