Query not working when I add BETWEEN operator

Discussion in 'PHP' started by product, Jul 30, 2007.

0
  1. #1
    I have a search form with about 12 or so search fields, dropdowns, etc...

    THIS WORKS (below)

    <?php

mysql_select_db($database_studentaiddata, $studentaiddata);
$query_Recordset1 = "SELECT * 
FROM `master_table` 
WHERE `WhatSite` LIKE '%$WhatSite%' AND `LeadType` LIKE '%$LeadType%' AND `RepID` = '$RepID' AND `IAmA` LIKE '%$IAmA%' AND `LoanType` LIKE '%$LoanType%' AND `State` LIKE '%$State%' AND `LoanAmountRequest` LIKE '%$LoanAmountRequest%' AND `RadioButton1` LIKE '%$RadioButton1%' AND `RadioButton2` LIKE '%$RadioButton2%' AND `RadioButton3` LIKE '%$RadioButton3%' AND `RadioButton4` LIKE '%$RadioButton4%' AND `OutOfSchool` LIKE '%$OutOfSchool%' ORDER BY `Date` ASC";
$Recordset1 = mysql_query($query_Recordset1, $studentaiddata) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?>
    Code (markup):


    HERE IS THE QUERY THAT DOESNT WORK (read below and code follows)

    I have 3 queries that are missing in the WORKING code ABOVE... I have a Date/Time Stamp that I want to search between and 2 others "DOB" and "GradDate"... When I add any of the BETWEEN's nothing works.. Ive taken DOB and GradDate out to see if the "Date" one works and nothing also vise versa. Can someone please help!!!! 3 days on this!!! (PS: GradDate and DOB are in yyyy-mm-dd format coming from the form, Date is a Time stamp so if you see above the query I've added the time stamp)
    HERE IS THE QUERY THAT DOESNT WORK
    
<?php

$Date1 = "$Date1 01:00:01";
$Date2 = "$Date2 23:59:59";


mysql_select_db($database_studentaiddata, $studentaiddata);
$query_Recordset1 = "SELECT * 
FROM `master_table` 
WHERE `Date` BETWEEN '$Date1' AND '$Date2' AND `WhatSite` LIKE '%$WhatSite%' AND `LeadType` LIKE '%$LeadType%' AND `RepID` = '$RepID' AND `IAmA` LIKE '%$IAmA%' AND `LoanType` LIKE '%$LoanType%' AND `State` LIKE '%$State%' AND `DOB` BETWEEN '$DOB1' AND '$DOB2' AND `LoanAmountRequest` LIKE '%$LoanAmountRequest%' AND `GradDate` BETWEEN '$GradDate1' AND '$GradDate2' AND `RadioButton1` LIKE '%$RadioButton1%' AND `RadioButton2` LIKE '%$RadioButton2%' AND `RadioButton3` LIKE '%$RadioButton3%' AND `RadioButton4` LIKE '%$RadioButton4%' AND `OutOfSchool` LIKE '%$OutOfSchool%' ORDER BY `Date` ASC";
$Recordset1 = mysql_query($query_Recordset1, $studentaiddata) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?>
    Code (markup):
     
    product, Jul 30, 2007 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,899
    Likes Received:
    4,555
    Best Answers:
    123
    Trophy Points:
    665
    #2
    http://dev.mysql.com/doc/refman/4.1/en/comparison-operators.html#function_between

    but remember bodmas from school? You need some brackets to tell the database how to handle the query.

    This will be better

    $query_Recordset1 = "SELECT * 
FROM `master_table` 
WHERE (`Date` BETWEEN '{$Date1}' AND '{$Date2}')
 AND `WhatSite` LIKE '%{$WhatSite}%' 
 AND `LeadType` LIKE '%{$LeadType}%' 
 AND `RepID` = '{$RepID}' 
 AND `IAmA` LIKE '%$IAmA%' 
 AND `LoanType` LIKE '%$LoanType%' 
 AND `State` LIKE '%$State%' 
 AND (`DOB` BETWEEN '$DOB1' AND '$DOB2')
 AND `LoanAmountRequest` LIKE '%$LoanAmountRequest%' 
 AND (`GradDate` BETWEEN '$GradDate1' AND '$GradDate2')
 AND `RadioButton1` LIKE '%$RadioButton1%' 
 AND `RadioButton2` LIKE '%$RadioButton2%' 
 AND `RadioButton3` LIKE '%$RadioButton3%' 
 AND `RadioButton4` LIKE '%$RadioButton4%' 
 AND `OutOfSchool` LIKE '%$OutOfSchool%' 
 ORDER BY `Date` ASC";
    Code (markup):
    all those like statements are going to make it run like a pig - anything you can do about them? Surely you have more control over the radio buttons and can use a plain equals?

    throw {} in to make the parsing more exact and faster too.
     
    sarahk, Jul 30, 2007 IP
  3. product

    product Peon

    Messages:
    3
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    It still didnt work... Im very new to programming but I am also realizing that I have a ton of NULL values in my database. I just cleaned up a lot of testing data out of the database and realized that the only records being pulled when I query are the ones without any null values... how do I include rows that have null values because each row may contain null values in different columns? << Im thinking this my be my whole problem
     
    product, Jul 30, 2007 IP