PHP MySQL Statements not Updating Database

Question

After much editing and checking tutorial sites. Code currently not calling info from Database and when clicking Approve button, does not edit database. I do have a column identifier named Reg_ID which can specify which column of data you choose to edit. The form is submitting, just clears the information that I enter in and doesn't store the data.

This file is named Approve Deny Prayer Request.

<?php
$DB_HOST = "XXXXXXX";
$DB_NAME = "XXXXXXX";
$DB_PASS = "XXXXXXX";
$DB_USER = "XXXXXXX";

$link = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
if($link->connect_errno > 0) {
die('Connection failed [' . $db->connect_error . ']');
}

$query = "SELECT * FROM Request";
$result = mysqli_query($link,$query); //<----- Added link
$row = mysqli_fetch_array($result);

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

$id = mysqli_real_escape_string($link,$_POST['id']);
$firstname = mysqli_real_escape_string($link,$_POST['first']);
$lastname = mysqli_real_escape_string($link,$_POST['last']);
$phone = mysqli_real_escape_string($link,$_POST['phone']);

$query2=mysqli_query($link,"UPDATE Request SET Reg_F_Name='$firstname',     Reg_L_Name='$lastname',Reg_Request='$phone' WHERE id='$id'" );

if($query2){
header("Location: fbcaltusprayerorg.ipagemysql.com");
}

} // brace if(isset($_POST['add']))

?>

<form action="" method="post">

<table>
<input type="hidden" name="id" value="<? echo "$row[Reg_ID]" ?>">

<tr>
<td>First Name:</td>
<td><input type="text" name="first" value="<? echo "$row[Reg_F_Name]" ?>"></td>
</tr>

<tr>
<td>Last Name:</td>
<td><input type="text" name="last" value="<? echo "$row[Reg_L_Name]" ?>"></td>
</tr>

<tr>
<td>Prayer Request:</td>
<td><input type="text" name="phone" value="<? echo "$row[Reg_Request]" ?>"></td>
</tr>

</table>
<input name="add" type="submit" id="add" value="Approve Prayer Request">

</form>

Answer 1

Firstly, your initial code did not contain an opening <form> tag; that has been included below.

The way you're attempting to run your code is leaving you open to SQL injection.

• Use prepared statements, or PDO

Now, here's what you need to do.

• Create a column named id and set it to AUTO_INCREMENT if needed, but not required; just as long as there is some data related to it and holds a unique name/id.
• Create a hidden field called/named id

Then use UPDATE along with SET and a WHERE clause.

Sidenote: This will automatically redirect you to the page's filename you've called it.

In this example, I used header("Location: http://www.example.com/update.php");

Replace the DB credentials with your own.

<?php
$DB_HOST = "xxx";
$DB_NAME = "xxx";
$DB_PASS = "xxx";
$DB_USER = "xxx";

$link = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
if($link->connect_errno > 0) {
  die('Connection failed [' . $db->connect_error . ']');
}

$query = "SELECT * FROM Request";
$result = mysqli_query($link,$query); //<----- Added link
$row = mysqli_fetch_array($result);

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

$id = mysqli_real_escape_string($link,$_POST['id']);
$firstname = mysqli_real_escape_string($link,$_POST['first']);
$lastname = mysqli_real_escape_string($link,$_POST['last']);
$phone = mysqli_real_escape_string($link,$_POST['phone']);

$query2=mysqli_query($link,"UPDATE Request SET Reg_F_Name='$firstname', Reg_L_Name='$lastname',Reg_Request='$phone' WHERE id='$id'" );

if($query2){
header("Location: http://www.example.com/update.php");
}

} // brace if(isset($_POST['add']))

?>

<form action="" method="post">

<table>
<input type="hidden" name="id" value="<? echo "$row[id]" ?>">

<tr>
<td>First Name:</td>
<td><input type="text" name="first" value="<? echo "$row[Reg_F_Name]" ?>"></td>
</tr>

<tr>
<td>Last Name:</td>
<td><input type="text" name="last" value="<? echo "$row[Reg_L_Name]" ?>"></td>
</tr>

<tr>
<td>Prayer Request</td>
<td><input type="text" name="phone" value="<? echo "$row[Reg_Request]" ?>"></td>
</tr>

</table>
<input name="add" type="submit" id="add" value="Approve Prayer Request">

</form>

Answer 2

where is the call to update the database with your sql statement?

I have a function that normally I just for update of the database. I also make sure to add column for each table like UpdateDtTm and add that to the end of my update. That way you know you are going to always update something on an update statement. Also make sure to use a key and a unique id to make sure you only update the row you want.

Also, try using this syntax

$query2 = "Update Request set Reg_F_Name = $row[Reg_F_Name], Reg_L_Name = $row['Reg_L_Name],    Reg_Request = $row['Reg_Request'], UpdateDtTM = Now() where <A UNIQUE KEY ROW> = <UNIQUE ID>. 

 $result = db_update ("updating request in some location", $sql,"update");


 function db_update($function_name,$sql,$type) {

    // Get access to PHP global variables
    global $database;
    //if the database value is not pulled from the global array make sure
    //the system has it based on the Session value set on load
    if (! $database) {
        $database = $_SESSION['database'];
    }

    // Now authenticate the user with the database
    $db = db_connect($database);
    // Run SQL Query
mysql_query($sql);
// Mysql won't return a $result for UPDATE, so have to test with mysql_affected_rows
// mysql also won't do an update if the values are the same, so you could
// possibly have an instance where nothing is change and this fails
// got around this by adding an updated column that is increased by 1 everytime
// an update is performed.  this ensures that you always have something updated
if ( mysql_affected_rows()==0 ) {

    // Unable to update
    $error = "db_update error<br>$sql<br>".mysql_errno()." - ".mysql_error();
    database_error($error,$sql);

    // Exit the function after error
    exit;

}

// Do nothing for this guy
// We don't need to return anything
return;

}