returning all values

[AshleyH]

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

I have this:

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

[guelphdad]

remove the where clause. alternately use this:

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

AND con_Customer = "#URL.con_Customer#"

which would get triggered only when you know a customer id has been passed through your form.

[bvarvel]

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.

[guelphdad]

I'm gonna be lazy and appropriate your code bvarvel, this is what I am speaking of:

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.

[AshleyH]

Hi Guys,

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

Ashley

[r937]

here it is with two conditions --

<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

[AshleyH]

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?

[r937]

oh i just realized my example had two identical conditions, which would be rather pointless

yes you can pull just the A's

<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>

[AshleyH]

Brill thanks very much!

Ashley