Cant use this query in php even though it works inline on my database

Question

I am trying to handle if either NULL or a datetime object is passed to my handler while still writing NULL to the database and not just 00000000

I can get it to work if I parse the stop_date first and then depending on what it is, send it through 2 entirely different queries, but I want to be able to do it in one query either way

if(strtolower($stop_date) != 'null'){
            $stop = date("Y-m-d H:i:s", strtotime($stop_date));
            if($stop <= date('Y-m-d H:i:s')){
                die('No stop date was entered for this appointment. Recurring appointments must have a stop date or marked "NULL" if they do not have a stop date');
            }
        }   
        else{
            $stop = NULL;
        } 
        $db->query("
        UPDATE appt_recurring
        SET hc_id = :hc_id, note = :note, appt_type_id = :appt_type_id, start_date = :start_date, end_date = :end_date, 
        stop_date = (
            CASE
                WHEN
                    ".$stop." != NULL
                THEN
                    :stop_date
                ELSE
                    $stop
            END
        ), 
        frequency = :frequency, frequency_type = :frequency_type, frequency_data = :frequency_data
        WHERE id = :id
        ");

        $db->bind(':id', $id );
        $db->bind(':hc_id', $hc_id );
        $db->bind(':note', $note );
        $db->bind(':appt_type_id', $appt_type_id );
        $db->bind(':start_date', $start_date->format('Y-m-d H:i:s'));
        $db->bind(':end_date', $end_date->format('Y-m-d H:i:s'));
        if(strtolower($stop_date) != 'null'){
            $db->bind(':stop_date', $stop);
        } 
        $db->bind(':frequency', $frequency );
        $db->bind(':frequency_type', $frequency_type );
        $db->bind(':frequency_data', $frequency_data );

This provides nothing but syntax errors and in some cases with some tweaking a parameter error. Please help!

Seems like you could do without the conditional in the UPDATE statement. Simply pass a `null` or a datetime string. Is the issue with the table design? The column allows nulls yes? — ficuscr, Aug 29 '19 at 20:57
Also, when $stop_date is a date, it should be in quotes in the query. — Jonnix, Aug 29 '19 at 20:58
@KobyCampbell I didn't ask which DBMS, I asked which DB PHP library. — Jonnix, Aug 29 '19 at 20:59
So summing up, there appear to be (at least) 2 mentioned issues. 1) concat-ing a PHP value of null doesn't add anything to the string, i.e. you don't get `null != NULL`, you get `!= NULL` which will be invalid. 2) dates in query should be in quotes. — Jonnix, Aug 29 '19 at 21:02
@Jonnix I dont know atm but I just tried it with IS NULL language and that doesnt work either. the != NULL works inline when I am directly in my databse so I dont know why this language isnt working when I pass it to that database from my php file... — Koby Campbell, Aug 29 '19 at 21:03
Is `$db` your own library? Normally `query(` can't be used with prepared statements and `bind` is `bind_param` or `bindparam`. — user3783243, Aug 29 '19 at 21:04
@Jonnix and I just tried using quotes to concat the date variable I am using and still now go — Koby Campbell, Aug 29 '19 at 21:05
Yeah $db is just the name of my variable I used to get an instance of the database to work from — Koby Campbell, Aug 29 '19 at 21:06
Please add the definition of that so we can see what connection you are working with. — user3783243, Aug 29 '19 at 21:06

ficuscr · Accepted Answer · 2019-08-29T21:32:54.583

3

A few things jump out. For starters WHEN ".$stop." != NULL should be WHEN ".$stop." IS NOT NULL. And as @Jonnix highlighted, evaluating like that on the value being assigned makes no sense. The SQL would read as WHEN != NULL. Just a note for if you are evaluating a null state field in the future.

Really there should be no need for logic like that in the update statement.

You should simply assign a null value to the variable you will bind to the end_date.

Can you not force a null by using:

$db->bind(':end_date', NULL);

If you can't get that working you need to be sure the column allows null values (DESC appt_recurring;).

So, in summary, aim for one if to check this, not one up top and one later.


if(strtolower($stop_date) != 'null'){
  $stop = date("Y-m-d H:i:s", strtotime($stop_date));
  if($stop <= date('Y-m-d H:i:s')){
    die('No stop date was entered for this appointment. Recurring appointments must have a stop date or marked "NULL" if they do not have a stop date');
  }
  $endDate = $stop->format('Y-m-d H:i:s');
} else {
  $endDate = NULL;
}

...

$db->bind(':end_date', $endDate);

edited Aug 29 '19 at 21:32

answered Aug 29 '19 at 21:04

ficuscr

6,975
2
32
52

1

But if $stop is PHP null, that will produce `WHEN != NULL`. – Jonnix Aug 29 '19 at 21:06
Yes Jonnix is correct. One sec playing with a SQL browser. – ficuscr Aug 29 '19 at 21:06
A +1 for working out the value for end_date in PHP and just binding the value idea instead of the CASE stuff. – Jonnix Aug 29 '19 at 21:10
Yeah definetely binding it conditionally was way easier than trying to get the CASE statement working in the php query haha thanks guys! – Koby Campbell Aug 29 '19 at 21:17
@ficuscr is there a way to do this check on the GET request so that I can clean it up? right now the user passes in a string "null" to signify that it is NULL but then I have to parse that query in both the handler and system files to assign it NULL as a value – Koby Campbell Aug 29 '19 at 21:30
1

@KobyCampbell Guess I'm not clear on what the handler vs system files are. Overriding a variable like that, either with a string 'null' or an actual `null` makes total sense. You might just need to include a conditional then wherever you have to evaluate it. Common pattern, why we use [ternary](https://stackoverflow.com/questions/17981723/how-to-write-a-php-ternary-operator) often in scenarios like this, more compact / readable. Would have to see all the code really to weigh in more. – ficuscr Aug 29 '19 at 21:37

Cant use this query in php even though it works inline on my database

1 Answers1