Professional Web Applications Themes

Query Question - PHP Development

I have a form with drop down menus to query for name, month, and year to capture activity accordingly by an individual for a given month and given year. But I'd like to also be able to query ALL individiduals for a given month and year OR an individual for the whole year for example. My question, is there any way to have blank (not filled in) fields of a query input ignored when left blank? I'm currently doing very successfully what I described in the para above but it's taking me 3 queries (which I have on the same ...

  1. #1

    Default Query Question

    I have a form with drop down menus to query for name, month, and year
    to capture activity accordingly by an individual for a given month and
    given year. But I'd like to also be able to query ALL individiduals
    for a given month and year OR an individual for the whole year for
    example.

    My question, is there any way to have blank (not filled in) fields of
    a query input ignored when left blank? I'm currently doing very
    successfully what I described in the para above but it's taking me 3
    queries (which I have on the same sheet for simplicity) to do it when
    it would look a lot neater to have a single query and leave any drop
    down menu items that I don't want to include on the query, blank and
    ignored. Any ideas? TIA
    cover Guest

  2. #2

    Default Re: Query Question

    so do you have an 'all' option in your drop down ?
    what do the three existing queries look like ?

    lesperancer@natpro.com Guest

  3. #3

    Default Re: Query Question

    Hello Cover,

    $query = "select * from table where 1 = 1 ";
    if($name != "") $query .= "and name = '".$name."'";
    if($month != "") $query .= "and month = '".$month."'";
    if($year != "") $query .= "and year = '".$year."'";

    Joseph Melnick
    JM Web Consultants
    http://www.jphp.com





    "cover" <coverland914 > wrote in message
    news:.. 


    Joseph Guest

  4. #4

    Default Re: Query Question

    I don't see anything wrong with that code. Because you're appending to
    the $query var, it's just one single SQL query. But your perl code
    (I'm assuming you're using perl) is controlling the query structure.

    BTW, I think your code would be more efficient if you used this syntax:

    if( $name != '' ) $query .= "and name = '$name'";

    or even:

    ( $name != '' ) && ( $query .= "and name = '$name'" );

    Hope this helps.
    -Hawk

    jedihawk Guest

  5. #5

    Default Re: Query Question

    I don't see anything wrong with that code. Because you're appending to
    the $query var, it's just one single SQL query.

    BTW, I think your code would be slightly more efficient if you used
    this syntax:

    if( $name != '' ) $query .= "and name = '$name'";

    jedihawk Guest

  6. #6

    Default Re: Query Question

    "jedihawk" <com> kirjoitti
    viestissä:googlegroups.com... 

    Forgetting spaces here...

    Something more like
    if( $name != '' )
    $query .= " and name = '$name' "; // padding string with some spaces

    So the final query won't be
    "select * from table where 1 = 1 and name = '$name'and month = '$month'and
    year = '$year'";

    but rather
    "select * from table where 1 = 1 and name = '$name' and month = '$month'
    and year = '$year' ";



    --
    "I am pro death penalty. That way people learn
    their lesson for the next time." -- Britney Spears

    com


    Kimmo Guest

  7. #7

    Default Re: Query Question

    What wonderful code - talk about slick... :-) don't quite understand
    what the "from table where 1 = 1" is doing (the business with the '1'
    but it sure works great - thanks so much...

    A second question since this bit of magic happened so fast and if you
    have a moment... On a database with name, month, year and so on is
    days (as in days taken off). The query runs very nicely but I'd love
    to have a total at the bottom of the query page.

    name, date, days, notes pretty much comprise it with 1, 2, 3, and so
    on being entered into a mysql database varchar field for the numbers.

    Is adding the totalizer relatively easy on a query? I have what I
    think is a pretty good book on PHP & mysql but haven't been able to
    find any answer there.

    thanks,
    Chris (cover)
    just changed my newserver

    On Fri, 27 May 2005 11:56:16 -0400, "Joseph Melnick"
    <com> wrote:
     

    Chris Guest

  8. #8

    Default Re: Query Question

    Chris (com) wrote:
    : What wonderful code - talk about slick... :-) don't quite understand
    : what the "from table where 1 = 1" is doing (the business with the '1'
    : but it sure works great - thanks so much...


    WHERE 1 = 1 (i.e. where one equals one)

    is always true.

    It is the same as having no where clause, except that you can append
    something like

    " AND name='fred' "

    and the statement will still work.

    WHERE 1 = 1 AND name='fred'


    Otherwise you need to worry about adding the WHERE clause and when to add
    the AND.

    It is a useful idiom, nothing more.

    --

    This space not for rent.
    Malcolm Guest

  9. #9

    Default Re: Query Question

    I really do appreciate ALL of your replies, guys - thanks so much...
    I've gained from all of them as I learn more about PHP.

    Chris

    Chris Guest

  10. #10

    Default Re: Query Question

    Hello Kimmo,

    You are correct. missing spaces are another trap.

    Adding white space between clauses is important.

    Thank you for noticing.

    Joseph Melnick

    "Kimmo Laine" <com> wrote in message
    news:d78204$624$kolumbus.fi... 
    >
    > Forgetting spaces here...
    >
    > Something more like
    > if( $name != '' )
    > $query .= " and name = '$name' "; // padding string with some spaces
    >
    > So the final query won't be
    > "select * from table where 1 = 1 and name = '$name'and month =
    > '$month'and year = '$year'";
    >
    > but rather
    > "select * from table where 1 = 1 and name = '$name' and month = '$month'
    > and year = '$year' ";
    >
    >
    >
    > --
    > "I am pro death penalty. That way people learn
    > their lesson for the next time." -- Britney Spears
    >
    > com
    >[/ref]


    Joseph Guest

  11. #11

    Default Re: Query Question

    Hi Chris

    Funny thing is I have been working on a site (see all my recent posts)
    where I have had to do something very similar, and I know how complicated
    is gets very quickly.

    Just for reference, you may want to look at adding some JavaScript to
    your form that does some of the hard work for you, the form I have
    been working on has something like 50 different queries that could be
    made depending on what the user has sleeted, I found by using JavaScript
    I could say if the user selects X then set Y to whatever. This way you
    are only passing the info you really need.

    Brian


    Brian Guest

  12. #12

    Default Re: Query Question

    I noticed that Message-ID: <6Jnme.743$ntli.net> from
    Brian contained the following:
     

    But you really should not rely on Javascript.

    --
    Geoff Berrow 0110001001101100010000000110
    001101101011011001000110111101100111001011
    100110001101101111001011100111010101101011
    Geoff Guest

  13. #13

    Default Re: Query Question

    Hello Geoff,

    This is definitely true.

    JavaScript should only be used for the benefit of the client to provide
    valid input.

    Expect the unexpected. Any hacker can sent garbage data to your application.

    Check input for length, type, range, format and remove garbage.

    Get to understand Regular Expressions, They are your friend.

    Write functions once that you can reuse to minimize your effort.


    Joseph Melnick
    JM Web Consultants
    www.jphp.com




    "Geoff Berrow" <co.uk> wrote in message
    news:com... 
    >
    > But you really should not rely on Javascript.
    >
    > --
    > Geoff Berrow 0110001001101100010000000110
    > 001101101011011001000110111101100111001011
    > 100110001101101111001011100111010101101011[/ref]


    Joseph Guest

  14. #14

    Default Re: Query Question


    "Joseph Melnick" <com> wrote in message
    news:com... 


    I agree with what your saying, but for example the site I have been
    working on recently required the user to select a 'store' or 'all stores'
    My client asked for a tick box for all stores, and 2 drop down lists of
    the store number or location.
    By using JavaScript, if the 'all' box is ticked then set the 2 drop down
    lists
    to blank, if say the store location is selected then set the store number
    and
    tick box to blank, and so on.

    This way when the form is submitted I can run various tests to see what
    has been set, what to look for and what variables to use. The reason I have
    done it this way is so I don't have to build a un-friendly user interface
    that
    would confuses the user.by select this if you want this, and that if you
    want that
    and so on.

    I'm not saying that once the form has been submitted you should run Regular
    Expressions on the incoming data, but this way you no what to run on what.


    Brian



    Brian Guest

  15. #15

    Default Re: Query Question

    Hello Brian,

    Yes you are correct. Using JavaScript for usability is valid.

    You could have simplified this task with a single mullti-select without the
    need for a checkbox.

    <select name="locations" multiple>
    <option value="99999" selected>All Locations</option>
    <option value="89837">89837 - Smallville</option>
    ....
    <option value="98374">98374 - Largerville</option>
    </select>

    Would simplify validation and reduce page real-estate.

    Just a idea.

    Have a great day.

    Joseph Melnick
    JM Web Consultants
    http://www.jphp.com



     


    Joseph Guest

  16. #16

    Default Query Question2

    Built on machine and works fine:
    PHP 5.0.4
    Apache 2.0.53
    MySQL Client API version 4.1.7

    Running on a server with the code below hanging up unless it's
    'remmed' out...:
    PHP 4.1.1
    Apache 1.3.23
    MySQL Client API version 3.23.39

    mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to
    database"); // make connection to database
    mysql_select_db($DBName) or die("Unable to select database $DBName");
    // select database
    // if (mysqli_connect_errno())
    // {
    // echo 'Error: Could not connect to database. Please report this
    problem.';
    // exit;
    // }


    Everything on my form worked fine on my development machine with the
    newer versions of PHP, MySQL, and Apache. When I ran it on the server
    with older versions, my query hung up.

    QUESTION: The part that specifically hangs is "if
    (mysqli_connect_errno())" of which I have to think something is there
    that wasn't in the older versions so... Does anyone know an
    equivalent line that will work on the older PHP ??? TIA




    Chris Guest

Similar Threads

  1. Query Question Please HELP
    By flooker in forum Coldfusion Database Access
    Replies: 4
    Last Post: June 26th, 04:45 PM
  2. 6.1 Query of Query Question. Ref to own Col
    By smcgovern in forum Coldfusion - Advanced Techniques
    Replies: 4
    Last Post: April 24th, 10:17 PM
  3. SQL query question
    By Joost Kraaijeveld in forum PostgreSQL / PGSQL
    Replies: 6
    Last Post: February 3rd, 11:48 AM
  4. ASP SQL query question
    By Brandon in forum ASP Database
    Replies: 6
    Last Post: May 3rd, 04:37 AM

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