How to add a array variable parameter in mysql query?(PHP)

Question

I need to do a query like:

  UPDATE screening
                 SET maileddate = NOW(),
                     reference = '{$reference[$user_id]}'
                     WHERE user_id IN(....)

And I want to do the judgement, when reference[$user_id] is empty, reference[$user_id] = NULL. My code is :

    if(empty($reference[$user_id]) || $reference[$user_id] == ''){
        $reference[$user_id] = NULL;
     }

But when I execute the query, if $reference[$user_id] is empty, the value of reference in database is empty but not null. What should I do to make it null? thanks

The last part is unclear. Could you say it another way?

Jason McCreary
– Jason McCreary

2011-09-23 20:13:40 +00:00
Commented Sep 23, 2011 at 20:13 — Jason McCreary
– Jason McCreary, Commented Sep 23, 2011 at 20:13

GolezTrol · Accepted Answer · 2011-09-23 20:32:34Z

2

You may need to pass NULL as a string to MySQL if the variable is empty. Use a different variable to hold the possibly NULL contents and quote the non-null contents. Don't forget to escape it otherwise:

$refid = empty($reference['user_id']) ? "NULL" : "'" . mysql_real_escape_string($reference['user_id']) . "'";

UPDATE screening SET maileddate = NOW(), reference = '{$refid}'
      WHERE user_id IN(....)

edited Sep 23, 2011 at 20:32

GolezTrol

116k19 gold badges186 silver badges215 bronze badges

answered Sep 23, 2011 at 20:18

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

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

Comments

Michael · Accepted Answer · 2011-09-23 20:33:46Z

0

Just make it a string saying null. If it's null $reference[$user_id] = 'null';

Oh, also with the query you should be using reference IS null {$reference[$user_id]} instead of the equals sign

edited Sep 23, 2011 at 20:33

answered Sep 23, 2011 at 20:16

Michael

7633 silver badges5 bronze badges

3 Comments

Alex Hu Over a year ago

I got the string value 'null' in database, not NULL

Michael Over a year ago

Ohhh, that changes everything. Then just do the if statement to say if it's null set it to 'null' then keep `reference = {$reference[$user_id]}'

Michael Over a year ago

wait, now I'm confused... do you want to save the string 'null' into the db or save the value NULL into the db?

gview · Accepted Answer · 2011-09-23 20:39:47Z

0

$reference = 'reference = ';
$reference .= ' empty($reference[$user_id])  ? 'NULL' : "'". mysql_real_escape_string($reference[$user_id]) . "'";

$query = "UPDATE screening
      SET maileddate = NOW(),
      $reference       
      WHERE user_id IN(....)";

answered Sep 23, 2011 at 20:39

gview

15.5k3 gold badges53 silver badges56 bronze badges

Comments

jprofitt · Accepted Answer · 2011-09-23 21:11:05Z

0

Without seeing the rest of your code, my guess is it has something to do with reference = '{$reference[$user_id]}' having {$reference[$user_id]} in single quotes. MySQL is going to see whatever is in there as what should be in the database. So if $reference[$user_id] prints out as nothing (because it's NULL), that bit of your query will be reference = '' rather than reference = NULL. You need to use the keyword NULL rather than the actual value NULL for the variable you use in the query.

As an example:

$query = "UPDATE screening " . 
             "SET maileddate = NOW(), " . 
                 "reference = " . ($reference[$user_id] === NULL ? 'NULL ' : "'{$reference[$user_id]}' ") . 
                 "WHERE user_id IN(....)";

edited Sep 23, 2011 at 21:11

answered Sep 23, 2011 at 20:17

jprofitt

11k4 gold badges38 silver badges46 bronze badges

2 Comments

Alex Hu Over a year ago

Could you give me an example of what to do?

jprofitt Over a year ago

Edited my answer with an example

Scoutman · Accepted Answer · 2011-09-23 20:40:01Z

-1

Try this:

$user_id = empty($reference[$user_id]) ? 'NULL' : $reference[$user_id];
$sql = "UPDATE screening
                 SET maileddate = NOW(),
                     reference = " . $user_id . "
                     WHERE user_id IN(....)";

edited Sep 23, 2011 at 20:40

answered Sep 23, 2011 at 20:18

Scoutman

1,63012 silver badges20 bronze badges

1 Comment

Michael Berkowski Over a year ago

This will set the reference column to an empty string rather than NULL

Collectives™ on Stack Overflow

How to add a array variable parameter in mysql query?(PHP)

5 Answers 5

Comments

3 Comments

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

3 Comments

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related