Selecting data from mysql database from a search form

Question

I am new at this and still learning. I have a search page and want to use the input to search a mysql table and display results in a form to update the record back into the table.

Every time I try and run it I get a PHP Notice: Undefined variable: password in /var/www/html/update.php on line 106, referer: http://172.20.10.161/search.php

in the error_log.

All help would be most appreciated.

I have google and tried various methods to get this right, i feel there is some little thing I am missing here.

Below is the code from my search.php page

<?php

session_start();


if (!isset($_SESSION["loggedin"]) || $_SESSION["loggedin"] !== true) {
    header("location: login.php");
    exit;
}

?>

<form action="update.php" method="post">
   <div class="form-group">
       <label>Name</label>
       <input type="text" name="name" class="form-control" value="">
   </div>
   <div class="form-group">
       <input type="submit" class="btn btn-primary" value="Search">
   </div>
</form>

Then on my page that should show the results if have the following.

update.php

top of page

<?php
session_start();

if (!isset($_SESSION["loggedin"]) || $_SESSION["loggedin"] !== true) {
   header("location: login.php");
   exit;
}
?>

Code in page to run query

<?php

require_once "include/dbconf.php";

if(isset($_POST['Search']))
{
    $name=$_POST['name'];

    $sql = "SELECT (name, surname, email, username, password) from net_users WHERE name LIKE '%".$name."%'";
    $result  = mysqli_query($link, $sql) or die ('Something went wrong');

    while($row=mysqli_fetch_array($result))
    {
         $username  =$row['username'];
         $password  =$row['password'];
         $name      =$row['name'];
         $surname   =$row['surname'];
         $email     =$row['email'];
    }
}

mysqli_close($link);
?>

<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post">
    <div class="form-group">
         <label>Name</label>
         <input type="text" name="name" class="form-control" value="<?php echo $name; ?>">
    </div>
    <div class="form-group">
         <label>Surname</label>
         <input type="text" name="surname" class="form-control" value="<?php echo $surname; ?>">
    </div>
    <div class="form-group">
         <label>Email</label>
         <input type="email" name="email" class="form-control" value="<?php echo $email; ?>">
    </div>
    <div class="form-group">
         <label>Username</label>
         <input type="text" name="username" class="form-control" value="<?php echo $username; ?>">
    </div>
    <div class="form-group">
         <label>Password</label>
         <input type="text" name="password" class="form-control" value="<?php echo $password; ?>">
    /div>
    <div class="form-group">
        <input type="update" class="btn btn-primary" value="update">
    </div>
</form>

I am hoping to pull the desired input on search $name to search the mysql db and return the results in the form on the update page to update the information back into the database.

Answer 1

I would recommend a couple of changes to update.php.

<?php
session_start();

if (!isset($_SESSION["loggedin"]) || $_SESSION["loggedin"] !== true) {
   // Can everyone logged in update the system.  If not, filter it as required
   header("location: login.php");
   exit;
}
?>

Given the following connection file dbconf.php with a procedual MySQLi - https://www.php.net/manual/en/mysqli.quickstart.dual-interface.php

<?php 
/* 
Database credentials.
Assuming you are running MySQL server with default setting (user 'root' with no password)
*/ 
define('DB_SERVER', 'localhost'); 
define('DB_USERNAME', 'xxxxxxxx'); 
define('DB_PASSWORD', '**********'); 
define('DB_NAME', 'users');

/* Attempt to connect to MySQL database */ 
$link = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME); 
// Check connection 
if($link === false)
{
    die("ERROR: Could not connect. " . mysqli_connect_error());
} 
?>

The search query will need to account for SQL Injection - How can I prevent SQL injection in PHP?.

<?php

require_once "include/dbconf.php";

// placeholder for the returned data
$data = array();

// Verify the search query is present
// Or handle empty 
if(isset($_POST['name']))
{
    // SQL injection - https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php
    $name=$_POST['name'];

    // TODO: Verify that you need the password here
    // Generally passwords are not to be stored as plain text
    $sql = "SELECT (id, name, surname, email, username, password) from net_users WHERE name LIKE '?'";

    $stmt = mysqli_prepare($link, $sql);
    mysqli_stmt_bind_param($stmt, 's', $name);

    // Execute the query
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);

    while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC))
    {
        // Copy the result to a local array
        // Each entry in $data will be an associative array of values
        $data[] = $row;
    }
} else {
    // TODO : Handle this more gracefully
    die('Search query missing');
}

mysqli_close($link);

if (empty($data))
{
    // TODO: No records matched, handle gracefully
    die('No records matched');
}
?>

Once you have the data, output as needed. Note that I have also selected id column - As all other fields are updateable, it would not be possible to identify the record if all the fields are changed. To work around this, you need a value that will always identify the record being updated. I have chosen the id column, but any other unique - non-updateable field would do.

<?php
foreach($data as $record)
{
?>
<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post">
    <input type="hidden" name="id" value="<?php echo $record['id']; ?>" />
    <div class="form-group">
         <label>Name</label>
         <input type="text" name="name" class="form-control" value="<?php echo $record['name']; ?>">
    </div>
    <div class="form-group">
         <label>Surname</label>
         <input type="text" name="surname" class="form-control" value="<?php echo $record['surname']; ?>">
    </div>
    <div class="form-group">
         <label>Email</label>
         <input type="email" name="email" class="form-control" value="<?php echo $record['email']; ?>">
    </div>
    <div class="form-group">
         <label>Username</label>
         <input type="text" name="username" class="form-control" value="<?php echo $record['username']; ?>">
    </div>
    <div class="form-group">
         <label>Password</label>
         <input type="text" name="password" class="form-control" value="<?php echo $record['password']; ?>">
    /div>
    <div class="form-group">
        <input type="update" class="btn btn-primary" value="update">
    </div>
</form>
<?php
}
?>