Professional Web Applications Themes

Mysql select query with php - PHP Development

Hi, I want to create a sql query but don't know if this is possible with mysql. The Query should do the following: Select all db entries from table in which entry a is smaller than the number (count) of all db entries with criteria x from another table. Thx for your help! Benjamin gießer...

  1. #1

    Default Mysql Select query with php

    Hi, I want to create a sql query but don't know if this is possible with
    mysql.
    The Query should do the following:
    Select all db entries from table in which entry a is smaller than the number
    (count) of all db entries with criteria x from another table.
    Thx for your help!
    Benjamin gießer


    Benjamin Dickgießer Guest

  2. #2

    Default Mysql select query with php

    Hi, I want to create a sql query but don't know if this is possible with
    mysql.
    The Query should do the following:
    Select all db entries from table in which entry a is smaller than the number
    (count) of all db entries with criteria x from another table.
    Thx for your help!
    Benjamin gießer


    Benjamin Dickgießer Guest

  3. #3

    Default Re: Mysql select query with php


    "Benjamin gießer" <Benjamingiesser.net> schrieb im Newsbeitrag
    news:bp7pe1$feg$1uns-a.ucl.ac.uk...
    > Hi, I want to create a sql query but don't know if this is possible with
    > mysql.
    > The Query should do the following:
    > Select all db entries from table in which entry a is smaller than the
    number
    > (count) of all db entries with criteria x from another table.
    > Thx for your help!
    > Benjamin gießer
    >
    >
    Hi there,

    you have to split your query in two subqueries:
    $query1 = "SELECT COUNT(*) as counter FROM another_table WHERE
    criteria='X'";
    $result = mysql_query ($query1);
    // ---- at this place, you have to extract counter from result ---- see php
    doentation for this..
    // something like $array = mysql_fetch_array($result); $counter =
    $array['counter'];

    $query2 = "SELECT * FROM your_table WHERE entry < $counter ";
    // execute second query and that's it.. mysql doesn't support subqueries, as
    far as I know


    regards..
    dani




    Dani Guest

  4. #4

    Default Re: Mysql Select query with php

    On Sun, 16 Nov 2003 12:08:19 -0000, "Benjamin gießer"
    <Benjamingiesser.net> wrote:
    >Hi, I want to create a sql query but don't know if this is possible with
    >mysql.
    Please don't multipost, you've sent an identical post to alt.php.sql. If you
    want to post to multiple groups, then crosspost (one message, with multiple
    groups in the Newsgroup header).
    >The Query should do the following:
    >Select all db entries from table in which entry a is smaller than the number
    >(count) of all db entries with criteria x from another table.
    In most databases, you'd just use a subquery:

    SELECT *
    FROM table2
    WHERE a < (SELECT COUNT(*)
    FROM table1
    WHERE 'criteria x')

    But MySQL doesn't support subqueries.

    So you're probably better off executing the subquery to get the count, and
    then doing a second query against the first table to compare with entry 'a'.
    Assuming that your 'criteria x' is independent of the outer select, though.

    --
    Andy Hassall (andyandyh.co.uk) icq(5747695) ([url]http://www.andyh.co.uk[/url])
    Space: disk usage ysis tool ([url]http://www.andyhsoftware.co.uk/space[/url])
    Andy Hassall Guest

  5. #5

    Default Re: Mysql Select query with php

    4.1 does.


    "Andy Hassall" <andyandyh.co.uk> wrote in message
    news:7ivervg03i679oscgssuqs37apng8n9m364ax.com...
    > On Sun, 16 Nov 2003 12:08:19 -0000, "Benjamin gießer"
    > <Benjamingiesser.net> wrote:
    >
    > >Hi, I want to create a sql query but don't know if this is possible with
    > >mysql.
    >
    > Please don't multipost, you've sent an identical post to alt.php.sql. If
    you
    > want to post to multiple groups, then crosspost (one message, with
    multiple
    > groups in the Newsgroup header).
    >
    > >The Query should do the following:
    > >Select all db entries from table in which entry a is smaller than the
    number
    > >(count) of all db entries with criteria x from another table.
    >
    > In most databases, you'd just use a subquery:
    >
    > SELECT *
    > FROM table2
    > WHERE a < (SELECT COUNT(*)
    > FROM table1
    > WHERE 'criteria x')
    >
    > But MySQL doesn't support subqueries.
    >
    > So you're probably better off executing the subquery to get the count,
    and
    > then doing a second query against the first table to compare with entry
    'a'.
    > Assuming that your 'criteria x' is independent of the outer select,
    though.
    >
    > --
    > Andy Hassall (andyandyh.co.uk) icq(5747695) ([url]http://www.andyh.co.uk[/url])
    > Space: disk usage ysis tool ([url]http://www.andyhsoftware.co.uk/space[/url])

    john Guest

  6. #6

    Default Re: Mysql Select query with php


    On Sun, 16 Nov 2003 16:15:01 GMT, "john" <mynameisjohn.com> wrote:
    >"Andy Hassall" <andyandyh.co.uk> wrote in message
    >news:7ivervg03i679oscgssuqs37apng8n9m364ax.com.. .
    >> But MySQL doesn't support subqueries.
    >
    >4.1 does.
    4.1 is still alpha; when it's released, then it'll be fair to say MySQL
    supports subqueries.

    --
    Andy Hassall (andyandyh.co.uk) icq(5747695) ([url]http://www.andyh.co.uk[/url])
    Space: disk usage ysis tool ([url]http://www.andyhsoftware.co.uk/space[/url])
    Andy Hassall Guest

  7. #7

    Default Re: Mysql select query with php

    On Sun, 16 Nov 2003 12:09:15 -0000, "Benjamin gießer"
    <Benjamingiesser.net> wrote:
    >Hi, I want to create a sql query but don't know if this is possible with
    >mysql.
    >The Query should do the following:
    >Select all db entries from table in which entry a is smaller than the number
    >(count) of all db entries with criteria x from another table.
    >Thx for your help!
    >Benjamin gießer
    >
    You might be able to something like...

    SELECT * FROM table1,table2
    WHERE table1.field < COUNT(table2.field)
    AND table2.otherfield=whatever

    I've not tried it though :)


    kafooey
    - [email]kafooeynospam.yahoo.co.uk[/email]
    - [url]http://www.pluggedout.com/blog[/url]
    kafooey Guest

  8. #8

    Default Re: Mysql select query with php

    On Sun, 16 Nov 2003 12:09:15 -0000, "Benjamin gießer"
    <Benjamingiesser.net> wrote:
    >Hi, I want to create a sql query but don't know if this is possible with
    >mysql.
    >The Query should do the following:
    >Select all db entries from table in which entry a is smaller than the number
    >(count) of all db entries with criteria x from another table.
    >Thx for your help!
    >Benjamin gießer
    >
    If you have the latest version of MySQL you can nest SQL statements of
    course...

    e.g.

    SELECT * FROM table1
    WHERE field1 > SELECT COUNT(*) FROM table2 WHERE...



    kafooey
    - [email]kafooeynospam.yahoo.co.uk[/email]
    - [url]http://www.pluggedout.com/blog[/url]
    kafooey Guest

Similar Threads

  1. Replies: 4
    Last Post: July 30th, 02:02 AM
  2. Query of Query to select a title first letter
    By Conti in forum Coldfusion Database Access
    Replies: 2
    Last Post: March 18th, 11:42 PM
  3. Replies: 0
    Last Post: November 4th, 05:38 PM
  4. Using < and > in a select query
    By Grynch in forum Coldfusion Database Access
    Replies: 3
    Last Post: April 18th, 03:55 AM
  5. mysql select query with unusual criteria fails
    By Phil Powell in forum PHP Development
    Replies: 1
    Last Post: October 20th, 07:47 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