SQL Query to turn 0's into blank

[rwachowiak]

whats the easiest way to a query turn 0's (zeros) into "-" in the display?

 

this is my code so far... but say Field 1 is a 0, i want a - there instead

 

but i cant use a and if then statement because the actual query is massive...

 

$query  = "SELECT * FROM table";

$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){

echo "Instead of 0 this is a -: {$row['field1']}";

}

 

thanks!

[cooldude832]

They way you wrote it is a php str_replace

if you wanted to do the replacement in mysql you should use something like this (but don't use that nasty star operator to select all field)

SELECT *, REPLACE(field1,'0','-') as Replaced from `table`

 

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace

 

http://us2.php.net/str_replace

[rwachowiak]

They way you wrote it is a php str_replace

if you wanted to do the replacement in mysql you should use something like this (but don't use that nasty star operator to select all field)

SELECT *, REPLACE(field1,'0','-') as Replaced from `table`

 

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace

 

http://us2.php.net/str_replace

 

yes perfect, now what about if i got this going on:

 

$id = (isset($_GET['Userid']))? mysql_escape_string($_GET['Userid']) : "1";
$query  = "SELECT (wk1act-week1), week1, wk1act FROM staff JOIN project ON project.staff_id=staff.id WHERE project.staff_id='$id' GROUP BY project.id";

$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
echo "<tr>".
   "<td width='40' height='20' class='vertspreader'><div align='center'>{$row['week1']}</div></td>" .
   "<td width='40' height='20' class='topspreader'><div align='center'>{$row['wk1act']}</div></td>" .
   "<td width='40' height='20' class='topspreader'><div align='center'>{$row['(wk1act-week1)']}</div></td>" .
}
</tr>

 

thanks!!!

[Hooker]

what about it?

[rwachowiak]

what about it?

 

how do i do the replace? there are 0's in the wk1act and week1 fields... but there are also other numbers, but in the query, i want all the numbers that arent 0 to show properly, and if a zero is found i want it as a -...

 

any ideas?

[Hooker]

$id = (isset($_GET['Userid']))? mysql_escape_string($_GET['Userid']) : "1";
$query  = "SELECT (wk1act-week1), REPLACE(week1,'0','-') as week1a, REPLACE(wk1act,'0','-') as wk1acta FROM staff JOIN project ON project.staff_id=staff.id WHERE project.staff_id='$id' GROUP BY project.id";

$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
echo "<tr>".
   "<td width='40' height='20' class='vertspreader'><div align='center'>{$row['week1a']}</div></td>" .
   "<td width='40' height='20' class='topspreader'><div align='center'>{$row['wk1acta']}</div></td>" .
   "<td width='40' height='20' class='topspreader'><div align='center'>{$row['(wk1act-week1)']}</div></td>" .
}
</tr>

[rwachowiak]

$id = (isset($_GET['Userid']))? mysql_escape_string($_GET['Userid']) : "1";
$query  = "SELECT (wk1act-week1), REPLACE(week1,'0','-') as week1a, REPLACE(wk1act,'0','-') as wk1acta FROM staff JOIN project ON project.staff_id=staff.id WHERE project.staff_id='$id' GROUP BY project.id";

$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
echo "<tr>".
   "<td width='40' height='20' class='vertspreader'><div align='center'>{$row['week1a']}</div></td>" .
   "<td width='40' height='20' class='topspreader'><div align='center'>{$row['wk1acta']}</div></td>" .
   "<td width='40' height='20' class='topspreader'><div align='center'>{$row['(wk1act-week1)']}</div></td>" .
}
</tr>

 

ah nice thats how! perfect!!!!

 

ok... but now it changes 10 to 1-... VERY CLOSE THOUGH!!

 

how can i make it exact?? hmm

[cooldude832]

do some reading on your own if we spoon feed you it all you aint' gonna learn we gave you all the tools you need.

[rwachowiak]

do some reading on your own if we spoon feed you it all you aint' gonna learn we gave you all the tools you need.

 

uh... ive looked and looked it all i can see is that it replaces the 0 no matter what... i cant figure it out i been searching for a while now...

[cooldude832]

well if u showed us what you tried on your own

[Barand]

use IF, not REPLACE

 

 

IF(week1=0, '-', week1) as week1