Can not update MySQL database?

Question

I'm trying to update date in database but for some reason it won't work, here is the code.

This is me PHP page where I put code to display data and I have an EDIT button that sends me to a page_edit where I cane type another NAME and UpdatE it. I want to pick a random row and edit name in it.

Main page (main.php)

<?php
    if ($mysqli->connect_error) {die("Connection failed: " . $mysqli->connect_error);} 
    $sql = "SELECT * FROM users ORDER BY name";
    $result = $mysqli->query($sql);
    while ($rows = mysqli_fetch_array($result)) {   
        echo '
            <div class="admin-content-con">
            <header class="clearfix">
            </header>
            <table class="table table-striped">
            <thead>
                <tr>
                <th>No</th>
                <th>Name</th>
                <th>Link</th>
                <th>Actions</th>
            </tr>
            </thead>
            <tbody>';            
        echo "<tr>";
        echo "<td>" .$rows ['id']."</td>";
        echo "<td>" .$rows ['name']."</td>";
        echo "<td>" .$rows ['link']."</td>";
        echo "<td> 
        <a href='page_edit.php?id=".$rows["id"]."'class='btn btn-xs btn-warning '  role='button'>edit</a> 
        <a href='delete.php?id=".$rows["id"]."'class='btn btn-xs btn-danger'  role='button' onclick='return checkDelete()'>del</a></td>";     
        echo "</tr>";       
        echo '</tbody>
        </table>
        </div>';
    } 
?>

Page (page_edit.php).

<div class="settings-row">
    <h3>Name</h3>
    <p>This is permanently show on the left corner of navigation</p>

    <form action="name.php" method="post" >   
        <div class="form-group">
            <input type="text" class="form-control" name="name">
            <input type="submit" value="Submit" name="submit">

        </div>
    </form>
</div>

PHP back-end code who update the database (name.php).

<?php
    include '*'; 
    $dbname = "*";
    $conn = mysqli_connect("localhost", "root", "root", $dbname);
    if (!$conn) { die("Connection failed: " . mysqli_connect_error()); }
    $sql ="UPDATE users SET name='".$_POST['name']."' WHERE id='$id'";
    if (mysqli_query($conn, $sql)) {
        mysqli_close($conn);
        header('Location: main.php'); 
        exit;
    } else {
        echo "Error update record";
    }
?>

You are open to SQL injections. Parameterize your query. Use error reporting, `Error update record` is useless. What happens with the execution of this code currently? Where is `$id` defined? Looks like you forgot to include the `id` in the HTML. — user3783243, Feb 08 '20 at 17:56
This is a local host server i'm trying to make it work. The `id` in html, explain to me because i'll try anything — Deda Mraz, Feb 08 '20 at 17:58
As @HiteshKumar points out, it looks like you need to add `$id = $_POST['id'];` before `sql = ...` statement. And yes, the sql injection vulnerability needs to be addressed as well. — Rob Moll, Feb 08 '20 at 18:02
I try to get it from page_edit.php, the `id`. Dont know how I'm new at php. :( — Deda Mraz, Feb 08 '20 at 18:04
@user3783243 Oh! I thought he was passing it in the url `edit` — Rob Moll, Feb 08 '20 at 18:06

score 1 · Accepted Answer · answered Feb 08 '20 at 18:07

You need to pass the id to the edit page. You then need to take in the id on the update page.

<div class="settings-row">
       <h3>Name</h3>
       <p>This is permanently show on the left corner of navigation</p>

       <form action="name.php" method="post" >   
       <div class="form-group">
       <input type="text" class="form-control" name="name">
       <input type="submit" value="Submit" name="submit">
       <input type="hidden" value="<?php echo htmlspecialchars($_GET['id']);?>" name="id">
       </div>
       </form>
       </div>

and then

<?php
include '*'; 
$dbname = "*";
$conn = mysqli_connect("localhost", "root", "root", $dbname);
if (!$conn) {die("Connection failed: " . mysqli_connect_error());}
$id = $_POST['id'];
$sql ="UPDATE users SET name='".$_POST['name']."' WHERE id='$id'";
if (mysqli_query($conn, $sql)) {
    mysqli_close($conn);
    header('Location: main.php'); 
    exit;
} else {
  echo "Error update record";
}
?>

See How can I prevent SQL injection in PHP? for how you should write the SQL.

Roughly,

$sql ="UPDATE users SET name=? WHERE id=?";

then prepare, bind, and execute.

Additionally use error reporting for both PHP and the mysqli driver. Your $id should have been reported as an undefined variable.

Oh man thanks a lot i just need this ``. – Deda Mraz Feb 08 '20 at 18:11 — Deda Mraz, Feb 08 '20 at 18:11

Hitesh Kumar · Answer 2 · 2020-02-08T18:26:23.750

page_edit.php should look like below -

//Add hidden input type in form for get value of id

<div class="settings-row">
           <h3>Name</h3>
           <p>This is permanently show on the left corner of navigation</p>

           <form action="name.php" method="post" >
           <input type="hidden" class="form-control" name="id" value="<?php echo htmlspecialchars($_GET['id']);?>">   
           <div class="form-group">
           <input type="text" class="form-control" name="name">
           <input type="submit" value="Submit" name="submit">

       </div>
       </form>
       </div>

And page.php should be like this

<?php
include '*'; 
$dbname = "*";
$conn = mysqli_connect("localhost", "root", "root", $dbname);
if (!$conn) {die("Connection failed: " . mysqli_connect_error());}
$id = $_POST['id'];
$sql ="UPDATE users SET name='".$_POST['name']."' WHERE id='$id'";
if (mysqli_query($conn, $sql)) {
    mysqli_close($conn);
    header('Location: main.php'); 
    exit;
} else {
  echo "Error update record";
}
?>

Yes, that will secure against it (with double quote encapsulation, if singles were used need to use the constant) — user3783243, Feb 08 '20 at 18:23

Can not update MySQL database?

2 Answers2