CODEWITHSUNDEEP
phpmysql
Jalavahini Technologies
Jalavahini Technologies

Reputation: 5

mysql query to fetch details of users between specific dates

$query="SELECT DISTINCT ind_id,indentdate,submitted_by 
        FROM raiseindent 
        where submitted_by='$nm' 
          AND indentdate BETWEEN '$fromDate' AND '$toDate' 
          OR indentdate BETWEEN '$fromDate' AND '$toDate' 
          OR submitted_by='$nm'";

here $nm is username

I am not getting any errors, this query fetching records if I use only $nm (User Name) or between two dates

If I use to serach specific user name and two dates it is fetching all users records between selected dates

I need specific only the results from entered user name in username field to serach

Upvotes: 0

Views: 139

Answers (2)

RiggsFolly
RiggsFolly

Reputation: 94642

Try this

$query="SELECT DISTINCT ind_id,indentdate,submitted_by 
        FROM raiseindent 
        where submitted_by='$nm' 
          AND indentdate BETWEEN '$fromDate' AND '$toDate'";

I dont think it need to be any more complicated than this.

Or if it is possible for $nm to be empty or the dates to be empty then do those tests in PHP and dynamically build the query accordingly rather than trying to get the SQL to do it all for you

$sql = 'SELECT DISTINCT ind_id,indentdate,submitted_by 
    FROM raiseindent 
    WHERE ';


if ( !empty($nm) && $nm != 'ALL' ) {
    $sql .= "submitted_by='$nm' AND ";
}
if ( !empty($fromDate) && !empty($toDate) ) {
    $sql .= " indentdate BETWEEN '$fromDate' AND '$toDate' ";
}
$sql = rtrim($sql, 'AND ');

Upvotes: 1

Rahul
Rahul

Reputation: 77866

Though it's already answered; for a note try using >= and <= to have the exact start and end date inclusive, instead BETWEEN operator like

SELECT DISTINCT ind_id,indentdate,submitted_by 
        FROM raiseindent 
        where submitted_by='$nm' 
          AND (indentdate >= '$fromDate' AND indentdate <= '$toDate');

Upvotes: 0

Related Questions