Web Analytics Made Easy -
StatCounter Complicated Statement - CodingForum

Announcement

Collapse
No announcement yet.

Complicated Statement

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Complicated Statement

    right, i need help with an SQL statement. been struggling with this and posted a few questions in the PHP section, but it does come down to the SQL statement in the end.

    to keep it simple, if in my database i have numerous rows that all have the following fields: autoInc (auto incremented), name, id.

    Code:
    auto Inc name  id
    1           Bob   1001
    2           Sue   1002
    3           Jim    1005
    4           Joe    1009
    5           Bill     1002
    ...etc.
    from here, i'm going to run a for loop that will search all numbers starting at "1001", right up to "9999"

    Code:
    for ($i=1001;$i<=9999;$i++) {
    
    etc.
    
    }
    as each $i variable is accounted for (1001,1002,1003,1004,1005... etc.), I want to use a statement to search the "id" field in my database - and if $i is NOT found, i want it to...

    Code:
    echo $i.'<br />';
    ...which with the above example will look something like...

    Code:
    1003
    1004
    1006
    1007
    1008
    1010...etc.
    The "id" represents which category each person falls into, so there will be some with the same "id" (Bob & Bill), hence why it's not the auto-incremented field.

    Can I write basic statements... yes. Do I get confused when they get more complicated and involve IN and BETWEEN? Very much so.

    I know it can be done, but just need help with the syntax.

    Many thanks...

    Last edited by halifaxer; Apr 10, 2009, 05:23 PM.

  • #2
    No, not complicated, at all. Honest, about as simple as SQL queries get.
    Code:
    SELECT DISTINCT id 
    FROM yourtablename 
    WHERE id BETWEEN 1001 AND 9999 
    ORDER BY id
    You don't even need to use BETWEEN, by the by:
    Code:
    SELECT DISTINCT id 
    FROM yourtablename 
    WHERE id >= 1001 AND id <= 9999 
    ORDER BY id
    or, 100% equivalent,
    Code:
    SELECT DISTINCT id 
    FROM yourtablename 
    WHERE id > 1000 AND id < 10000
    ORDER BY id
    (Assuming, of course, the id is an integer field.)
    Be yourself. No one else is as qualified.

    Comment

    Working...
    X