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 8 of 8
  1. #1
    Regular Coder Bengal313's Avatar
    Join Date
    Jun 2002
    Location
    Metro Detroit
    Posts
    298
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Writing a complex sql statement.

    PHP/Oracle 9i

    I have a table (DOG_HEADER) that has the following fields, HOUSE_NO, STREET_NAME, AND APT_NO. I am trying to write a sql statement that will take in the values from a form and execute the query. The form is sending the values for HOUSE_NO, STREET_NAME, and APT_NO. I can get the sql to work only if all three are entered in the form. But, some people live in a house so they don't use apt_no. If the user leave it blank the results are incorrect. How do I write a query that will search for house_no, street_name, (which are required fields) and if there is an apt_no entered then search uisng that as well. Else just using those first two fields. Thanx Here is some sample code for the results page.



    <?php
    // $colname__rsdogs = strtoupper($colname__rsdogs); // I added this

    //$colname__rsdogdetails = strtoupper($colname__rsdogdetails); // I added this

    //Connection statement
    require_once('../../Connections/oracle2.php');

    // begin Recordset
    $colname__rsdogs = '1';
    if (isset($HTTP_GET_VARS['STREET_NAME'])) {
    $colname__rsdogs = $HTTP_GET_VARS['STREET_NAME'];
    }
    $NUMBER__rsdogs = '1';
    if (isset($HTTP_GET_VARS['HOUSE_NO'])) {
    $NUMBER__rsdogs = $HTTP_GET_VARS['HOUSE_NO'];
    }
    $APT__rsdogs = '1';
    if (isset($HTTP_GET_VARS['APT_NO'])) {
    $APT__rsdogs = $HTTP_GET_VARS['APT_NO'];
    }
    $query_rsdogs = sprintf("SELECT * FROM DOG_HEADER WHERE STREET_NAME = '%s' AND HOUSE_NO = '%s' AND APT_NO = '%s'", $colname__rsdogs,$NUMBER__rsdogs,$APT__rsdogs);
    $rsdogs = $oracle2->SelectLimit($query_rsdogs) or die($oracle2->ErrorMsg());
    $totalRows_rsdogs = $rsdogs->RecordCount();
    // end Recordset
    Last edited by Bengal313; 11-18-2003 at 09:55 PM.
    "Knowledge Speaks But Wisdom Listens"

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I never used PHP with Oracle and i don't quit understand the sql-stringlines, but i think you need to dynamically build the sql like this:
    PHP Code:
    //Connection statement
    require_once('../../Connections/oracle2.php');

    $query_rsdogs = ("SELECT * FROM DOG_HEADER WHERE 9=9");
    // begin Recordset
    if (isset($HTTP_GET_VARS['STREET_NAME'])) {
       
    $query_rsdogs .= (" AND STREET_NAME = " $HTTP_GET_VARS['STREET_NAME']);
    }
    if (isset(
    $HTTP_GET_VARS['HOUSE_NO'])) {
       
    $query_rsdogs .= (" AND HOUSE_NO = " $HTTP_GET_VARS['HOUSE_NO']);
    }
    if (isset(
    $HTTP_GET_VARS['APT_NO'])) {
       
    $query_rsdogs .=  (" AND APT_NO = " $HTTP_GET_VARS['APT_NO']);
    }
    $rsdogs $oracle2->SelectLimit($query_rsdogs) or die($oracle2->ErrorMsg());
    $totalRows_rsdogs $rsdogs->RecordCount();
    // end Recordset 
    -->The 9=9 is to make is a bit easier --> so you don't need a counter to see if the condition needs to start with 'AND' or not
    --> you really shouldn't use * --> specify the variables you actually need
    --> maybe it's just me, but i dont like isset for textfields. I always check if the strlen($_GET['var']) >=1
    -->if you pull values from the querystring, you realy need check the values to avoid SQL-injections
    --> why do you use the get-method? why not post ?
    <edit>typos</edit>
    Last edited by raf; 11-19-2003 at 12:06 AM.

  • #3
    Regular Coder
    Join Date
    Jun 2002
    Location
    Sheffield, UK
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i think i can answer the GET vars Q, looks to me a like a search of some sort, always use GET for searches so they can be bookmarked or linked to.
    "To be successful in IT you don't need to know everything - just where to find it in under 30 seconds"

    (Me Me Me Me Me Me Me Me Me)

  • #4
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Originally posted by ReadMe.txt
    i think i can answer the GET vars Q, looks to me a like a search of some sort, always use GET for searches so they can be bookmarked or linked to.
    I see what you mean, but it doesn't exactly looks like google or another search-engines page that only has a search-function + by allowing this fro bookmarking, you need to make sure that the db-design, search-algoritme and pageadress wount be changed + that you make sure you prevent DOS attacks that would really slow down your db (if not worse)

    Personally, i only use the get-method (i'm talking about posting forms using method="get", no dynamically generated links with PK-values in the querystring) if i also redirect to that page with values in the querystring, that i've computed or selected from a db or session-variables or so.

    But that's just me, of course.

  • #5
    Regular Coder Bengal313's Avatar
    Join Date
    Jun 2002
    Location
    Metro Detroit
    Posts
    298
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Hey guys,

    Everything works fine with the get method, but Like I said. but, if you look at my code, If the user doesn't enter a value for APT_NO, the resulting page uses "1" as a default.

    $APT__rsdogs = '1';

    Then the query fires and looks for the condition where HOUSE_NO, STREET_NAME are equal to the entered fields and APT_NO is equla to "1". So for those records that has the apartment field blank, the results are wrong. So, my question is How would I write a query that would set the APT_NO is nothing, meaning the field is empty and use an IF of ELSE IF statement to first set the apt_no to nothing then it would look for the incoming values for APT_NO,

    $APT__rsdogs = '1'; //how do I set this to nothing (empty)
    if (isset($HTTP_GET_VARS['APT_NO'])) {
    $APT__rsdogs = $HTTP_GET_VARS['APT_NO'];
    }


    and fire a condition statement that would do something like if there is a value in APT_NO then fire a statement like


    $query_rsdogs = sprintf("SELECT * FROM DOG_HEADER WHERE STREET_NAME = '%s' AND HOUSE_NO = '%s' AND APT_NO = '%s'", $colname__rsdogs,$NUMBER__rsdogs,$APT__rsdogs);
    $rsdogs = $oracle2->SelectLimit($query_rsdogs) or die($oracle2->ErrorMsg());
    $totalRows_rsdogs = $rsdogs->RecordCount();


    But if APT_NO is empty, then fire a sql statement without the APT_NO. example.

    $query_rsdogs = sprintf("SELECT * FROM DOG_HEADER WHERE STREET_NAME = '%s' AND HOUSE_NO = '%s'", $colname__rsdogs,$NUMBER__rsdogs,$APT__rsdogs);
    $rsdogs = $oracle2->SelectLimit($query_rsdogs) or die($oracle2->ErrorMsg());
    $totalRows_rsdogs = $rsdogs->RecordCount();
    "Knowledge Speaks But Wisdom Listens"

  • #6
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I don't know if you ' ve read my code, but that will do exactly what you ask. It will add the select-conditions of the variables, if they are set in the querystring

    Unless the app_no is always set. Then you need to change the condition. So

    PHP Code:
    if (isset($HTTP_GET_VARS['APT_NO'])) { 
    then becomes

    PHP Code:
    if (strlen($HTTP_GET_VARS['APT_NO']) >= 1) { // in case it's an empty variable or so 
    or

    PHP Code:
    if ($HTTP_GET_VARS['APT_NO'] != 1) {  // if the querystringvariable is set to 1 in the posting page 
    <edit>The app replaced some spaces y underscores </edit>
    Last edited by raf; 11-19-2003 at 09:48 AM.

  • #7
    Regular Coder Bengal313's Avatar
    Join Date
    Jun 2002
    Location
    Metro Detroit
    Posts
    298
    Thanks
    8
    Thanked 0 Times in 0 Posts
    I tried the code you guys suggested. I seem to get al kinds of error I knda played with my code and got it to work somewhat. When I seach without entering a APT_NO I get an exact match. But when I enter APT_NO, instead of getting just the apartment with that APT_NO, I get a listing of all the listing under that HOUSE_NO and STREET_NAME. Here is my code can anyone help me.


    //Connection statement
    require_once('../../Connections/oracle2.php');

    // begin Recordset
    $colname__rsdogs = '1';
    if (isset($HTTP_GET_VARS['STREET_NAME'])) {
    $colname__rsdogs = $HTTP_GET_VARS['STREET_NAME'];
    }
    $NUMBER__rsdogs = '1';
    if (isset($HTTP_GET_VARS['HOUSE_NO'])) {
    $NUMBER__rsdogs = $HTTP_GET_VARS['HOUSE_NO'];
    }
    $APT__rsdogs = 'z';
    if (isset($HTTP_GET_VARS['APT_NO'])) {
    $APT__rsdogs = $HTTP_GET_VARS['APT_NO'];
    }

    if ($APT__rsdogs = 'z') {
    $query_rsdogs = sprintf("SELECT * FROM DOG_HEADER WHERE STREET_NAME = '%s' AND HOUSE_NO = '%s'", $colname__rsdogs,$NUMBER__rsdogs,$APT__rsdogs);
    }
    else {
    $query_rsdogs = sprintf("SELECT * FROM DOG_HEADER WHERE STREET_NAME = '%s' AND HOUSE_NO = '%s' AND APT_NO = '%s'", $colname__rsdogs,$NUMBER__rsdogs,$APT__rsdogs);
    }
    $rsdogs = $oracle2->SelectLimit($query_rsdogs) or die($oracle2->ErrorMsg());
    $totalRows_rsdogs = $rsdogs->RecordCount();
    // end Recordset
    "Knowledge Speaks But Wisdom Listens"

  • #8
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    if ($APT__rsdogs = 'z') {

    should be
    if ($APT__rsdogs == 'z') {


  •  

    Posting Permissions

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