Update the value in mysql PHP

Question

I am unable to update the value in the mySQL table. In my code below, option is in the format of [["test",0],["opt",0]]. I want to update it to [["test",1],["opt",0]]

<?php
if (isset($_POST['submit']))
{ 
$opt=$_POST['xyz'];
mysql_connect("localhost","root","");
 mysql_select_db("test"); 
 $sqlstmt="select * from polls where question='".$_POST['name']."' ";
$abc = mysql_query($sqlstmt); 
$rw=mysql_fetch_array($abc);
$opts = json_decode($rw['option']);


 for($i=0;$i<sizeof($opts);$i++)
 {
//$sqlstmt="select * from polls where question='".$_POST['name']."' and opton=$opt";

   if($opt==$opts[$i][0])
   {
  $opts[$i][1]+=1;
  echo $opts[$i][1];

    }

}
var_dump($opts);

$a=json_encode($opts);
  $b="UPDATE  polls
  SET option="$a", 
  WHERE question='".$_POST['name']."'";
  var_dump($_POST['name']);
  $c=mysql_query($b);
 var_dump($c);
}

I don't know where I am going wrong but var_dump($c) is returning the boolean false

why are you json_decoding $rw[option], is it json data saved into the database? — Kylie, Jun 10 '13 at 06:00
Yes Kylek it is storing in the form of json data like [["test",0],["opt",0]] — Shashank Cool, Jun 10 '13 at 06:01
Does this code actually compile? Also, your code is vulnerable to [sql injection](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php?rq=1). — Ja͢ck, Jun 10 '13 at 06:07
this code is compiling and the value is also updating but it is ubnable to update in database — Shashank Cool, Jun 10 '13 at 06:09
Your code gives a parse error, as can be seen [here](http://codepad.viper-7.com/rfUhxn). — Ja͢ck, Jun 10 '13 at 06:10
You are using [an **obsolete** database API](http://stackoverflow.com/q/12859942/19068) and should use a [modern replacement](http://php.net/manual/en/mysqlinfo.api.choosing.php). You are also **vulnerable to [SQL injection attacks](http://bobby-tables.com/)** that a modern API would make it easier to [defend](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) yourself from. — Quentin, Jun 10 '13 at 06:28

Kylie · Accepted Answer · 2013-06-10T06:32:40.593

First off.. You should be using PDO instead of the deprecated mysql_* functions please... Your code has major security holes!!!

http://php.net/manual/en/book.pdo.php

But, to answer you question...your problem is concatenation of $a So just change to this...

$a=json_encode($opts);
$b="UPDATE  polls SET option='".$a."' 
WHERE question='".$_POST['name']."'";
$c=mysql_query($b);
var_dump($c);
}

UPDATE / EDIT Heres your complete code, with PDO, for safety

You have to edit your connection info obviously, and I haven't tested this, but PDO is better than deprecated mysql_* functions, so here you go...

$dbname = "test";
$hostname = "localhost";
$pw = "root";
$username = "";

if (isset($_POST['submit']))
  { 
   $opt=$_POST['xyz'];

  $pdo = new PDO ("mssql:host=$hostname;dbname=$dbname","$username","$pw");
  try{
  $query = $pdo->prepare("select * from polls where question=:question");
  $query->execute(array(':question' => $_POST['name']));
  $rw = $query->fetchAll(PDO::FETCH_ASSOC);
  } catch(PDOException $ex) {
  //whatever error handling you want
  echo "An Error occured!"; 
  some_logging_function($ex->getMessage());
  }

  $opts = json_decode($rw['option']);

  for($i=0;$i<sizeof($opts);$i++)
  {
   if($opt==$opts[$i][0])
  {
  $opts[$i][1]+=1;

}

$a=json_encode($opts);
try { 
$query = $pdo->prepare("UPDATE  polls
SET option=? WHERE question=?");
$query->execute(array($a, $_POST['name']));
} catch(PDOException $ex) {
  //whatever error handling you want
  echo "An Error occured!"; 
  some_logging_function($ex->getMessage());
}

This code is dangerous, because there's no sql injection mitigation done. — Ja͢ck, Jun 10 '13 at 06:05
well obviously....were fixing his problems, not security holes — Kylie, Jun 10 '13 at 06:06
You should write code as if it's going to be copied and pasted into production code; this might actually be true in some cases. Adding a simple `mysql_real_escape_string()` would be the minimum. — Ja͢ck, Jun 10 '13 at 06:09

score 0 · Answer 2 · edited May 23 '17 at 10:25

0

There are a couple of issues; the most important one is having vulnerable code:

$sqlstmt="select * from polls where question='".$_POST['name']."' ";

This is dangerous code, because it opens up the code for SQL injection attacks. The least you should do is escape any user input:

$sqlstmt = sprintf("select * from polls where question='%s'",
    mysql_real_escape_string($_POST['name'])
);

Furthermore, the update statement has a parse error in both PHP and MySQL. Here's the fix:

$b = sprintf("UPDATE polls SET `option`='%s' WHERE question='%s'",
    mysql_real_escape_string($a),
    mysql_real_escape_string($_POST['name'])
);

Note that option is a reserved word in MySQL and therefore must be enclosed by backticks.

Recommendation

I would recommend you read up on either mysqli or PDO and adopt prepared statements as the way to prevent the most common SQL injection attacks.

edited May 23 '17 at 10:25

Community

1
1

answered Jun 10 '13 at 06:19

Ja͢ck

170,779
38
263
309

Thank you jack i am a begginer il try to implement what u have said – Shashank Cool Jun 10 '13 at 06:25
jack it is not updating into database – Shashank Cool Jun 10 '13 at 06:30
Jack i already used die(mysql_error()) but it is showing 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'option='[[\"Option1\",1],[\"Option2\",0],[\"Option3\",0],[\"Option4\",0]]' WHERE' at line 1 – Shashank Cool Jun 10 '13 at 06:34
should i use while loop? – Shashank Cool Jun 10 '13 at 06:38
@ShashankCool Oh, guess `option` is a reserved word, so you need to escape that with backticks .. updated answer. – Ja͢ck Jun 10 '13 at 06:41
jack still it is not working :( – Shashank Cool Jun 10 '13 at 06:45
yea jack the same error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''option'='[[\"Option1\",1],[\"Option2\",0],[\"Option3\",0],[\"Option4\",0]]' WHERE' at line 1 – Shashank Cool Jun 10 '13 at 06:48
@ShashankCool I don't see the backticks around `option` ... backticks are not normal ticks, if you're not sure, copy and paste from my answer. – Ja͢ck Jun 10 '13 at 06:48

score -1 · Answer 3 · edited May 23 '17 at 11:49

-1

Change -

  $b="UPDATE  polls
  SET option="$a", 
  WHERE question='".$_POST['name']."'";

To

$b="UPDATE  polls SET option='".$a."' WHERE question='".$_POST['name']."'";

As per the JACK suggestion..the above code is open to SQL injections..so take of it before implementing it in real terms.

Read well popular post here to deal with the sql injection

edited May 23 '17 at 11:49

Community

1
1

answered Jun 10 '13 at 06:02

swapnesh

26,318
22
94
126

Reason for doing this so ? – swapnesh Jun 10 '13 at 06:06
Yeah I got downvoted to....dont understand the reason...our answers are completely valid – Kylie Jun 10 '13 at 06:06

Update the value in mysql PHP

3 Answers3