php按用户过滤

Question

ive got list of reports. for default, the report list will be showing all reports without filtering. when drop down filter click, it will filter the result by name. anyidea how to fix it?

function getReportSingleMonth($month, $year, $id_user=NULL) {
   $month = $db->real_escape_string($month);
   $year = $db->real_escape_string($year);
   $db->query("SELECT * FROM user WHERE MONTH(date)='$month' AND YEAR(date)='$year' AND id_user='$id_user'");
}

the html part:

<form method="post" name="report_filter" action="<?= $_SERVER['PHP_SELF'];?>?report&month=<?= $_GET['month'];?>&year=<?= $_GET['year'];?>">
<div align="right"><select name="user_name" onchange="report_filter.submit();"><option value="--">Filter by:</option><option value="1">Andi</option>M<option value="2">Jenny</option><select></div>    
<? if(isset($_POST['user_name'])):
    $admin->getReportSingleMonth($_GET['month'], $_GET['year'], $_POST['user_name'])     
else :
    $admin->getReportSingleMonth($_GET['month'], $_GET['year']);
endif;
?>
</form>

Answer 1

In older versions of MySQL you can't put quotes around integer values. Try switching this line:

$db->query("SELECT * FROM user WHERE MONTH(date)='$month' AND YEAR(date)='$year'" AND id_user=$id_user");

Looking again, I noticed that you're not always going to pass a user_id. With that in mind the function should be changed:

function getReportSingleMonth($month, $year, $id_user=NULL) {
    $month = $db->real_escape_string($month);
    $year = $db->real_escape_string($year);
    $query = "SELECT * FROM user WHERE MONTH(date)='$month' AND YEAR(date)='$year'";
    if ( is_int( $id_user ) ) {
        $query .= ' and id_user=' . $id_user;
    }
    $db->query( $query );
}

Now the id_user part of the query is only added if it was passed to the function.

I also recommend using sprintf