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 6 of 6
  1. #1
    Regular Coder MaDmiX's Avatar
    Join Date
    Feb 2012
    Location
    Charlotte, NC
    Posts
    160
    Thanks
    40
    Thanked 0 Times in 0 Posts

    MySQL Date range or IS NULL

    Hi All,

    I have a form that populates search criteria in a query query. I want the user to be able to select a date range if they want to but also be able to use other search criteria and not use the range.

    I have used IS NULL before but that was in MS Access. It doesn't seem to be working here but I am probably not using it correctly. I am getting the error "Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given..." so it looks like the query is not running. How would I do this in MySQL? Here is the query:

    PHP Code:
        $query ="SELECT *
                 FROM     tblequipissues
                 WHERE     Location LIKE '%$Location%' AND
                        LocationNum LIKE '%$LocationNum%' AND
                        EquipType LIKE '%$EquipType%' AND
                        UnitNo LIKE '%$EquipNum%' AND
                        (IssueDate >= '$FromIssueDate' OR '$FromIssueDate' IS NULL) AND 
                          (IssueDate <= '$ToIssueDate' OR '$ToIssueDate' IS NULL)
                        FixedDate LIKE '%$FixedDate%' AND
                        EnteredBy LIKE '%$EnteredBy%' AND
                        FixedBy LIKE '%$Assigned%'"

    Thanks!

    Ken

  • #2
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,133
    Thanks
    12
    Thanked 332 Times in 328 Posts
    print your error message using mysql_error() then you know what the DB complains about.

    print your query, then it should become obvious. an empty string is never null, it’s still a string.
    Last edited by Dormilich; 09-07-2013 at 08:13 AM.
    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer

  • Users who have thanked Dormilich for this post:

    MaDmiX (09-08-2013)

  • #3
    Regular Coder MaDmiX's Avatar
    Join Date
    Feb 2012
    Location
    Charlotte, NC
    Posts
    160
    Thanks
    40
    Thanked 0 Times in 0 Posts
    Thanks for your reply Dormilich. I will try mysql_error() function when I get to the office tonight. How would I print my query? Also, based on your advice I will try something like :

    PHP Code:
    (IssueDate >= '$FromIssueDate' OR '$FromIssueDate' ""
    I will let you know how it works out.

    Thanks,

    Ken

  • #4
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,133
    Thanks
    12
    Thanked 332 Times in 328 Posts
    How would I print my query?
    echo $sql;
    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer

  • #5
    Regular Coder MaDmiX's Avatar
    Join Date
    Feb 2012
    Location
    Charlotte, NC
    Posts
    160
    Thanks
    40
    Thanked 0 Times in 0 Posts
    Hi Dormilich,

    I got it working. This helped me out alot; "an empty string is never null, it’s still a string". I realized once I got the query to pull records that I had to make some further adjustments to the date variables to get them to include the correct "To" date in the results. Here is the working script:

    PHP Code:
    <?php require_once("../includes/connection.php"); ?>
    <?php 
    require_once("../includes/functions.php"); ?>
    <?php
        
    if(isset($_POST['FromIssueDate'])) {$FromIssueDate $_POST['FromIssueDate'];}
        if(isset(
    $_POST['ToIssueDate']) && $_POST['ToIssueDate'] !='') {$ToIssueDate $_POST['ToIssueDate'].' 23:59:59';}else{$ToIssueDate='';}
        if(isset(
    $_POST['FromFixedDate'])) {$FromFixedDate $_POST['FromFixedDate'];}
        if(isset(
    $_POST['ToFixedDate']) && $_POST['ToFixedDate'] !='') {$ToFixedDate $_POST['ToFixedDate'].' 23:59:59';}else{$ToFixedDate='';}
        if(isset(
    $_POST['FixedDate'])) {$FixedDate $_POST['FixedDate'];}
        if(isset(
    $_POST['Location'])) {$Location $_POST['Location'];}
        if(isset(
    $_POST['LocationNum'])) {$LocationNum $_POST['LocationNum'];}
        if(isset(
    $_POST['EquipType'])) {$EquipType $_POST['EquipType'];}
        if(isset(
    $_POST['EquipNum'])) {$EquipNum $_POST['EquipNum'];}
        if(isset(
    $_POST['EnteredBy'])) {$EnteredBy $_POST['EnteredBy'];}
        if(isset(
    $_POST['Assigned'])) {$Assigned $_POST['Assigned'];}
    ?>

    <?php

        $query 
    ="SELECT *
                 FROM     tblequipissues
                 WHERE     Location LIKE '%$Location%' AND
                        LocationNum LIKE '%$LocationNum%' AND
                        EquipType LIKE '%$EquipType%' AND
                        UnitNo LIKE '%$EquipNum%' AND
                        (IssueDate >= '$FromIssueDate' OR '$FromIssueDate' = '') AND 
                          (IssueDate <= '$ToIssueDate' OR '$ToIssueDate' = '') AND
                        (FixedDate >= '$FromFixedDate' OR '$FromFixedDate' = '') AND 
                          (FixedDate <= '$ToFixedDate' OR '$ToFixedDate' = '') AND
                        EnteredBy LIKE '%$EnteredBy%' AND
                        FixedBy LIKE '%$Assigned%'"
    ;

        if (!
    $qryresult=mysql_query($query)){
            exit(
    mysql_error());
        }

        echo 
    "<table width='100%' border='1px' cellpadding='0' cellspacing='0' class='sortable'>";
        echo 
    "<tr>
                <th width='15%'>Issue Date:</th>
                <th width='20%'>Entered by:</th>
                <th width='15%'>Location:</th>
                <th width='15%'>Equipment:</th>
                <th width='20%'>Assigned to:</th>
                <th width='10%'>Fixed Date:</th>
                <th width='5%'>Status:</th>
            </tr>"
    ;    
        while(
    $row mysql_fetch_assoc($qryresult)) {
            echo 
    "<tr onclick=\"location.href='search-ticket-details.php?tblEquipIssuesID=".$row['tblEquipIssuesID']."';\">
                    <td>"
    .$row['IssueDate']."</td>
                    <td>"
    .$row['EnteredBy']."</td>
                    <td>"
    .$row['Location']." ".$row['LocationNum']."</td>
                    <td>"
    .$row['EquipType']." ".$row['UnitNo']."</td>
                    <td>"
    .$row['FixedBy']."</td>
                    <td>"
    .$row['FixedDate']."</td>
                    <td>"
    ;

                    if (
    $row['CloseTicket']==1){
                        echo 
    "Closed";
                    }else{
                        echo 
    "Open";
                    }

                    echo 
    "</td></tr>";
        }
        echo 
    "</table>";

    ?>

    <?php mysql_close($dbConnection); ?>
    Thank again,

    Ken

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    Although much of the discussion here is PHP, the problem is the SQL, so I'm going to move this into the MySQL forum instead.
    For reference, so long as all your variables are correct (the result of a query call is the variable provided to the fetch), than this error always means the same thing: "Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given..." which is that your query has failed. That is why you see the or die(mysql_error()); so often as its a quick and easy fail for the query itself.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 


  •  

    Posting Permissions

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