...

View Full Version : Writing a complex sql statement.



Bengal313
11-18-2003, 09:33 PM
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

raf
11-18-2003, 10:13 PM
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:


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

ReadMe.txt
11-18-2003, 10:32 PM
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.

raf
11-19-2003, 12:15 AM
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.

Bengal313
11-19-2003, 08:46 AM
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();

raf
11-19-2003, 09:43 AM
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


if (isset($HTTP_GET_VARS['APT_NO'])) {

then becomes


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

or


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>

Bengal313
11-20-2003, 05:25 PM
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

raf
11-20-2003, 08:43 PM
if ($APT__rsdogs = 'z') {

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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum