-2

I have a field datatype VARCHAR in mysql. It has to save data like 495/1,495/2 but when i am running query from PHP it save in mysql like 495, 247.5. so basically mysql divides the values.

Then I have tried this

$sfno = $_POST['sfno']; // i am getting value from HTML form the value is 495/2    
$sfno = explode("/",$sfno);
$sfnom = "$sfno[0]"."-"."$sfno[1]";

then mysql subtract the values and save them as 494,493

so how can i save arithmetic operators in mysql database?

enter image description here

Jaiffable
  • 67
  • 1
  • 12
  • `$sfno = "497/2";` and then save it. – Criesto Oct 15 '15 at 12:14
  • How is the value in the $_POST created? – x13 Oct 15 '15 at 12:20
  • @ThisNameBetterBeAvailable from the HTML form where user input the value like `495/1,495/2` etc – Jaiffable Oct 15 '15 at 12:21
  • @Jaiffable What happens when you do what Criesto said? Does it work? – x13 Oct 15 '15 at 12:24
  • @ThisNameBetterBeAvailable NO PHP is working fine but when it goes to mysql it divides the value and save them – Jaiffable Oct 15 '15 at 12:26
  • Maybe this (note the single quotes around the variables but inside the double quotes): `$sfnom = "'$sfno[0]'"."-"."'$sfno[1]'";` If the values appear between quotes in the sql query it won't dare to calculate it – x13 Oct 15 '15 at 12:30
  • @ThisNameBetterBeAvailable it gets error `Unknown column '2f2' in 'field list'` – Jaiffable Oct 15 '15 at 12:32
  • @Jaiffable then i guess that it does not know the colum '2f2'. Can you post your query code? – x13 Oct 15 '15 at 12:33
  • @ThisNameBetterBeAvailable `$query = "INSERT INTO sfarea(sfareaid,sfno,sftotarea,sfremarea)values($nxtid,$sfnom,$sfarea,$sfremarea)";` – Jaiffable Oct 15 '15 at 12:36
  • `%2f` is url encoded for `/`, try URL decoding it (`$decoded = urldecode('%2f')`) before putting it in the query. – x13 Oct 15 '15 at 12:41
  • 1
    @ThisNameBetterBeAvailable No it's also not working plz check this [link](http://postimg.org/image/xcc2oiq77/) – Jaiffable Oct 15 '15 at 12:55
  • @Jaiffable See my answer. – x13 Oct 15 '15 at 12:57

1 Answers1

1

You need to put the variable between quotes in the query string to prevent calculation.

$sfnom = "$sfno[0]"."-"."$sfno[1]";

Becomes (note the single quotes inside the string):

$sfnom = "'$sfno[0]"."-"."$sfno[1]'";

You also need to URL decode it (%2f is /, otherwise it will contain 2f instead of /):

$sfnom = urldecode("'$sfno[0]"."-"."$sfno[1]'");

So INSERT INTO sfarea(sfareaid,sfno,sftotarea,sfsoldarea) values(2003,495/2,9854,0) becomes:

 INSERT INTO sfarea(sfareaid,sfno,sftotarea,sfsoldarea) values(2003,'495/2',9854,0)

Only way you are going to store arithmetic operators is as a string, so you need quotes around it.

x13
  • 2,179
  • 1
  • 11
  • 27