0

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 */;
Improve this question
5
  • 1
    Provide table structure, example data.. Commented Jul 6, 2018 at 23:21
  • Are you sure the values are actually null and not just empty? It's hard to tell what's going on without your table structure and some sample data as Raymond pointed out. Commented Jul 6, 2018 at 23:30
  • I'm not really sure now actually. some rows have no value, empty. so it should be null, i am assuming. I want to provide some data, but there are many columns, and looking for a way to do it now. Commented Jul 6, 2018 at 23:33
  • ok, I included table structure and some data in the post now. I forgot to include Id, but it has no empty values, and so it can be excluded from this, I hope. Commented Jul 6, 2018 at 23:38
  • Your insert statement doesn't insert NULL. You would have to use NULL instead of ''. This way you have only strings of length 0, but not NULL. Commented Jul 7, 2018 at 0:18

3 Answers 3

Reset to default
1

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) |
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

I hate to say this, I tried your suggestion but the results are the same.
Edit your question, add this query (the version you actually ran) and it's results. Better yet, create a SQL Fiddle with sample data and post the link in your question.
Actually, I just looked at your schema and sample data. You have varchar columns and you are populating them with '' and empty string. That's not a null. It's a valid value for the column. You can either change the column types to integer, or change you insert statement and replace the '' with NULL to insert a null value.
See my updated answer. I've also created a SQL fiddle with corrected data in the INSERT statement from your question
I tried to change the column type, i get the "Incorrect integer value" error, it doesn't let me do it. That might be the reason, but still its default value is null. isn't it supposed to work anyway? i'll have to change the whole db then. and there are also some columns with alphanumeric characters which I can't change to INT.
|
0

Instead of 

IFNULL(D0c,0)

Try

REPLACE(Doc,NULL,0)
Improve this answer

2 Comments

should be REPLACE(Doc, NULL, 0) instead of 'NULL' which is a string..
You are correct, Just tested...sorry. IFNULL, as you have written, is working for me though.
0

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" ;
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.