How to replace empty query with value in PHP + MySQL

Question

I need to replace returned query from MySQL, if it is empty, with zero, but I can't figure out how.

For example I have table named values. There is device id and some period with values.

id_device | 2015-05-01 | 2015-05-02 | 2015-05-03 | 2015-05-04 | 2015-05-05
---------------------------------------------------------------------------
     1    |    1000    |    990     |    980     |    970     |    960    |
     2    |    1150    |    1140    |    1130    |    1120    |    1100   |
     3    |    1050    |    1040    |    1030    |    1020    |    1010   |
     4    |            |            |            |            |           |
     5    |    1250    |    1240    |    1230    |    1220    |    1210   |

When I use

$sql = mysql_query("SELECT * FROM values");
while ($row = mysql_fetch_array($sql)) {
    if (mysql_num_rows($row) == 0) { 
         $row[1] = 0; $row[2] = 0; $row[3] = 0; $row[4] = 0; $row[5] = 0;
         }
    echo "<td>$row[1] $row[2] $row[3] $row[4] $row[5]</td>";
    }

The 4th row is still empty instead of being replaced by zeros. Why? And how can I replace the empty fields with zeros?

Thank's @Sarath. Sry for accidently rejected your changes. I made some grammar mistakes here, but the code still doesn't work as I expect. — , Jun 30 '15 at 11:40
If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) [statements](http://php.net/manual/en/pdo.prepared-statements.php) instead, and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Jun 30 '15 at 11:55
(Commentary about downvotes does not belong in questions or answers. You can put them in comments if you like). — halfer, Jun 30 '15 at 12:26

score 1 · Accepted Answer · answered Jun 30 '15 at 11:46

1

You can handle that in your SQL Query, See example below:

SELECT `device_id`, IF(`column_name` = '', 0, `column_name`) as col_name FROM `table_name`

Apart from that, mysql_num_rows is used to return number or rows, in executed query.

EDIT:

You can use IFNULL, just incase if you are getting null values instead of blanks.

answered Jun 30 '15 at 11:46

kamal pal

4,187
5
25
40

Additionally, `mysql_*` functions are deprecated, and will be removed in future versions, so use [PDO](http://php.net/manual/en/book.pdo.php) instead – kamal pal Jun 30 '15 at 11:50
Thanks! Works like a charm. – Jun 30 '15 at 12:08

score 1 · Answer 2 · edited Jun 30 '15 at 13:34

1

The problem here is your condition, the mysql_num_rows. It checks, how many rows your SQL query returned. It returns this number, no matter where you are in your code, until your resource object ($sql) is overwritten.

So what your code does is the following:

#execute the sql statement
$sql = mysql_query("SELECT * FROM values);
#get the rows from the sql result, on by one
#the first $row contains id_device #1 and so on
while ($row = mysql_fetch_array($sql)) {
    #here you look up the number of rows your sql statement gets
    #this always returns 5, as the result from your query does not
    #change when looking at the single lines of your result
    if (mysql_num_rows($row) == 0) { 
        #you'll never enter this condintion
        $row[1] = 0; $row[2] = 0; $row[3] = 0; $row[4] = 0; $row[5] = 0;
    }
    echo "<td>$row[1] $row[2] $row[3] $row[4] $row[5]</td>"
}

Try this instead:

$sql = mysql_query("SELECT * FROM values);
while ($row = mysql_fetch_array($sql)) {
    #checks if one of the cells is empty
    if (in_array("", $row)){
        #if so, $row will be filled with empty values
        #except for the first element, as this is your device_id
        $row = array_fill(1, count($row), 0);
    }
    echo "<td>$row[1] $row[2] $row[3] $row[4] $row[5]</td>"
}

edited Jun 30 '15 at 13:34

halfer

19,824
17
99
186

answered Jun 30 '15 at 11:57

Tobias Weichart

359
1
13

I got your logic, thanks. I would give you upvote but this damn trolls has downvoted me for -3 and now my rank is too low to give upvotes :) – Jun 30 '15 at 12:10
no problem, just wanted to point out that the problem is the php code, not the sql query. can be "fixed" by changing the sql query too though ;-) – Tobias Weichart Jun 30 '15 at 12:13
@JEYCDEE: I've upvoted on your behalf. I don't think it's productive to describe people as 'trolls' - we don't know why they downvoted. Just answer a couple of questions and you'll get the rep back soon enough. – halfer Jun 30 '15 at 12:28
@halfer Thank you, kind sir. I think downvoting without any comments is useless and can't describe it anyhow but 'trolls' and I was just a bit upset. But I got your point. – Jun 30 '15 at 13:05
1

@JEYCDEE: no probs. The Stack Overflow Meta site has discussed "voting without commenting" many times, and the community's feeling is that it is perfectly acceptable. Some people like to vote on content and move on, and perhaps they assume the problem is evident. Someone's upvoted you now anyway `:-)`. – halfer Jun 30 '15 at 13:33

score 0 · Answer 3 · edited Mar 17 '17 at 11:34

You are checking if the number of rows are equal to zero but I understand that you want to check if there is empty columns, the MySQL will return empty string if the column is NULL or empty in the database, so you can simply add inside the while loop a check for each column if it is empty to become equal with zero

e.g

if($row[1]=="") $row[1]=0;
if($row[2]=="") $row[2]=0;
if($row[3]=="") $row[3]=0;
if($row[4]=="") $row[4]=0;
if($row[5]=="") $row[5]=0;

How to replace empty query with value in PHP + MySQL

3 Answers3