Replacing null values with zeros in mysql

Question

I know similar questions asked before, and I checked most of them. but I can't get this query to work. I have several columns with null values, I want to display them as Zeros in the html table. but I can't get the result i want.

The query I make is this:

 $query = "SELECT Id, IFNULL(D0c,0), IFNULL(D1c,0), IFNULL(D2c,0), IFNULL(D3c,0) AS D0c, D1c, D2c, D3c FROM Findata Order by Id Desc Limit 100"; 

When I run the query I get these results:

 ['889', , 1, 4, ],['888', , 1, 2, ],['887', 1, 1, 3, 1],['886', 2, 2, 1, 2],['885', , 1, 2, ], etc

Instead of Zeros, still null results are displayed. I need a little help here please. thanks.

-- phpMyAdmin SQL Dump
-- version 4.6.4
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Jul 06, 2018 at 11:35 PM
-- Server version: 5.7.14
-- PHP Version: 5.6.25

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `Nexel`
--

-- --------------------------------------------------------

--
-- Table structure for table `temptable`
--

CREATE TABLE `temptable` (
  `D0c` varchar(5) DEFAULT NULL,
  `D1c` varchar(5) DEFAULT NULL,
  `D2c` varchar(5) DEFAULT NULL,
  `D3c` varchar(5) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `temptable`
--

INSERT INTO `temptable` (`D0c`, `D1c`, `D2c`, `D3c`) VALUES
('1', '2', '1', '1'),
('2', '1', '', '2'),
('2', '2', '1', ''),
('2', '2', '1', ''),
('1', '', '4', ''),
('', '2', '1', '2'),
('3', '', '1', '1'),
('', '2', '2', '1'),
('3', '1', '1', ''),
('2', '1', '', '2'),
('2', '2', '1', ''),
('1', '3', '1', ''),
('2', '', '2', '1'),
('', '2', '3', ''),
('1', '3', '1', ''),
('1', '2', '1', '1'),
('1', '3', '1', ''),
('2', '1', '2', ''),
('', '1', '2', '2'),
('1', '1', '2', '1'),
('1', '2', '1', '1'),
('3', '1', '', '1'),
('2', '2', '', '1'),
('2', '2', '1', ''),
('1', '', '4', ''),
('1', '4', '', ''),
('', '2', '', '3'),
('1', '', '3', '1'),
('2', '1', '2', ''),
('1', '2', '1', '1'),
('', '3', '1', '1'),
('2', '2', '1', ''),
('2', '1', '1', '1'),
('2', '1', '2', ''),
('2', '1', '2', ''),
('', '2', '1', '2'),
('', '1', '3', '1'),
('2', '1', '2', ''),
('', '1', '4', '');

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Answer 1

In case of empty string:

$query = "SELECT  Id, CASE when D0c like '' then '0' else D0c end as D0c, CASE when D1c like '' then '0' else D1c end as D1c, CASE when D2c like '' then '0' else D2c end as D2c, case when D3c like '' then '0' else D3c end as D3c FROM Mytable Order by Id Desc Limit 100" ;

Answer 2

See update below

Instead of IFNULL, try COALESCE():

SELECT 
    Id, 
    COALESCE(D0c,0) AS `FixedD0c`,
    COALESCE(D1c,0) AS `FixedD1c`,
    COALESCE(D2c,0) AS `FixedD2c`,
    COALESCE(D3c,0) AS `FixedD3c`,
    D0c,
    D1c, 
    D2c, 
    D3c 
FROM Findata 
Order by Id Desc 
Limit 100;

NOTE: BTW, in the query shown in your question, you seemed to be trying to assign column alias' all at once, but you merely renamed the results of the 4th column, and then included columns 2-4 again, without the IFNULLs. Your results from the query would not be as shown in your question, but would have had 7 columns in the output.

EDIT:

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE `Findata` (
  `D0c` varchar(5) DEFAULT NULL,
  `D1c` varchar(5) DEFAULT NULL,
  `D2c` varchar(5) DEFAULT NULL,
  `D3c` varchar(5) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `temptable`
--

INSERT INTO `Findata` (`D0c`, `D1c`, `D2c`, `D3c`) VALUES
('1', '2', '1', '1'),
('2', '1', NULL, '2'),
('2', '2', '1', NULL),
('2', '2', '1', NULL),
('1', NULL, '4', NULL),
(NULL, '2', '1', '2'),
('3', NULL, '1', '1'),
(NULL, '2', '2', '1'),
('3', '1', '1', NULL),
('2', '1', NULL, '2'),
('2', '2', '1', NULL),
('1', '3', '1', NULL),
('2', NULL, '2', '1'),
(NULL, '2', '3', NULL),
('1', '3', '1', NULL),
('1', '2', '1', '1'),
('1', '3', '1', NULL),
('2', '1', '2', NULL),
(NULL, '1', '2', '2'),
('1', '1', '2', '1'),
('1', '2', '1', '1'),
('3', '1', NULL, '1'),
('2', '2', NULL, '1'),
('2', '2', '1', NULL),
('1', NULL, '4', NULL),
('1', '4', NULL, NULL),
(NULL, '2', NULL, '3'),
('1', NULL, '3', '1'),
('2', '1', '2', NULL),
('1', '2', '1', '1'),
(NULL, '3', '1', '1'),
('2', '2', '1', NULL),
('2', '1', '1', '1'),
('2', '1', '2', NULL),
('2', '1', '2', NULL),
(NULL, '2', '1', '2'),
(NULL, '1', '3', '1'),
('2', '1', '2', NULL),
(NULL, '1', '4', NULL);

Query 1:

SELECT 
    COALESCE(D0c,0) AS `FixedD0c`,
    COALESCE(D1c,0) AS `FixedD1c`,
    COALESCE(D2c,0) AS `FixedD2c`,
    COALESCE(D3c,0) AS `FixedD3c`,
    D0c,
    D1c, 
    D2c, 
    D3c 
FROM Findata 
Limit 100

Results:

| FixedD0c | FixedD1c | FixedD2c | FixedD3c |    D0c |    D1c |    D2c |    D3c |
|----------|----------|----------|----------|--------|--------|--------|--------|
|        1 |        2 |        1 |        1 |      1 |      2 |      1 |      1 |
|        2 |        1 |        0 |        2 |      2 |      1 | (null) |      2 |
|        2 |        2 |        1 |        0 |      2 |      2 |      1 | (null) |
|        2 |        2 |        1 |        0 |      2 |      2 |      1 | (null) |
|        1 |        0 |        4 |        0 |      1 | (null) |      4 | (null) |
|        0 |        2 |        1 |        2 | (null) |      2 |      1 |      2 |
|        3 |        0 |        1 |        1 |      3 | (null) |      1 |      1 |
|        0 |        2 |        2 |        1 | (null) |      2 |      2 |      1 |
|        3 |        1 |        1 |        0 |      3 |      1 |      1 | (null) |
|        2 |        1 |        0 |        2 |      2 |      1 | (null) |      2 |
|        2 |        2 |        1 |        0 |      2 |      2 |      1 | (null) |
|        1 |        3 |        1 |        0 |      1 |      3 |      1 | (null) |
|        2 |        0 |        2 |        1 |      2 | (null) |      2 |      1 |
|        0 |        2 |        3 |        0 | (null) |      2 |      3 | (null) |
|        1 |        3 |        1 |        0 |      1 |      3 |      1 | (null) |
|        1 |        2 |        1 |        1 |      1 |      2 |      1 |      1 |
|        1 |        3 |        1 |        0 |      1 |      3 |      1 | (null) |
|        2 |        1 |        2 |        0 |      2 |      1 |      2 | (null) |
|        0 |        1 |        2 |        2 | (null) |      1 |      2 |      2 |
|        1 |        1 |        2 |        1 |      1 |      1 |      2 |      1 |
|        1 |        2 |        1 |        1 |      1 |      2 |      1 |      1 |
|        3 |        1 |        0 |        1 |      3 |      1 | (null) |      1 |
|        2 |        2 |        0 |        1 |      2 |      2 | (null) |      1 |
|        2 |        2 |        1 |        0 |      2 |      2 |      1 | (null) |
|        1 |        0 |        4 |        0 |      1 | (null) |      4 | (null) |
|        1 |        4 |        0 |        0 |      1 |      4 | (null) | (null) |
|        0 |        2 |        0 |        3 | (null) |      2 | (null) |      3 |
|        1 |        0 |        3 |        1 |      1 | (null) |      3 |      1 |
|        2 |        1 |        2 |        0 |      2 |      1 |      2 | (null) |
|        1 |        2 |        1 |        1 |      1 |      2 |      1 |      1 |
|        0 |        3 |        1 |        1 | (null) |      3 |      1 |      1 |
|        2 |        2 |        1 |        0 |      2 |      2 |      1 | (null) |
|        2 |        1 |        1 |        1 |      2 |      1 |      1 |      1 |
|        2 |        1 |        2 |        0 |      2 |      1 |      2 | (null) |
|        2 |        1 |        2 |        0 |      2 |      1 |      2 | (null) |
|        0 |        2 |        1 |        2 | (null) |      2 |      1 |      2 |
|        0 |        1 |        3 |        1 | (null) |      1 |      3 |      1 |
|        2 |        1 |        2 |        0 |      2 |      1 |      2 | (null) |
|        0 |        1 |        4 |        0 | (null) |      1 |      4 | (null) |

Answer 3

Instead of

IFNULL(D0c,0)

Try

REPLACE(Doc,NULL,0)