SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Addict AshleyH's Avatar
    Join Date
    Mar 2005
    Posts
    260
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    returning all values

    I'm sure this is a straight forward and obvious one:

    I have this:

    Code:
    SELECT *
    FROM tbl_contacts
    WHERE con_Customer = "#URL.con_Customer#"
    Which obviously when you pass a value over the url returns the relevant records.

    How can I pass a value to the url which passes ALL the records back.
    Is it something like ?con_customer='%'?

    Many thanks

    Ashley

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    remove the where clause. alternately use this:

    Code:
    SELECT *
    FROM tbl_contacts
    WHERE 1=1
    then what you do is add your additional condition above but do a check for that in your scripting language.

    i.e. i'm guessing you are passing a value from a form or something so you would do an IF/ELSE clause and add

    Code:
    AND con_Customer = "#URL.con_Customer#"
    which would get triggered only when you know a customer id has been passed through your form.

  3. #3
    googlicious graymatter bvarvel's Avatar
    Join Date
    Sep 2002
    Location
    Katy, TX
    Posts
    956
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:

    if (isset(_$GET['variable'])) {
       
    $sql "SELECT * FROM tbl_contacts WHERE con_Customer = \"#URL.con_Customer#\"";
    } else {
       
    $sql "SELECT * FROM tbl_contacts";

    This of course is a VERY simplified way to do it, you'll need to make sure you're not prone to sql injection and some error handling, but this should give you an idea of the basics.

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm gonna be lazy and appropriate your code bvarvel, this is what I am speaking of:

    PHP Code:
    if (isset(_$GET['variable'])) {
       
    $sql "SELECT * FROM tbl_contacts WHERE 1 = 1";
    } else {
       
    $sql $sql "con_Customer = \"#URL.con_Customer#\""

    yours will work fine as well. I've picked the above up though because it is easier to modify if there are more conditions.

    perhaps a customer id could be entered or a part name or job number etc. etc. but not necessarily all at once. just a little less fiddling with the code I guess. and yes you would also be doing an isset for each of those variables in your IF/ELSIF/ELSE clauses.

  5. #5
    SitePoint Addict AshleyH's Avatar
    Join Date
    Mar 2005
    Posts
    260
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Guys,

    Thanks for the responses, think I get what your saying - I'm coding in Coldfusion.

    Ashley

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    here it is with two conditions --
    Code:
    <CFQUERY NAME="myquery">
       SELECT please
            , list
            , the
            , columns
            , you
            , need
         FROM tbl_contacts
        WHERE 1=1
       <CFIF LEN(URL.con_Customer)>
          AND con_Customer = '#URL.con_Customer#'
       </CFIF>
       <CFIF LEN(URL.con_Customer)>
          AND con_Customer = '#URL.con_Customer#'
       </CFIF>
    </CFQUERY>
    note that either or both of the conditions can be absent -- if both are absent, then WHERE 1=1 returns all rows
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict AshleyH's Avatar
    Join Date
    Mar 2005
    Posts
    260
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy - thanks for the response!

    If, for example, I wanted to show companies that start with the letter 'A' is there much more coding to go through - is it much more complex?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    oh i just realized my example had two identical conditions, which would be rather pointless

    yes you can pull just the A's
    Code:
    <CFQUERY NAME="myquery">
       SELECT please
            , list
            , the
            , columns
            , you
            , need
         FROM tbl_contacts
        WHERE 1=1
       <CFIF LEN(URL.con_Customer_initial)>
          AND con_Customer LIKE '#URL.con_Customer_initial#&#37;'
       </CFIF>
    </CFQUERY>
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict AshleyH's Avatar
    Join Date
    Mar 2005
    Posts
    260
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Brill thanks very much!

    Ashley


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
  •