Return something in the query when there are no records

Question

Return something in the query when there are no records

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I'm making adjustments to a graphics system, basically there is a query in the database to bring values and popular the chart, but when there is no specific record does not return anything from the database and the graph breaks.

I would like that when there was no record at least it would bring the fields with zeroed values, is it possible?

Note: I use MySQL. The graphs are in JS, after the query I transform it into JSON for the chart to consume.

Example: select nome from clientes

If you have a record and I convert to JSON it will look like this:

{nome: fulano, nome: ciclano}

But if it does not register, it looks like this:

{}

And I'd like it at least:

{nome: ""}

But I would only be interested if it was a bank solution, I do not know if it is possible.

mysql sql javascript

asked by anonymous 23.01.2017 / 18:30

2 answers

Change equal titles Method call inside the JSP

score 1 · Answer 1

You can make an IF within your SELECT How did you not post the fields? follow this example I hope I have helped.

TABLE:

+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+
| book_id | book_name                           | isbn_no     | cate_id | aut_id | pub_id | dt_of_pub  | pub_lang | no_page | book_price |
+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+
| BK001   | Introduction to Electrodynamics     | 0000979001  | CA001   | AUT001 | P003   | 2001-05-08 | English  |     201 |      85.00 |
| BK002   | Understanding of Steel Construction | 0000979002  | CA002   | AUT002 | P001   | 2003-07-15 | English  |     300 |     105.50 |
| BK003   | Guide to Networking                 | 0000979003  | CA003   | AUT003 | P002   | 2002-09-10 | Hindi    |     510 |     200.00 |
| BK004   | Transfer  of Heat and Mass          | 0000979004  | CA002   | AUT004 | P004   | 2004-02-16 | English  |     600 |     250.00 |
| BK005   | Conceptual Physics                  | 0000979005  | CA001   | AUT005 | P006   | 2003-07-16 | NULL     |     345 |     145.00 |
| BK006   | Fundamentals of Heat                | 0000979006  | CA001   | AUT006 | P005   | 2003-08-10 | German   |     247 |     112.00 |
| BK007   | Advanced 3d Graphics                | 0000979007  | CA003   | AUT007 | P002   | 2004-02-16 | Hindi    |     165 |      56.00 |
| BK008   | Human Anatomy                       | 0000979008  | CA005   | AUT008 | P006   | 2001-05-17 | German   |      88 |      50.50 |
| BK009   | Mental Health Nursing               | 0000979009  | CA005   | AUT009 | P007   | 2004-02-10 | English  |     350 |     145.00 |
| BK010   | Fundamentals of Thermodynamics      | 0000979010  | CA002   | AUT010 | P007   | 2002-10-14 | English  |     400 |     225.00 |
| BK011   | The Experimental Analysis of Cat    | 0000979011  | CA004   | AUT011 | P005   | 2007-06-09 | French   |     225 |      95.00 |
| BK012   | The Nature  of World                | 0000979012  | CA004   | AUT005 | P008   | 2005-12-20 | English  |     350 |      88.00 |
| BK013   | Environment a Sustainable Future    | 0000979013  | CA004   | AUT012 | P001   | 2003-10-27 | German   |     165 |     100.00 |
| BK014   | Concepts in Health                  | 0000979014  | CA005   | AUT013 | P004   | 2001-08-25 | NULL     |     320 |     180.00 |
| BK015   | Anatomy & Physiology                | 0000979015  | CA005   | AUT014 | P008   | 2000-10-10 | Hindi    |     225 |     135.00 |
| BK016   | Networks and Telecommunications     | 00009790_16 | CA003   | AUT015 | P003   | 2002-01-01 | French   |      95 |      45.00 |
+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+





mysql> SELECT book_name,
    -> IF(pub_lang="English", "Engllish Book", "Other Lnaguage")
    -> AS Language
    -> FROM book_mast;
+-------------------------------------+----------------+
| book_name                           | Language       |
+-------------------------------------+----------------+
| Introduction to Electrodynamics     | Engllish Book  | 
| Understanding of Steel Construction | Engllish Book  | 
| Guide to Networking                 | Other Lnaguage | 
| Transfer  of Heat and Mass          | Engllish Book  | 
| Conceptual Physics                  | Other Lnaguage | 
| Fundamentals of Heat                | Other Lnaguage | 
| Advanced 3d Graphics                | Other Lnaguage | 
| Human Anatomy                       | Other Lnaguage | 
| Mental Health Nursing               | Engllish Book  | 
| Fundamentals of Thermodynamics      | Engllish Book  | 
| The Experimental Analysis of Cat    | Other Lnaguage | 
| The Nature  of World                | Engllish Book  | 
| Environment a Sustainable Future    | Other Lnaguage | 
| Concepts in Health                  | Other Lnaguage | 
| Anatomy & Physiology                | Other Lnaguage | 
| Networks and Telecommunications     | Other Lnaguage | 
+-------------------------------------+----------------+
16 rows in set (0.02 sec)

EXAMPLE REMOVED FROM THE FOLLOWING SITE:

link

score 0 · Answer 2

When you receive the data from the server, you can normalize the output in javascript. An example would be something like:

$.ajax({
        url: '',
        method: 'GET'
}).success(function(data){
   // caso o retorno seja vazio
   if (data.length == 0){
       data.atributo1 = 0;
       data.atributo2 = 0;
       // definir todos os atributos usado no grafico
   }
}).error(function(data){});