[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

PHP returning \N when dividin by aeron MySql Table

Posted on 2014-08-25
11
Medium Priority
?
285 Views
Last Modified: 2014-08-25
I have a MySql table and I manipulate numbres. I divide a row of numbrs by zero and get no error the result is just \N. It  I really need to 0's in the result.
0
Comment
Question by:robrodp
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 40284213
Dividing by zero is 'undefined' and does not return 0's.  More info here: http://dev.mysql.com/doc/refman/5.0/en/precision-math-expressions.html
0
 
LVL 58

Expert Comment

by:Gary
ID: 40284219
You cannot divide by zero in PHP - why on earth would you want to?
Enable error reporting in your page and you will see the error.
0
 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 1000 total points
ID: 40284300
You cannot divide by zero in PHP
You cannot divide by zero in mathematics!  My guess is that the \N is an indicator of Not-a-Number.  Your script will need to test for zeros and avoid dividing by zero.

See if this helps: http://www.mysqltutorial.org/mysql-nullif/
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:robrodp
ID: 40284335
Hey guys.. I knew that you cannot divide by zero.

My table has colums, for each comul y sum its total and didife each cell by this total and I get a participationcpercentage.

When the colum sums zero (lno sales or whatever) I amgetting this result I was expecting an error, but no error just this \N
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 40284444
The MySQL response is listed in the link above.  Default response is blank like ''.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40284465
Please show us the table structure, a bit of test data, and the exact query you're using so we can load the tables and duplicate the problem.  This is called the SSCCE and it's essential if you want a quick and accurate (eg: tested) answer.
0
 

Author Comment

by:robrodp
ID: 40284516
the table
is:

 for simpliciyi I have ta bele with 1 column and n rows...

Say
Sales
                 Year1    Year2
Total         1,600      0
Auto        1,000       0
Clothing    500       0
Food          100       0
Car                0       0

I need percenteges

                 Year1    Year2
Total         1,600      0
Auto        1,000       0
Clothing    500       0
Food          100       0
Car                0       0

I need a table with:

Year 1      Year 2
62.5         0
50              0
20             0
0             0

I sum the values of teh columa and then divide. Not too hard... but rather complex sytax

But for year 2 I just cant figure it out properly.


Thx
0
 
LVL 58

Expert Comment

by:Gary
ID: 40284531
Are you doing this in the sql or the php, somewhere up above (cannot see it now) Ray gave you the IF NULL clause you can use in an SQL statement
0
 

Author Comment

by:robrodp
ID: 40284544
I doing it in MySql

Like

cast(colname/(select sum(colname) from file where not colname='Total')  *  100 as decimal(5,2))
0
 
LVL 58

Accepted Solution

by:
Gary earned 1000 total points
ID: 40284559
Actuall ifnull wouldn't work for you
Untested:

cast(colname/(select CASE WHEN sum(colname)=0 THEN 1 else sum(colname) END from file where not colname='Total')  *  100 as decimal(5,2)) 

Open in new window

...and probably wrong...always find this confusing in MySQL
0
 

Author Closing Comment

by:robrodp
ID: 40284622
That did the trick

Thx guys,,,, and I know that dividing by zero isa no no in math....
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses
Course of the Month18 days, 20 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question