How do I get NULL into a MySQL integer column with PHP/MySQLi?

Question 1

I'm passing values into a PHP page through the URL and using them in queries with MySQLi. The problem is that an empty string gets converted to zero for an integer column, when in fact it needs to be NULL.

How do I get NULL into an integer column with PHP/MySQLi, from a param passed in the URL?

Update: Here's an example of how I'd use the incoming parameter and a prepared statement to do the insert. The one I need to be NULL on the DB is $specialtyType.

function InsertItem($itemID, $ownerID, $specialtyType)
{
    $this->insertItemStmt->bind_param("ssi", $itemID, $ownerID, $specialtyType);
    $this->insertItemStmt->execute();

If I add Jason's suggested line before the bind_param (setting up an intermediate variable and using it in the bind_param instead of $specialty_type), it winds up containing 0 after the execute().

Question 2

$int_value = empty($_GET['int_value']) ? NULL : (int)$_GET['int_value'];

Use $int_value for the column in your INSERT.

Question 3

This is one example where using prepared statements really saves you some trouble.

In MySQL, in order to insert a null value, you must specify it at INSERT time or leave the field out which requires additional branching:

INSERT INTO table2 (f1, f2)
  VALUES ('String Value', NULL);

However, if you want to insert a value in that field, you must now branch your code to add the single quotes:

INSERT INTO table2 (f1, f2)
  VALUES ('String Value', 'String Value');

Prepared statements automatically do that for you. They know the difference between string(0) "" and null and write your query appropriately:

$stmt = $mysqli->prepare("INSERT INTO table2 (f1, f2) VALUES (?, ?)");
$stmt->bind_param('ss', $field1, $field2);

$field1 = "String Value";
$field2 = null;

$stmt->execute();

It escapes your fields for you, makes sure that you don't forget to bind a parameter. There is no reason to stay with the mysql extension. Use mysqli and it's prepared statements instead. You'll save yourself a world of pain.

Question 4

<?php
$sql = new mysqli('127.0.0.1','root','Qwert12345','plot_io_db');
//echo $sql->query('Select * From players');
?>

It will work. Just remove port from localhost (127.0.0.1)

Question 5

Use COALESCE to avoid that outcome.

SELECT COALESCE(SUM(column),0)
FROM   table
WHERE  ...

To see it in action, please see this sql fiddle: http://www.sqlfiddle.com/#!2/d1542/3/0

More Information:

Given three tables (one with all numbers, one with all nulls, and one with a mixture):

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE foo
(
  id    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  val   INT
);

INSERT INTO foo (val) VALUES
(null),(1),(null),(2),(null),(3),(null),(4),(null),(5),(null),(6),(null);

CREATE TABLE bar
(
  id    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  val   INT
);

INSERT INTO bar (val) VALUES
(1),(2),(3),(4),(5),(6);

CREATE TABLE baz
(
  id    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  val   INT
);

INSERT INTO baz (val) VALUES
(null),(null),(null),(null),(null),(null);

Query 1:

SELECT  'foo'                   as table_name,
        'mixed null/non-null'   as description,
        21                      as expected_sum,
        COALESCE(SUM(val), 0)   as actual_sum
FROM    foo
UNION ALL

SELECT  'bar'                   as table_name,
        'all non-null'          as description,
        21                      as expected_sum,
        COALESCE(SUM(val), 0)   as actual_sum
FROM    bar
UNION ALL

SELECT  'baz'                   as table_name,
        'all null'              as description,
        0                       as expected_sum,
        COALESCE(SUM(val), 0)   as actual_sum
FROM    baz

Results:

| TABLE_NAME |         DESCRIPTION | EXPECTED_SUM | ACTUAL_SUM |
|------------|---------------------|--------------|------------|
|        foo | mixed null/non-null |           21 |         21 |
|        bar |        all non-null |           21 |         21 |
|        baz |            all null |            0 |          0 |

Question 6

My take on your issue is to construct a derived table of the neighborhoods values you hope to find, and LEFT JOIN to the actual table:

   SELECT x.neighborhoods,
          COALESCE(mt.incidents, 0) AS incidents
     FROM (SELECT 'Old Town' AS neighborhoods
             FROM DUAL
           UNION ALL
           SELECT 'New Town'
             FROM DUAL) x
LEFT JOIN MYTABLE mt ON mt.neighborhoods = x.neighborhoods

How do I get NULL into a MySQL integer column with PHP/MySQLi?

Answers

php,mysql,mysqli,null,integer