php update MySql record

Question

I am trying to use PHP to do a simple API to update a certain column in a record in my database. However, I am stuck with Missing parameters error whenever I try something like http://localhost/set_is_open.php?open=0&shop_id=2

Here is my API:

<?php
    header('Content-Type: application/json');
    require_once 'db.php';

    if(!isset($_GET["open"]) || empty(trim($_GET["open"])) || !isset($_GET["shop_id"]) || empty(trim($_GET["shop_id"])))
        die(json_encode(array("error" => "Missing request paramters.")));

    $isOpen = trim($_GET["open"]);
    $shop_id = trim($_GET["shop_id"]);

    if(! $conn ) {
        die('Could not connect: ' . mysql_error());
    }

    $sql = 'UPDATE shops
                    SET is_open = isOpen
                    WHERE shop_id = shop_id';

    mysql_select_db('shops');
    $retval = mysql_query( $sql, $conn );
    if(! $retval ) {
        die('Could not update data: ' . mysql_error());
    }
    echo "Updated data successfully\n";
    mysql_close($conn);
?>

I doubt that there is something wrong I am doing maybe in the syntax, but I couldn't figure it out. I followed this tutorial here

You know that using string from the request are a sure way to sql injections in your system? Please use prepared statements instead — André Schild, Jul 18 '15 at 08:36

score 1 · Answer 1 · edited Jul 18 '15 at 09:41

1

Replace your current query string

$sql = 'UPDATE shops SET is_open = isOpen WHERE shop_id=shop_id'

with

$sql = "UPDATE shops SET is_open = '$isOpen' WHERE shop_id='$shop_id'"

PHP can then replace the variables in your string by the values that you set before.

Anyways, using the mysql API is deprecated and in the way you do it, also unsafe. Use PDO or mysqli in combination with prepared statements instead.

edited Jul 18 '15 at 09:41

Bhavesh G

3,000
4
39
66

answered Jul 18 '15 at 08:47

Niklas S.

1,046
10
22

@Behavesh Gangani Why am I getting( Could not update data: Unknown column '$shop_id' in 'where clause') when adding the dollar sign I am getting no syntax error but the database is not actually updated? – A_Matar Jul 18 '15 at 11:32

Victor Henriquez · Answer 2 · 2015-07-18T10:08:51.827

The problem is in the if-condition: empty(trim($_GET["open"])).

When open=0, empty(0) == true, so you get the "Missing parameter" message. You need to change or remove that condition. is_numeric could be a good alternative to do that check.

Try this:

if(!isset($_GET["open"]) || !is_numeric($_GET["open"]) ||
!isset($_GET["shop_id"]) || !is_numeric($_GET["shop_id"]))
die(json_encode(array("error" => "Missing request paramters.")));

score 1 · Accepted Answer · edited May 23 '17 at 12:14

Problem is in your if condition.

With empty() , the following things are considered empty:

"" (an empty string)
0 (0 as an integer)
0.0 (0 as a float)
"0" (0 as a string)
NULL
FALSE
array() (an empty array)
var $var; (a variable declared, but without a value in a class)

And you are inserting open=0&shop_id=2 as argument so here, open variable is assumed an empty.

you simply use,

if(!isset($_GET["open"]) || trim($_GET["open"]) == '' || 
   !isset($_GET["shop_id"]) || trim($_GET["shop_id"] == ''))
    die(json_encode(array("error" => "Missing request paramters.")));

Other point is that mysql functions are deprecated. Use mysqli_ or PDO is best. Also use prepared statements for security.

Read : Why shouldn't I use mysql_* functions in PHP?

php update MySql record

3 Answers3