I have a table like
+------+----------+
| id | location |
+------+----------+
| 1 | TVM |
| 2 | KLM |
| 3 | EKM |
+------+----------+
And I have an array of id like [1,2,1,3,1]. I need to get the result as
+------+----------+
| id | location |
+------+----------+
| 1 | TVM |
| 2 | KLM |
| 1 | TVM |
| 3 | EKM |
| 1 | TVM |
+------+----------+
I am already tried WHERE IN like conditions but no luck.
A where statement cannot multiply the number of rows. It can only filter rows out. You want a join:
select tl.*
from tablelike tl join
(select 1 as n union all select 2 union all select 1 union all
select 3 union all select 1
) n
on tl.id = n.n;
Note: if you are already generating the list via a query or from a table, then use that for the query rather than passing the list out of the database and then back in.
(SELECT 1 AS n, 1 AS s UNION ALL SELECT 2, 2 UNION ALL SELECT 1,3 UNION ALL SELECT 3,4 ...) and then add ORDER BY n.s on the outer query.
order by clause.You could also return this result with a query like this; this uses a separate SELECT to return each occurrence of row with id=1.
( SELECT id, location FROM mytable WHERE id IN (1,2)
ORDER BY id
)
UNION ALL
( SELECT id, location FROM mytable WHERE id IN (1,3)
ORDER BY id
)
UNION ALL
( SELECT id, location FROM mytable WHERE id IN (1)
ORDER BY id
)
Following a similar pattern, the result could be obtained by combining the results from five SELECT statements, each returning a separate row. That would probably be a little simpler to achieve from a small array, e.g.
$glue = ") ) UNION ALL
(SELECT id, location FROM mytable WHERE id IN (";
$sql = "(SELECT id, location FROM mytable WHERE id IN ("
. implode($glue, $myarray)
. ") )";
