Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Feb 2008
    Posts
    37
    Thanks
    4
    Thanked 0 Times in 0 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; 04-10-2009 at 10:23 PM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    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.)

  • Users who have thanked Old Pedant for this post:

    halifaxer (04-11-2009)


  •  

    Posting Permissions

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