SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member
    Join Date
    Apr 2006
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem with query

    This query doesnt quite work.

    Code:
    <?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 (`DOB` BETWEEN '{$DOB1}' AND '{$DOB2}')
     AND (`GradDate` BETWEEN '{$GradDate1}' AND '{$GradDate2}')
     AND `WhatSite` LIKE '&#37;{$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 `ID` ASC";
     
    
    
    $Recordset1 = mysql_query($query_Recordset1, $studentaiddata) or die(mysql_error());
    $row_Recordset1 = mysql_fetch_assoc($Recordset1);
    $totalRows_Recordset1 = mysql_num_rows($Recordset1);
    ?>
    The problem is these three lines of code:
    Code:
     WHERE (`Date` BETWEEN '{$Date1}' AND '{$Date2}')
     AND (`DOB` BETWEEN '{$DOB1}' AND '{$DOB2}')
     AND (`GradDate` BETWEEN '{$GradDate1}' AND '{$GradDate2}')
    For example if I only have the $Date query I can search by date
    OR if I only have the $DOB query I can search by date of birth
    OR if I only have the $GradDate query I can search by grad date!
    BUT when I have two of them together or all three I dont get any results

    I ALREADY KNOW MY PROBLEM THOUGH!!!!!!!!!!!!!!!!!!!!! The problem is that some of my rows of data dont contain all of that data.. They all contain the $Date but not all rows have data for $DOB and $GradDate. All of my data is set to not null because when I get null values I dont get any of those results at all.... Please help Im a serious newbie and Ive been trying to figure this out for a week!

  2. #2
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    WHERE (`Date` = '' OR `Date` BETWEEN '{$Date1}' AND '{$Date2}')
     AND (`DOB = '' OR `DOB` BETWEEN '{$DOB1}' AND '{$DOB2}')
     AND (`GradDate` = '' OR `GradDate` BETWEEN '{$GradDate1}' AND '{$GradDate2}')

    Also, earlier in the code I think you mean $Date1 = "$Date1 00:00:00";

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    please don't compare dates to empty strings

    that doesn't make any sense whatsoever
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok,
    `Date` = '0000-00-00 00:00:00' OR `Date` BETWEEN .....

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the "zero" date is also a very poor coding decision

    use NULL and your SQL skills will be portable to other databases
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Apr 2006
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    NULL doesnt pull any results though and I dont know how to write a query that pulls NULL results along with the others. Also the other replies are small tid bits.. Im not "Skilled" enough in this to understand what you mean. Can you show me the full code how it would be written.. I tried to organize my post so it would be easy to understand.

  7. #7
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    this will not work:
    Code:
    `Date` = NULL
    this will:
    Code:
    `Date` IS NULL

  8. #8
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks longneck. The IS NULL is what I needed to know.

    product247 -
    replace the WHERE clauses in your query for date, DOB and gradDate with the following
    Code:
    WHERE (`Date` IS NULL OR `Date` BETWEEN '{$Date1}' AND '{$Date2}')
     AND (`DOB IS NULL OR `DOB` BETWEEN '{$DOB1}' AND '{$DOB2}')
     AND (`GradDate` IS NULL OR `GradDate` BETWEEN '{$GradDate1}' AND '{$GradDate2}')
    This way the dates will only be restricted if there is a non-null date stored to be restricted by.

  9. #9
    SitePoint Member
    Join Date
    Apr 2006
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    WOW... Thank You,,, cranial-bore, longneck and r937. What cranial-bore wrote above works perfect. I was still having a problem though but found the solution. If the "from" and "to" GradDate or "from" and "to" DOB were left blank then it would be searching BETWEEN "nothing" and "nothing" so the output would only display the rows where the GradDate and DOB were "nothing"(side-by-side). Also If I was just searching by DOB then it would only display rows with a DOB that had no GradDate (you see the pattern). Because some data rows may contain the GradDate but not the DOB and visa versa..so in the actual search form on the DOB and GradDate dropdowns I changed the default selected "from" value to "0000-00-00" and the default selected "to" value to "9999-12-31" so if there was no selection made it would still search and display all the results BETWEEN 0000-00-00 AND 9999-12-31. THANKS GUYS!!!!!!!!!!!!!!


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
  •