Make th value NULL or if empty dont do the where

Question

I have now :

$inhoud = $_POST['inhoud'];
if($inhoud == "") {
    echo $inhoud;
    $inhoud = NULL;
    echo $inhoud;
}

$sql = 'SELECT DISTINCT bestanden.id AS id,
               bestanden.uploader AS uploader,
               bestanden.name AS name,
               bestanden.mime AS mime,
               bestanden.size AS size,
               bestanden.created AS created,
               zoeken.open_id AS open_id,
               zoeken.woord AS woord
        FROM bestanden
        LEFT JOIN zoeken ON bestanden.id = zoeken.open_id 
        WHERE zoeken.woord = "' . $inhoud . '" AND
              bestanden.name LIKE "%' . $_POST['naam'] . '%" AND
              bestanden.mime LIKE "%' . $_POST['formaat'] . '%"
              AND bestanden.created LIKE "%' . $_POST['datum'] . '%";';
$result = mysql_query($sql);

But when I search and $inhoud is empty, I got no results because they dont have an empty( " " ) (inhoud)space there. Is it possible to use an if or something in my mysql_query?

Try to use mysqli instead of mysql. – Lobo Dec 07 '12 at 14:00 — Lobo, Dec 07 '12 at 14:00

score 0 · Answer 1 · answered Dec 07 '12 at 13:52

    $sql = 'SELECT DISTINCT bestanden.id AS id, bestanden.uploader AS uploader, bestanden.name AS name, bestanden.mime AS mime, bestanden.size AS size, bestanden.created AS created, zoeken.open_id AS open_id, zoeken.woord AS woord FROM bestanden LEFT JOIN zoeken ON bestanden.id = zoeken.open_id 
    WHERE 1=1';
if(!empty($inhoud))
    $sql.=' AND zoeken.woord="' . $inhoud . '"';
$sql.=' AND bestanden.name LIKE "%' . $_POST['naam'] . '%" AND bestanden.mime LIKE "%' . $_POST['formaat'] . '%" AND bestanden.created LIKE "%' . $_POST['datum'] . '%"';
$result = mysql_query($sql);

score 0 · Answer 2 · answered Dec 07 '12 at 13:52

0

Use empty function

empty($inhound);

Look here: http://php.net/manual/en/function.empty.php

answered Dec 07 '12 at 13:52

Peter Adrian

279
1
5

score 0 · Answer 3 · answered Dec 07 '12 at 13:56

Maybe strlen() ?

if(strlen($inhoud)>0){
    $sql.=' AND zoeken.woord="' . $inhoud . '"';
    $sql.=' AND bestanden.name LIKE "%' . $_POST['naam'] . '%" AND
    bestanden.mime LIKE "%' .         $_POST['formaat'] . '%" AND 
    bestanden.created LIKE "%' . $_POST['datum'] . '%"';
    $result = mysql_query($sql);
 }else{
    //error fill in search term
 }

score 0 · Accepted Answer · edited May 25 '19 at 14:48

Please, don't use mysql_* functions in new code. They are no longer maintained and the deprecation process has begun on it. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

You can try with:

$inhoud = $_POST['inhoud'];
if ($inhoud == "") {
    echo $inhoud;
    $inhoud = NULL;
    echo $inhoud;
}

$sql = 'SELECT DISTINCT bestanden.id AS id,
               bestanden.uploader AS uploader,
               bestanden.name AS name,
               bestanden.mime AS mime,
               bestanden.size AS size,
               bestanden.created AS created,
               zoeken.open_id AS open_id,
               zoeken.woord AS woord
        FROM bestanden
        LEFT JOIN zoeken ON bestanden.id = zoeken.open_id 
        WHERE ';
if(!empty($inhoud)) {
    $sql .= 'zoeken.woord = "' . $inhoud . '" AND';
}
$sql .= 'bestanden.name LIKE "%' . $_POST['naam'] . '%" AND
         bestanden.mime LIKE "%' . $_POST['formaat'] . '%"
         AND bestanden.created LIKE "%' . $_POST['datum'] . '%";';

$result = mysql_query($sql);

score 0 · Answer 5 · answered Dec 07 '12 at 14:08

First of all, you shouldn't assume that $_POST[...] is set under any circumstances (unless you've already checked that it is set). Correct usage would be:

$inhoud = null:
if( isset( $_POST['inhound'])){
    $inhound = trim( $_POST['inhound']); // Trim to prevent strings like "    "
    if( strlen( $inhound) < 1){
        $inhound = null;
    }
}

Also note that mysql_* family is deprecated so rather use mysqli_* or PDO, having PDO example (using PDO::Prepare(), PDOStatement::bindParam() and PDOStatement::Execute()):

$sql = 'SELECT DISTINCT bestanden.id AS id, bestanden.uploader AS uploader,
            bestanden.name AS name, bestanden.mime AS mime,
            bestanden.size AS size, bestanden.created AS created,
            zoeken.open_id AS open_id, zoeken.woord AS woord
       FROM bestanden
       LEFT JOIN zoeken ON bestanden.id = zoeken.open_id 
       WHERE zoeken.woord=:inhound
           AND bestanden.name LIKE :naam
           AND bestanden.mime LIKE :formaat
           AND bestanden.created LIKE :date';

$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));

$sth->bindParam(':naam', isset( $_POST['naam']) ? '%' . $_POST['naam'] . '%', '%');
$sth->bindParam(':formaat', isset( $_POST['formaat']) ? '%'.$_POST['formaat'].'%', '%');
$sth->bindParam(':naam', isset( $_POST['date']) ? '%' . $_POST['date'] . '%', '%');
$sth->bindParam(':inhound', is_null( $inhound) ? '%' . $inhound . '%', '%');

$sth->execute();
$sth->fetchAll();

Make th value NULL or if empty dont do the where

5 Answers5