Insert array into SQL query

Question

I am running the following SQL query...

$user = 'salesrep_83';
SELECT COUNT(*) AS COUNT
FROM my_table_1 a
JOIN my_table_2 b
ON b.id       = a.sale_id
WHERE a.value = '$user'
AND b.date_posted LIKE '05-05-2014'

This works correctly and shows me all of the sales for the specified $user for todays date. I would like to change this so that it shows me all of the sales for todays date from all sales reps. I can generate and array with all of the users IDs but am unsure how to insert this into the query

Anyone has an example?

What if you just remove WHERE a.value = '$user'?

Guneli
– Guneli

2014-05-05 07:16:03 +00:00
Commented May 5, 2014 at 7:16 — Guneli
– Guneli, Commented May 5, 2014 at 7:16
Actually you'd remove a.value = '$user' AND...

hd1
– hd1

2014-05-05 07:18:38 +00:00
Commented May 5, 2014 at 7:18 — hd1
– hd1, Commented May 5, 2014 at 7:18

edi spring · Accepted Answer · 2014-05-05 09:27:33Z

2

You can simply use the in operator.

SELECT COUNT(*) AS count 
FROM my_table_1 a JOIN my_table_2 b ON b.id = a.sale_id 
WHERE a.value in ('salesrep_82','salesrep_83')
AND b.date_posted = '05-05-2014'

UPDATED (you're right Rahul):

Using LIKE makes no sense without a wildcard like %

b.date_posted LIKE '05-05-2014'

use

b.date_posted = '05-05-2014'

instead.

edited May 5, 2014 at 9:27

answered May 5, 2014 at 7:17

edi spring

665 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rahul Over a year ago

@fightstarr20, condition b.date_posted LIKE '05-05-2014' is actually doing a exact match; in such case why not make it b.date_posted = '05-05-2014'?

Ricardo · Accepted Answer · 2014-05-05 07:18:47Z

0

If you want the aggregate of all users combined, just use the in clause:

SELECT COUNT(*) AS COUNT
FROM my_table_1 a
JOIN my_table_2 b
ON b.id       = a.sale_id
WHERE a.value in ('$user1', '$user2')
AND b.date_posted LIKE '05-05-2014'

If you want to have the list of sales by user, then you can combine the in with group by:

SELECT value, COUNT(*) AS COUNT
FROM my_table_1 a
JOIN my_table_2 b
ON b.id       = a.sale_id
WHERE a.value in ('$user1', '$user2')
AND b.date_posted LIKE '05-05-2014'
GROUP BY value

Note that depending on where you are running this query, it is generally not a good practice to just assign the string value to it because of problems such as SQL injection.

answered May 5, 2014 at 7:18

Ricardo

7,9758 gold badges43 silver badges61 bronze badges

Collectives™ on Stack Overflow

Insert array into SQL query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related