rwachowiak Posted August 18, 2008 Share Posted August 18, 2008 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! Link to comment https://forums.phpfreaks.com/topic/120219-sql-query-to-turn-0s-into-blank/ Share on other sites More sharing options...
cooldude832 Posted August 18, 2008 Share Posted August 18, 2008 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 Link to comment https://forums.phpfreaks.com/topic/120219-sql-query-to-turn-0s-into-blank/#findComment-619292 Share on other sites More sharing options...
rwachowiak Posted August 18, 2008 Author Share Posted August 18, 2008 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!!! Link to comment https://forums.phpfreaks.com/topic/120219-sql-query-to-turn-0s-into-blank/#findComment-619304 Share on other sites More sharing options...
Hooker Posted August 18, 2008 Share Posted August 18, 2008 what about it? Link to comment https://forums.phpfreaks.com/topic/120219-sql-query-to-turn-0s-into-blank/#findComment-619308 Share on other sites More sharing options...
rwachowiak Posted August 18, 2008 Author Share Posted August 18, 2008 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? Link to comment https://forums.phpfreaks.com/topic/120219-sql-query-to-turn-0s-into-blank/#findComment-619321 Share on other sites More sharing options...
Hooker Posted August 18, 2008 Share Posted August 18, 2008 $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> Link to comment https://forums.phpfreaks.com/topic/120219-sql-query-to-turn-0s-into-blank/#findComment-619330 Share on other sites More sharing options...
rwachowiak Posted August 18, 2008 Author Share Posted August 18, 2008 $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 Link to comment https://forums.phpfreaks.com/topic/120219-sql-query-to-turn-0s-into-blank/#findComment-619352 Share on other sites More sharing options...
cooldude832 Posted August 18, 2008 Share Posted August 18, 2008 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. Link to comment https://forums.phpfreaks.com/topic/120219-sql-query-to-turn-0s-into-blank/#findComment-619402 Share on other sites More sharing options...
rwachowiak Posted August 18, 2008 Author Share Posted August 18, 2008 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... Link to comment https://forums.phpfreaks.com/topic/120219-sql-query-to-turn-0s-into-blank/#findComment-619437 Share on other sites More sharing options...
cooldude832 Posted August 18, 2008 Share Posted August 18, 2008 well if u showed us what you tried on your own Link to comment https://forums.phpfreaks.com/topic/120219-sql-query-to-turn-0s-into-blank/#findComment-619459 Share on other sites More sharing options...
Barand Posted August 18, 2008 Share Posted August 18, 2008 use IF, not REPLACE IF(week1=0, '-', week1) as week1 Link to comment https://forums.phpfreaks.com/topic/120219-sql-query-to-turn-0s-into-blank/#findComment-619613 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.