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 query.

Jon
– Jon

2013-04-15 12:30:00 +00:00
Commented Apr 15, 2013 at 12:30 — Jon
– Jon, Commented Apr 15, 2013 at 12:30

Community · Accepted Answer · 2017-05-23 12:06:04Z

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, 2017 at 12:06

CommunityBot

11 silver badge

answered Apr 15, 2013 at 12:29

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jon Over a year ago

Very nice function, I had been under the impression they wanted to update the table as well ><

Pilaventhiran Over a year ago

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

Collectives™ on Stack Overflow

Replace NULL Value in MySQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related