Professional Web Applications Themes

[mysql] SELECT COUNT(*) question - PHP Development

Is something like the following possible? SELECT products.id, count(*) as counter FROM products, keywords WHERE keywords.keyword in ("bla","hi","hello") AND counter > 2; Can't get it to work :/ Floris...

  1. #1

    Default [mysql] SELECT COUNT(*) question

    Is something like the following possible?

    SELECT products.id, count(*) as counter FROM products, keywords WHERE
    keywords.keyword in ("bla","hi","hello") AND counter > 2;

    Can't get it to work :/

    Floris


    Floris van den Berg Guest

  2. #2

    Default Re: [mysql] SELECT COUNT(*) question


    "Floris van den Berg" <flvdbergwxs.nl> wrote in message
    news:bkejov$kjt$1reader08.wxs.nl...
    > Is something like the following possible?
    >
    > SELECT products.id, count(*) as counter FROM products, keywords WHERE
    > keywords.keyword in ("bla","hi","hello") AND counter > 2;
    >
    > Can't get it to work :/
    >
    > Floris
    >
    >
    You've not specified the table join and to use the count function you need
    to use GROUP BY clause. Having said that I don't think the counter>2 will
    work anyway, not allowed to use the results of group function for extraction
    criteria. You'll probably need to create a temp table with the count results
    and then extract the final result set from that. Sub queries may do the
    trick as well but depends on what version of Mysql you have.

    Rgds,
    Chris


    gortonc Guest

  3. #3

    Default Re: [mysql] SELECT COUNT(*) question


    "Floris van den Berg" <flvdbergwxs.nl> wrote in message
    news:bkejov$kjt$1reader08.wxs.nl...
    > Is something like the following possible?
    No.
    > SELECT products.id, count(*) as counter FROM products, keywords WHERE
    > keywords.keyword in ("bla","hi","hello") AND counter > 2;
    If you explain what you want to do, may be we can help.


    sam Guest

  4. #4

    Default Re: [mysql] SELECT COUNT(*) question

    "Floris van den Berg" <flvdbergwxs.nl> wrote in news:bkejov$kjt$1
    reader08.wxs.nl:
    > Is something like the following possible?
    >
    > SELECT products.id, count(*) as counter FROM products, keywords WHERE
    > keywords.keyword in ("bla","hi","hello") AND counter > 2;
    >
    > Can't get it to work :/
    SELECT products.id, count(*) as counter
    FROM products, keywords
    WHERE keywords.keyword in ("bla","hi","hello")
    GROUP BY products.id
    HAVING counter > 2

    --
    Peter Strömberg
    C2K2 C2K3 ISCCIV02
    Peter Strömberg Guest

  5. #5

    Default Re: [mysql] SELECT COUNT(*) question

    "sam" <rbaba99caramail.com> schreef in bericht
    news:bkenas$1in3$1news.cybercity.dk...
    >
    > "Floris van den Berg" <flvdbergwxs.nl> wrote in message
    > news:bkejov$kjt$1reader08.wxs.nl...
    > > Is something like the following possible?
    >
    > No.
    >
    > > SELECT products.id, count(*) as counter FROM products, keywords WHERE
    > > keywords.keyword in ("bla","hi","hello") AND counter > 2;
    >
    > If you explain what you want to do, may be we can help.
    Okay. What i want to do is create a search page on an e-commerce website. I
    have a table with products (id, title, price) and a table with keywords (id,
    keyword). I have an array of strings i want to lookup in the keywords table
    (and i do have a GROUP BY as gortonc correctly remarked). The idea is that a
    product is only found if *all* given keywords are found in a product title.
    So i count the found entries and only accept those entries that have a count
    larger than the size of the keywords array.

    Floris


    Floris van den Berg Guest

  6. #6

    Default Re: [mysql] SELECT COUNT(*) question

    Floris van den Berg wrote...
    > Is something like the following possible?
    >
    > SELECT products.id, count(*) as counter FROM products, keywords WHERE
    > keywords.keyword in ("bla","hi","hello") AND counter > 2;
    >
    > Can't get it to work :/
    >
    > Floris

    try this:

    select products.id, count(*) as counter
    from products, keywords
    where keywords.keyword in ("bla","hi","hello")
    group by products.id
    having count(*) > 2
    hexkid Guest

  7. #7

    Default Re: [mysql] SELECT COUNT(*) question

    "hexkid" <hexkidhotpop.com> schreef in bericht
    news:da59b90c.0309190719.3c0cf146posting.google.c om...
    > Floris van den Berg wrote...
    > > Is something like the following possible?
    > >
    > > SELECT products.id, count(*) as counter FROM products, keywords WHERE
    > > keywords.keyword in ("bla","hi","hello") AND counter > 2;
    > >
    > > Can't get it to work :/
    > >
    > > Floris
    >
    >
    > try this:
    >
    > select products.id, count(*) as counter
    > from products, keywords
    > where keywords.keyword in ("bla","hi","hello")
    > group by products.id
    > having count(*) > 2
    Thanks! That works!

    Floris


    Floris van den Berg Guest

Similar Threads

  1. select count(*) from <table> taking FOREVER
    By Fan Ruo Xin in forum IBM DB2
    Replies: 10
    Last Post: June 6th, 05:24 PM
  2. Glitch in php or count() in mysql?
    By Schraalhans Keukenmeester in forum MySQL
    Replies: 7
    Last Post: October 24th, 02:56 AM
  3. Mysql select question, counting total recs for each state?
    By MeNotHome in forum PHP Development
    Replies: 1
    Last Post: July 12th, 09:32 PM
  4. newbie : trying to count records in mysql table
    By jim in forum PHP Development
    Replies: 1
    Last Post: July 11th, 02:25 PM
  5. a select within a count ??
    By andrewst in forum Oracle Server
    Replies: 3
    Last Post: July 1st, 01:33 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139