Replace NULL Value in MySQL

Question

how to find and replace empty rows when using this .....

$Qemaster="select * from emaster where `branch`='$bid' and `department`='$did' and `status`!='L'";
$Remaster=mysql_query($Qemaster);
while($Rowemaster=mysql_fetch_array($Remaster)){
    $empcode=$Rowemaster[id];
    $name=$Rowemaster[name];
    $Tleave=0;

echo "<tr>";
echo "<td rowspan='2'>".$name."</td>";
echo "<td>Leave</td>";

$Qlp="select * from lpsummary where ((`month` IN(04,05,06,07,08,09,10,11,12) and `year`='$year') or (`month` IN (01,02,03) and `year`='$Nyear')) and `empcode`='$empcode'";
$Rlp=mysql_query($Qlp);
$RRlp=mysql_num_rows($Rlp);
while($Rowlp=mysql_fetch_array($Rlp)){  

$leave=$Rowlp['leave'];
$Tleave=$Tleave+$leave;
if($leave==NULL){
    $Eleave='-';
}
else{
    $Eleave=$leave;
}
echo "<td>".$Eleave."</td>";
}
echo "<td><font color='red'>".$Tleave."</font></td>";
echo "<td><font color='green'>".substr(($Tleave/$RRlp),0,4)."</font></td>";
echo "<tr><td>Percentage</td>";
}

if there is an empty row ... i wanna to replace that as - instead of echo "".$Eleave."";

By learning how to use the [MySQL Update](http://dev.mysql.com/doc/refman/5.0/en/update.html) query. — Jon, Apr 15 '13 at 12:30

score 3 · Answer 1 · edited May 23 '17 at 12:06

3

actually you can do that directly via query so you will not have any IF-ELSE condition in your php code.by using COALESCE(), eg

Also, make it a habit to specify column names, not by using *.

SELECT colnames,..., COALESCE(`leave`, '-') `Leave`
FROM   lpsummary
WHERE  .....

UPDATE 1

SELECT  COALESCE(l.leave, '-') AS `LEAVE`
FROM    lpsummary l
WHERE   (
            (month IN(04,05,06,07,08,09,10,11,12) and year='$year') or 
            (month IN (01,02,03) and year='$Nyear')
        ) and empcode='$empcode'

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

edited May 23 '17 at 12:06

Community

1
1

answered Apr 15 '13 at 12:29

John Woo

258,903
69
498
492

Very nice function, I had been under the impression they wanted to update the table as well > – Jon Apr 15 '13 at 12:32
Thanks Mr.J W .. Now my code looks like as $Qlp="select leave,COALESCE(`leave`, '-') from lpsummary where ((`month` IN(04,05,06,07,08,09,10,11,12) and `year`='$year') or (`month` IN (01,02,03) and `year`='$Nyear')) and `empcode`='$empcode'"; But getting error as Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in – Pilaventhiran Apr 15 '13 at 12:50

Replace NULL Value in MySQL

1 Answers1