Unable to pass the value of ID in an update MySql query in the code

Question

I need to pass the value of a drop down list to an already existing table, I've tried a simple MySql query which works properly in the MySQL SQL console.

Here is the query which I need to run: update Trial set Status = 'Test Status' where id = '123'

When I try to run this query it runs into variable issues as it's unavailable to get the value of ID from the row.

Below are two Queries, the first one fetches all the data, and the second which is not proper is the one which needs to update the value of the drop down list to the already existing table where the ID mathches. Any help is appreciated.

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "cotactform";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT id, name, email, phone, Status,Url, datetime FROM Trial";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "<tr style='text-align:center;'><td>" . $row["id"]. "</td><td>" . $row["name"] . "</td><td>" . $row["email"]. "</td><td>". $row["phone"]. "</td><td>". $row["Url"]. "</td><td>". $row["datetime"]. "</td><form method='get' action='displaydata.php'><td><select name='Status'><option>--Select Status--</option><option value='valid'>Valid</option><option value='invalid'>Invalid</option><option value='followup'>FollowUp</option></select><button type='submit' name='submit'>Submit</button></form></td> <td>". $row["Status"]."</td></tr>";
    }
} else {
    echo "0 results";
}




  if(isset($_GET['submit']))
{
    $Status=$_GET['Status'];

   $conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
    // The query to update the Status
    $ins="update Trial set Status = '$Status' where id = 'id'";
    if($ins)
    {
        echo "<br>".$Status."inserted";
    }
    else
    {
        echo mysql_error();
    }
}

$conn->close();
?>

Edit Updated for adding the hidden field:

 while($row = $result->fetch_assoc()) {
        echo "<tr style='text-align:center;'><td>" . $row["id"]. "</td><td>" . $row["name"] . "</td><td>" . $row["email"]. "</td><td>". $row["phone"]. "</td><td>". $row["Url"]. "</td><td>". $row["datetime"]. "</td><form method='get' action='displaydata.php'><td><select type='hidden' name=".$row["id"]."><option>--Select Status--</option><option value='valid'>Valid</option><option value='invalid'>Invalid</option><option value='followup'>FollowUp</option></select><button type='submit' name='submit'>Submit</button></form></td> <td>". $row["Status"]."</td></tr>";
    }

Second query check:

if(isset($_GET['submit']))
{
    $Status=$_GET['Status'];


if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
     //$id=$_GET['id'];
     //$Status=$_GET['Status'];

    $ins = "update Trial set Status = '" . $Status ."' where id = '".$id."'";
    $run = $conn->query($ins);
    if($ins)
    {
        echo "<br>".$Status."inserted";
    }
    else
    {
        echo mysql_error();
    }
}

i didnt find mysql_query or mysqli_query execution, second you r mixing mysql and mysqli, third i suggest you to use integer as a status not string — devpro, Feb 20 '19 at 12:11
Also mind the SQL injection which is possible in the update query. — Raymond Nijland, Feb 20 '19 at 12:15
What have you tried to debug the problem? That's a lot of code, and most of it (like echoing some table lines) seems out of scope for your question — Nico Haase, Feb 20 '19 at 12:48

score 0 · Answer 1 · answered Feb 20 '19 at 12:14

0

Try this for the update query -

$ins = "update Trial set Status = " . $Status ." where id = 123";

Naturally, if you can obtain the 'id' and store it an a variable then you replace the hardcoded id (123) by $id (say), it would become -

$ins = "update Trial set Status = " . $Status ." where id = " . $id;

answered Feb 20 '19 at 12:14

sujaypatil

60
1
10

Mind the SQL injections, which is at top to hack webapplications – Raymond Nijland Feb 20 '19 at 12:15
but $status is string not integer, i think OP just forgot to use query exection – devpro Feb 20 '19 at 12:15
Is there any way to save the id as a variable, currently it's giving the Undefined Variable error, also I will update the $status as an int – Anjum Shaikh Feb 20 '19 at 12:19
@AnjumShaikh: send ID in hidden input of form `$row["id"]` then u can use it. – devpro Feb 20 '19 at 12:28
@devpro I added that in the hidden input of the form but it still gives an error on the line where the second query is placed that it is a undefined variable. Should I edit and add both of the updated queries to the question above – Anjum Shaikh Feb 20 '19 at 12:45
i only see select box not any hidden in this form `
`
– devpro Feb 20 '19 at 12:46

devpro · Answer 2 · 2019-02-20T13:11:06.037

There are so many issues in your code:

First, your code is wide open for SQL Injection, you can use PDO here to prevent SQL Injection.

Second, i didnt see query execution with your UPDATE query.

Third, this is just a suggestion, use integer for multiple status instead of Complete Word or String.

Fourth, Don't know what are you doing with your <form>, you are using hidden attribute with <select> box which is not required to get ID.

Fifth, using mysql_error() means, you are mixing mysqli and mysql together.

For getting ID, you need to modify your <form> as:

<form>
   <input type="hidden" name="id" value="your ID">
   <select>
     <option></option>
   </select>
</form>

Then, you can get this hidden ID in your query as $id = $_GET['id'].

Side Note: this is very important, you are using <form> method GET for UPDATE query, you must need to prevent your code with SQL Injection.

Some References:

How can I prevent SQL injection in PHP?

Are PDO prepared statements sufficient to prevent SQL injection?

Unable to pass the value of ID in an update MySql query in the code

2 Answers2