NULL with MySQL, PHP, JSON and JavaScript

Question

I often have some JavaScript which receives data from a server's database and presents it on the page.

$.getJSON( 'ajax.php',{id:$('bla').val()}, function(json) {
    $('#myClone').clone().removeAttr('id').text('json.a').appendTo(whatever)
});

ajax.php

$sql='SELECT a,b,c FROM t WHERE id=?';
$stmt = $conn->prepare($sql);
$data=array($_GET['id']);
$stmt->execute($data);
header('Content-Type: application/json;');
echo(json_encode($stmt->fetchAll(PDO::FETCH_ASSOC)));

A problem arises when the database contains NULL for a given column, and the associated JSON includes null without quotes, HTTP transmits the JSON string still with null without quotes, but JavaScript/jQuery appears to convert it into a string "null", and null is displayed on the page instead of the desired empty string.

How is this best rectified? Should be be done so server-side or client-side? If server-side, should it be done at the database level using something like SELECT IFNULL(a,""), or the PHP application level?

@JohnHascall That's why I included the situation in the question. Elements which contain text are appended to another element. — user1032531
– user1032531, Commented Dec 31, 2015 at 18:30
This sounds like a client-side bug to me, so my first attempt would be to resolve it in the client. — Siguza
– Siguza, Commented Dec 31, 2015 at 18:32
@user1032531 The situation is usually more complex. How much control do you have? For example, when I control the DB schema, I seek a design which has no nulls. — John Hascall
– John Hascall, Commented Dec 31, 2015 at 18:32
@JohnHascall I have full control. I usually only enforce no-nulls for FK columns and other special cases. — user1032531
– user1032531, Commented Dec 31, 2015 at 18:34

azoundria · Accepted Answer · 2015-12-31 18:49:16Z

1

Doing it at the database level will be the best. The only reason not to do it at the database level would be if you can foresee any situation in which another user of that particular database query or a higher level would need to distinguish between an empty string "" and null.

Doing the query at the JavaScript display level would potentially be a bad idea because there is the possibility to forget to add this check or code in a particular output location, and null would be displayed there. In addition, extra bytes are transmitted at every stage from the database to display, and the end user's CPU is processing the request, rather than your really fast MySQL, which will affect load time.

Consider a scenario with 1 million entries. You will do 1 million checks in MySQL (a language optimized for efficiency) instead of 1 million transmissions of 'null' from the database to PHP (an extra 2 million bytes), 1 million transmissions of 'null' from PHP to the page (another extra 2 million bytes), and 1 million checks at the client side in JavaScript (a language not known for efficiency).

answered Dec 31, 2015 at 18:49

azoundria

1,0252 gold badges9 silver badges28 bronze badges

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

1 Comment

user1032531 Over a year ago

Let me mull it over. Thank you.

Barmar · Accepted Answer · 2015-12-31 18:37:56Z

0

Use a conditional expression:

$.getJSON( 'ajax.php',{id:$('bla').val()}, function(json) {
    $('#myClone').clone().removeAttr('id').text(json.a === null ? '' : json.a).appendTo(whatever)
});

But I would probably go with the server-side solution you mentioned in the question.

edited Dec 31, 2015 at 18:37

answered Dec 31, 2015 at 18:30

Barmar

789k57 gold badges554 silver badges669 bronze badges

4 Comments

user1032531 Over a year ago

Thanks Barmar, I take it you typically recommend doing it client-side. I would rather use the user's resources than the server's so agree it this regard. Didn't know if there was a way to minimize the server script. Guess a function could be used...

John Hascall Over a year ago

Another consideration is how many clients consume this data. If you "fix" it at the server, all clients (current and future) are "fixed".

user1032531 Over a year ago

May I ask why you would go with a server-side solution?

Barmar Over a year ago

I generally like to canonicalize data as early in the process as possible.

Collectives™ on Stack Overflow

NULL with MySQL, PHP, JSON and JavaScript

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related