How to update specific table row in mysql table using php

Question

I am beginner in php , i am trying to update specific row but does not work . i need your help: update.php DIR . '/db_connect.php'; // connecting to db $db = new DB_CONNECT();

    if (isset($_POST['id'])) {

$id = $_POST["id"];
$location = $_POST["location"];
  $sql=mysql_query("UPDATE citizenalert set location={'$location'} WHERE id ={'$id'}");
}

  ?>

db_config.php

  <?php

   define('DB_USER', "root"); // db user
   define('DB_PASSWORD', ""); // db password (mention your db password here)
     define('DB_DATABASE', "ikirenga"); // database name
      define('DB_SERVER', "localhost"); // db server
     ?>

db_connect.php

           <?php

  class DB_CONNECT {

// constructor
function __construct() {
    // connecting to database
    $this->connect();
}

// destructor
function __destruct() {
    // closing db connection
    $this->close();
}

/**
 * Function to connect with database
 */
function connect() {
    // import database connection variables
    require_once __DIR__ . '/db_config.php';

    // Connecting to mysql database
    $con = mysql_connect(DB_SERVER, DB_USER, DB_PASSWORD) or die(mysql_error());


    // Selecing database
    $db = mysql_select_db(DB_DATABASE) or die(mysql_error()) or die(mysql_error());

    // returing connection cursor
    return $con;
}

/**
 * Function to close db connection
 */
function close() {
    // closing db connection
    mysql_close();
}
    }
   ?>

request_update.php

               <!doctype html>

                <html lang="en">

                   <body>
                  <form name="updates" method="post" action="update.php" > 
                   <label>User location</label> <input id="location"  type="text" name="location" > <br><br>
                     <input type="submit" name="submit" value="Submit" /> 
                     </form>  
                     </body>
                     </html>

chk your connection ist, and your code is open for SQL Injection. — devpro, Sep 26 '16 at 09:00
`UPDATE citizenalert set location='yourVal' WHERE id = YourID` run manually in code and php myadmin., also check `print_r($_POST)` what are u getting... before `if (isset($_POST['id'])) {` — devpro, Sep 26 '16 at 09:04
u r producing: `UPDATE citizenalert set location={'test'} WHERE id ={'1'} ` which is wrong for WHERE — devpro, Sep 26 '16 at 09:06
It amazes me that people are still using mysql_ functions still to this day. — Mikey, Sep 26 '16 at 09:11
all beginner using this... @Drew, dont know why teachers accept this. — devpro, Sep 26 '16 at 09:16
@devpro sad thing is that they are probably commercial websites http://stackoverflow.com/questions/38297105/mysql-real-escape-string-not-working-for-this-specific-example-mysql-real-escap#comment64014116_38297105 — Drew, Sep 26 '16 at 09:19

score 0 · Answer 1 · edited Sep 26 '16 at 09:05

0

Please use below Syntax

<?php
require_once __DIR__ . '/db_connect.php';
$db = new DB_CONNECT();

if (isset($_POST['id'])) {

$id = $_POST["id"];
$location = $_POST["location"];
$sql = mysql_query(" UPDATE citizenalert set location = " . $location . "   WHERE id = " . $id . ");
  }

Thanks.

edited Sep 26 '16 at 09:05

devpro

16,184
3
27
38

answered Sep 26 '16 at 09:04

Ramdas Gaikar

76
4

1

who knows, `$location` is string or integer. – devpro Sep 26 '16 at 09:05
sql injection here – Drew Sep 26 '16 at 09:09
$location is a string – Niragire Sam Sep 26 '16 at 09:21
@NiragireSam: did u checked the others answers?? – devpro Sep 26 '16 at 09:25
yes i am checking it and i don't find any change – Niragire Sam Sep 26 '16 at 09:34
@NiragireSam: this is not working? `$sql=mysql_query("UPDATE citizenalert set location='{$location}' WHERE id = '{$id}'");` – devpro Sep 26 '16 at 09:41
@devpro . YES is not working ,i don't know why . my table has two columns id and location . look at my form where i type value of location to update existing one . `
Name

` – Niragire Sam Sep 26 '16 at 10:01
@NiragireSam: missing ID in your form. where is ID field... – devpro Sep 26 '16 at 10:02
@devpro , i added it like this **id="location"** but still not update i get – Niragire Sam Sep 26 '16 at 10:26
@NiragireSam:where? please share the full code. – devpro Sep 26 '16 at 10:29
@devpro i share full code . check it and let me know why . Tnx – Niragire Sam Sep 26 '16 at 10:52
@NiragireSam: i have checked, u dont have `$_POST['id']` u cant get it by using `id=` etc... u need a input field for ID... what u expect the value of `ID`? – devpro Sep 26 '16 at 10:58
just i need to know if there is not any way i can update any table row without specifying its id statically in input field or in codes. – Niragire Sam Sep 26 '16 at 11:41

score 0 · Answer 2 · answered Sep 26 '16 at 09:05

0

Change your SQL query as below

$sql=mysql_query("UPDATE citizenalert set location='{$location}' WHERE id = '{$id}'");

answered Sep 26 '16 at 09:05

Saurabh

776
1
5
15

sql injection here – Drew Sep 26 '16 at 09:10

score 0 · Answer 3 · edited May 23 '17 at 12:19

If i test your code something like that:

<?php 
$id = 1;
$location = "test";
echo $sql="UPDATE citizenalert set location={'$location'} WHERE id ={'$id'}";
?>

It's giving this query:

UPDATE citizenalert set location={'test'} WHERE id ={'1'}

Please check here, both of values having issue.

Problem:

You are using {} outside the quotes. you need to use it inside the single quote.

Modified Query with same example:

echo $sql="UPDATE citizenalert set location='{$location}' WHERE id ='{$id}'";

Result:

UPDATE citizenalert set location='test' WHERE id ='1'

Side Notes:

Use mysqli_* or PDO, because mysql_* is deprecated and closed in PHP 7.
Your code is open for SQL Injection, you must need to protect with SQL Injection or just simply use Prepared Statement.

Some other Reference for Prepared Statement: php mysql bind-param, how to prepare statement for update query

How to update specific table row in mysql table using php

3 Answers3