Attention fellow PHP coders

Bit of coders block here...

I have a MySQL DB with about 60,000 records in, about 7 fields or so...

For example, field_a is a telephone number, and field_b is a result.

I want to find all telephone numbers where field_b has been 'Answerphone' three or more times for the same number in field_a... I just can't think of how to do this without a stupidly aggressive script.

Any ideas?

Code:

---

SELECT count(field_a) AS result_count
WHERE field_b='Answerphone' AND result_count>3
GROUP BY field_a

---

That not do the job ?

Looks like it might... give me a minute to try...

Hmm, customized it to fit my code...

Code:

---

SELECT count(number) AS result_count
WHERE comment='Answerphone' AND result_count>3
GROUP BY number

---

And just get this error...

Quote:

---

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE comment='Answerphone' AND result_count>3
GROUP BY number' at line 2

---

drop the "AND result_count>3" and see if that's the problem, you may not be able to have a WHERE clause based on the count

Quote:

---

Originally Posted by Iain

drop the "AND result_count>3" and see if that's the problem, you may not be able to have a WHERE clause based on the count

---

That is perfectly acceptable in MySQL.


What is wrong, is that there is no FROM part, try

Code:

---

SELECT COUNT(number) AS result_count
FROM tablex
WHERE comment='Answerphone' AND result_count>=3
GROUP BY number

---

I also change result_count=3 to result_count>=3 as he wants to include those that have 3 or more, not just more than 3.

I assumed he would know enough to have a FROM clause.....