0

I am using MySQL and PHP.

I tried to parameterize the queries using bind_param. Now I need to pass a list of integers to the parameterised IN Query.

I have tried with the below query:

select id,date,
  sum(value) as distance ,
  (select number from table2 where id=id) as Number
from Table1 where id in (1777,1778,1779)

But it is returning only the result for the first value in the list (1777).

How can I get the result for all the entries (1777,1778,1779) in the list?

Frazz
  • 2,995
  • 2
  • 19
  • 33
Rakesh
  • 41
  • 1
  • 7
  • what is `where id=id` is this a placeholder too? – Kevin Sep 09 '14 at 12:00
  • No, it was the id which is mentioned immediate after the select. in parameterised query i have used place holder. the equivalent parameterised query is : select id,date,sum(value) as distance ,(select number from table2 where id=id) as Number from Table1 where id in (?); – Rakesh Sep 09 '14 at 12:03
  • You might want to consider using a join rather than a subquery. – Rwd Sep 09 '14 at 12:08
  • Use some table aliases. id=id is ambiguous... both for us and for the MySQL engine. Another issue is you cannot parametrize a list of values for an IN condition. One parameter for one value. I know it can be a PITA when you want a variable length list of values, but that is how parameters work. – Frazz Sep 09 '14 at 12:29

1 Answers1

0

You need to use GROUP BY e.g.

SELECT 
    id, 
    date, 
    sum(value) as distance , 
    (SELECT number FROM table2 WHERE id=id) as Number 
FROM Table1 
WHERE 
    id in (1777,1778,1779) 
GROUP BY id

Hope this helps!

Rwd
  • 34,180
  • 6
  • 64
  • 78
  • We are getting the result with this. But when using bind_param we are not getting the result for the first id(1777) eg: $a='1777,1778,1779'; $sqlobj->bind_param('i',$a); when trying with this we are getting the result for only 1777 – Rakesh Sep 09 '14 at 12:12
  • You **CANNOT use ONE parameter for an entire list of values**. You must use a query like ... IN (?,?,?) and bind 3 parameters. – Frazz Sep 09 '14 at 12:30