mysql select query with empty value

Question

I am facing one issue while issuing below query. I am not sure what am i doing wrong?

$start= date("y-m-d", strtotime($start));        
$end= date("y-m-d", strtotime($end));
//    $start = '2016-03-03';
//   $end= '2016-04-04';
if($jobtitle == ''){
    $jobtitle = true;
}
if($start == '' or $end == ''){
    $start = true;
    $end = true;
}
$result = $mysqli->prepare("SELECT * 
                            FROM postdata 
                            where id = ? 
                              and title = ? 
                              and (date between ? and ?) 
                            order by date desc");

$result ->bind_param("isss", $id, $title, $start, $end);
$result->execute();

Actually i have a form with 3 fields. 1 title and others 2 are start and end for date.

If user either enter value in title or put start and end date in the form. User can also put values in both filed. Once data is submitted by user then i want to perform search in database.

Please advise why my query is not working.

Simple code indentation not only makes the code easier to read it also makes debugging much easier — RiggsFolly
– RiggsFolly, Commented Apr 11, 2016 at 19:49
title = '%%' says the content of the title column must contain %% and only %%. You are not using a LIKE and that is the only syntax that responds to % characters — RiggsFolly
– RiggsFolly, Commented Apr 11, 2016 at 19:55

Igor Bukin · Accepted Answer · 2016-04-11 19:49:30Z

1

You simply re-bind these variables in your IF statement

if($start = '' or $end = '')

There must be boolean operators like === or ==

answered Apr 11, 2016 at 19:49

Igor Bukin

1,0068 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

RiggsFolly Over a year ago

Although that whole IF is a bit of nonsense if you look at it even with the correct usage of ==

Roxx Over a year ago

Applogies but = was typo. I already tried == but it is not returning any data.

Igor Bukin Over a year ago

Agree. The date() function won't return an empty string any way. The check should be before the transformation. However with '=' in IF statement it hasn't any chance :)

Roxx · Accepted Answer · 2016-04-11 20:29:21Z

I created three separate select queries in if condition and it resolves the issue. Here is the code which i am using at the moment.

 if($title != '' and $_POST['start'] == '' and $_POST['end'] == ''){
      $result = $mysqli->prepare("SELECT * FROM posting where id = ? and title like ? order by date desc");
      $result ->bind_param("is", $id, $title);
 }
 if($title == '' and $_POST['start'] != '' and $_POST['end'] != ''){
      $result = $mysqli->prepare("SELECT * FROM posting where id = ? and (date between ? and ?) order by date desc");
      $result ->bind_param("iss", $id, $start, $end);
            }
 if($title != '' and $_POST['start'] != '' and $_POST['end'] != ''){
      $result = $mysqli->prepare("SELECT * FROM posting where id = ? and title like ? and (date between ? and ?) order by date desc");
      $result ->bind_param("isss", $id, $title, $start, $end);
            }

Collectives™ on Stack Overflow

mysql select query with empty value

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related