SQL和DataTables返回数据库的值

Question

Now before I ask I KNOW this is WAY OVER MY HEAD! I have no idea how to even begin editing this code. I have been DUMPED in it by some people I was working with and have been left to finish a project by myself! I have the code below and I would like to add in there

AND WHERE fieldname IS NOT NULL

Now for the original code where it needs to be added!

$sWhere = "";
if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" )
{
    $sWhere = "WHERE (";
    for ( $i=0 ; $i<count($aColumns) ; $i++ )
    {
        $sWhere .= "`".$aColumns[$i]."` LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
    }
    $sWhere = substr_replace( $sWhere, "", -3 );
    $sWhere .= ')';
}

/* Individual column filtering */
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
    if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
    {
        if ( $sWhere == "" )
        {
            $sWhere = "WHERE ";
        }
        else
        {
            $sWhere .= " AND ";
        }
        $sWhere .= "`".$aColumns[$i]."` LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%' ";
    }
}

I have NO IDEA where to even start. I hope someone can help, thanks in advance for baring with me!

///////// UPDATE / UPDATE / UPDATE / UPDATE / UPDATE/////////

Ok so I have found that the $sWhere only activates when a search field is populated! I need it to run on the data when it is initially pulled out and then still be active even when the search field is populated. What I have below is the SELECT query where a new $sQuery can be defined. I just need help to construct it to make sure it only pulls data WHERE fieldname IS NOT NULL. I have tried the obvious things with no luck. I am probably missing something fairly obvious. Thanks again for any help!!

/*
     * SQL queries
     * Get data to display
     */
    $sQuery = "
        SELECT SQL_CALC_FOUND_ROWS `".str_replace(" , ", " ", implode("`, `", $aColumns))."`
        FROM   $sTable
        $sWhere
        $sOrder
        $sLimit
        ";
    $rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());

    /* Data set length after filtering */
    $sQuery = "
        SELECT FOUND_ROWS()
    ";
    $rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
    $aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
    $iFilteredTotal = $aResultFilterTotal[0];

    /* Total data set length */
    $sQuery = "
        SELECT COUNT(`".$sIndexColumn."`)
        FROM   $sTable
    ";
    $rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
    $aResultTotal = mysql_fetch_array($rResultTotal);
    $iTotal = $aResultTotal[0];


    /*
     * Output
     */
    $output = array(
        "sEcho" => intval($_GET['sEcho']),
        "iTotalRecords" => $iTotal,
        "iTotalDisplayRecords" => $iFilteredTotal,
        "aaData" => array()
    );

    while ( $aRow = mysql_fetch_array( $rResult ) )
    {
        $row = array();
        for ( $i=0 ; $i<count($aColumns) ; $i++ )
        {
            if ( $aColumns[$i] == "version" )
            {
                /* Special output formatting for 'version' column */
                $row[] = ($aRow[ $aColumns[$i] ]=="0") ? '-' : $aRow[ $aColumns[$i] ];
            }
            else if ( $aColumns[$i] != ' ' )
            {
                /* General output */
                $row[] = $aRow[ $aColumns[$i] ];
            }
        }
        $output['aaData'][] = $row;
    }

    echo json_encode( $output );
?>

Answer 1

So this should be the final answer (me hopes:-))

Nah, another update:

$sWhere = "";
if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" )
{
    $sWhere = "WHERE (";
    for ( $i=0 ; $i<count($aColumns) ; $i++ )
    {

        $sWhere .= "`".$aColumns[$i]."` LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
    }
    $sWhere = substr_replace( $sWhere, "", -3 );
    $sWhere .= ') AND active IS NOT NULL';
}

/* Individual column filtering */
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
    if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
    {
        if ( $sWhere == "" )
        {
            $sWhere = "WHERE ";
        }
        else
        {
            $sWhere .= " AND ";
        }

        $sWhere .= "`".$aColumns[$i]."` LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%' ";
    }
}
if ( $sWhere != "" )
{
    $sWhere .= " AND active IS NOT NULL ";
}else{
    $sWhere = " WHERE active IS NOT NULL ";
}

Note that two things have changed:

The first query generator is for fulltext search over all fields, so it encapsulates the query in brackets and separates fields with an OR.

I added line 11: $sWhere .= ') AND active IS NOT NULL';

The second query generator is for individual column filtering which adds an LIKE clause to each of the specified columns. This where clause can be empty, so we need to check this before concatenating more parameters.

Updated: I added line 31-36:

if ( $sWhere != "" )
{
    $sWhere .= " AND active IS NOT NULL ";
}else{
    $sWhere = " WHERE active IS NOT NULL ";
}

Now, with this update no rows with active is NULL will be displayed anymore. No matter if if you do a fulltext search, individual column filtering or just init the table. Maybe you should check if the total count is displayed correct. Are we there yet???

What you need to do know is to replace the active db-field with the one you want to ignore (Carers_Mod_Date) if it's NULL. You can add more excludes in these two lines separated with an AND.

Last tips: Always use some spaces around your concatenations because mysql will not mind to much spaces, but missing ones may produce an error.