Results 1 to 7 of 7

Thread: Attention fellow PHP coders

  1. #1
    Banned
    Join Date
    Jul 2004
    Location
    Blackpool
    Posts
    983
    Thanks
    15
    Thanked
    38 times in 20 posts

    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?

  2. #2
    HEXUS.net Webmaster
    Join Date
    Jul 2003
    Location
    UK
    Posts
    3,108
    Thanks
    1
    Thanked
    0 times in 0 posts
    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 ?

  3. #3
    Banned
    Join Date
    Jul 2004
    Location
    Blackpool
    Posts
    983
    Thanks
    15
    Thanked
    38 times in 20 posts
    Looks like it might... give me a minute to try...

  4. #4
    Banned
    Join Date
    Jul 2004
    Location
    Blackpool
    Posts
    983
    Thanks
    15
    Thanked
    38 times in 20 posts
    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...
    #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

  5. #5
    HEXUS.net Webmaster
    Join Date
    Jul 2003
    Location
    UK
    Posts
    3,108
    Thanks
    1
    Thanked
    0 times in 0 posts
    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

  6. #6
    Member
    Join Date
    Jul 2003
    Posts
    59
    Thanks
    0
    Thanked
    0 times in 0 posts
    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.

  7. #7
    HEXUS.net Webmaster
    Join Date
    Jul 2003
    Location
    UK
    Posts
    3,108
    Thanks
    1
    Thanked
    0 times in 0 posts
    I assumed he would know enough to have a FROM clause.....

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. FREE PHP and MYSQL Webservers
    By ERU in forum Networking and Broadband
    Replies: 4
    Last Post: 12-04-2006, 09:00 PM
  2. Attention fellow Hexites! Charity ahoy!
    By Michael in forum General Discussion
    Replies: 7
    Last Post: 17-01-2006, 06:18 PM
  3. Making PHP and MySQL Talk?
    By Dav0s in forum Software
    Replies: 4
    Last Post: 18-09-2005, 10:53 PM
  4. Using PHP to replace the <title> tag.
    By XA04 in forum Software
    Replies: 8
    Last Post: 15-05-2005, 10:34 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •