what is wrong with this php update code

Question

I created this code to update mysql and it seems not to be working. I have tried my best but still, it doesn't work. It is not updating my database. what could be the problem? my config page

<?php

define("dbuser", "XX");

define("dbpass", "XX");

define("dbhost", "localhost");

define("dbname", "smsplugin");

?>

$smsbal = $_POST['smsbal'];
$values =$_POST['values'];
$api = $_POST['api'];

$db = new PDO('mysql:host='.dbhost.';dbname='.dbname.';charset=utf8', dbuser, dbpass);
$sql="UPDATE user SET api=?,values=?,left=? WHERE id='1'";
$q=$db->prepare($sql);
$q->execute(array($api,$values,$smsbal));

<form method="POST" action="" class="well form-horizontal" style="width:338px;margin:0 auto;"><br />
<br />

<p>
<label for="api"><strong>API Setting</strong></label>

<input type="text" name="api" id="api"  data-placement="right" data-html="true" data-animation="true"/>
</p>
<p>
<label for="values"><strong>API Values</strong></label>
<input type="text" name="values" id="values" title="These are the values that are passed to your SMS provider; <strong>Sender, Receiver and the Message</strong>. Depending on the 
setting of your SMS Provider, ensure that <em>$sender</em> is use for the sender value, <em>$receipient</em> is used for the receiver value and <em>$message</em> is used for the message value like in the
example below  <br /><strong>NOTE: CHANGE 'sender=','receipient=' and 'message=' TO THE ACCEPTED VARIABLES OF YOUR SMS PROVIDER.</strong>
sender=$sender&recipient=$recipient&message= $message" value="<?php print $fapi ;?>"  data-placement="right" data-html="true" data-animation="true"/>
</p>
    <p>
<label for="smsbal"><strong>SMS Balance</strong> (optional)</label>

<input type="text" name="smsbal" id="smsbal" title="This is the script that checks the remaining balance of your SMS unit. <br />
<strong>NOTE: ENTER THE ACCEPTED VARIABLE AND VALUE OF YOUR SMS PROVIDER.</strong><strong>E.g: bal=true</strong>" data-placement="right" data-html="true" data-animation="true" />
</p>
  <div class="control-group">
       <button type="submit" name="save" value="save" class="btn-primary btn-mini">Save</button>
  </div>
</form>

Hi, this calls for basic debugging. See [How to squeeze error message out of PDO?](http://stackoverflow.com/q/3726505) for help. As a side note, you do not need to use `mysql_real_escape_String()` in connection with PDO — Pekka, Aug 12 '13 at 11:55

score 7 · Accepted Answer · answered Aug 12 '13 at 12:12

Your BIGGEST and foremost problem is lack of error reporting.

It spoils not only this particular problem but your whole experience with PHP.
Every time when something goes wrong, PHP will tell you - what happened and whom to blame. Only if you let it. And you always should.

You've been told to check for errors in the comments. But for some reason didn't notice it. Ok, here goes a through explanation:

To be able to see database errors, one have to set PDO errmode to exceptions. Exceptions are better than regular errors in many ways: they always contains a stack trace, they can be caught using try..catch or handled using dedicated error handler. And even unhandled, they act as regular PHP errors providing all the important information, following site-wide error reporting settings.

Note that setting this mode as a connection option will let PDO throw exceptions on connection errors too, which is very important.
So, here is an example for creating a PDO connection right way:

$dsn = "mysql:host=$host;dbname=$db;charset=utf8";
$opt = array(
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    // other options 
);
$pdo = new PDO($dsn, $user, $pass, $opt);

Connecting this way, you will be always notified of all database errors, occurred during query execution. Note that you have to be able to see PHP errors in general. On a live site you have to peek into error logs, so, settings have to be

error_reporting(E_ALL);
ini_set('display_errors',0);
ini_set('log_errors',1);

while on a local development server it's ok to make errors on screen:

error_reporting(E_ALL);
ini_set('display_errors',1);

and of course you should never ever use error suppression operator (@) in front of your PDO statements.

Also, due to many bad examples telling you to wrap every PDO statement into try..catch block, I have to make a distinct note:

DO NOT use try..catch operator just to echo an error message. Uncaught exception is already excellent for this purpose, as it will act just the same way as other PHP errors - so, you can define the behavior using site-wide settings - so, you will have your error message without this useless code. While unconditionally echoed error message may reveal some sensitive information to a potential attacker, yet confuse a honest visitor.

A custom exception handler could be added later, but not required. Especially for new users, it is recommended to use unhandled exceptions, as they are extremely informative, helpful and secure.

Use try..catch only if you are going to handle the error itself - say, to rollback a transaction.

After getting your error you will know that SQL choked on the left word which is reserved in mysql and have to be formatted:

 UPDATE user SET api=?,values=?,`left`=? WHERE id='1'

Thank you so much for writing this up. This is going to be my standard link-to answer in cases of "my query doesn't work, help!" — viraptor, Aug 12 '13 at 12:39
Thanks, Your Common sense. You have solved my headache since last week friday. not only the 'left' field but 'values' was also a wrong syntax. I wouldn't have known this if not for the error reporting. — Charles Okaformbah, Aug 12 '13 at 13:01

MajorCaiger · Answer 2 · 2013-08-12T12:01:06.273

-1

Try wrapping your PHP in PHP openning and closing tags

<?php

... Your php

?>
<SomeHtml>
....

Also, are your db variables constants or should they be prefixed with a $ e.g $dbhost instead of dbhost.

edited Aug 12 '13 at 12:01

answered Aug 12 '13 at 11:55

MajorCaiger

1,893
1
12
18

What about the $'s on you db variables? What error are you getting? – MajorCaiger Aug 12 '13 at 12:02

score -1 · Answer 3 · answered Aug 12 '13 at 12:15

-1

You can try this:

<?php
    if($_POST){
        $smsbal = $_POST['smsbal'];
        $values =$_POST['values'];
        $api = $_POST['api'];

        //echo $api; No need to print it!

        $db = new PDO('mysql:host='.dbhost.';dbname='.dbname.';charset=utf8', dbuser, dbpass);
        $sql="UPDATE user SET api= :api, values= :values, left= :left WHERE id='1'";
        $q = $db->prepare($sql);
        $q->bindValue(':api', $api);
        $q->bindValue(':values', $values);
        $q->bindValue(':left', $left);
        $q->execute();
    }
?>

answered Aug 12 '13 at 12:15

Ronald Araújo

1,395
4
19
30

I can not guarantee it to be the best solution, but in case it would solve. – Ronald Araújo Aug 12 '13 at 12:28
If you pay attention to his question, he says he can not update his table. I believe that if he does so, he can! – Ronald Araújo Aug 12 '13 at 12:33

what is wrong with this php update code

3 Answers3