help w/mysqli update

1Tsme1941 · Mar 8, 2021

Hi, I'm trying to update a database using MYSQLi using a form and I closed my eyes and got lost.
I think the form is correct but I'm not sure how to state that the update is good (it's not). help!
-----------------------------------------------------
<!DOCTYPE html><html lang="en">
<head>
</head>
<body bgcolor="#ccffff"><center>
<h2><b> record a payment</h2><br>
<form action="payment.php" method="post">

<label for="name">Tenant:</label>
<input type="text" name="tenant" id="tenant">
<label for="unit">Unit:</label>
<input type="text" name="unit" id="unit">
<label for="amtpaid">Amt paid:</label>
<input type="text" name="amtpaid" id="amtpaid"><br>

<input type="submit" name="submit" value="make a payment">
</form></body></html>
-----------------------------------------------------------------------
this is getprerentdb.php code:
<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
$DB_NAME = 'prerentdb'; $DB_HOST = 'localhost';
$DB_USER = 'root'; $DB_PASS = '';
$mysqli = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
if (mysqli_connect_errno())
{printf("Connect failed: %s\n", mysqli_connect_error());exit();}
?>
------------------------------------
this is php update code:
<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

//Open a new connection to the MySQL server
require_once "getprerentdb.php";

//MySqli Select Query
$results = $mysqli->query ('SELECT * FROM `payments`');

echo "<center>";echo date('m/d/y');echo "</center>";
$id="''";
$tenant = $_POST['tenant'];
$unit = $_POST['unit'];
$amtpaid = $_POST['amtpaid'];
?>

<html>
<head></head>
<body onunload="receipts.php"><center><b>Rent Payment</b><br>

<?php
if(!empty($_POST["update"]))
{
$mysqli->query("Update payments SET
amtpaid = '$amtpaid'
Where Id = $Id'") ;
}
?>
</tbody></table></form></center></body></html>

Cromewell · Mar 9, 2021

1Tsme1941 said:
Where Id = $Id'") ;

You have a capital I, but you create the variable with a lowercase i.

Also, this is a dangerous way to substitute variables into SQL, it is trivial to inject my own sql and have you run it. You want to use bind variables to prevent it: https://www.php.net/manual/en/mysqli-stmt.bind-param.php

1Tsme1941 · Mar 9, 2021

here is the code:

<?php
//Open a new connection to the MySQL server
require_once "getprerentdb.php";

echo "<center>";echo date('m/d/y');echo "</center>";
$id="''";
$tenant = $_POST['tenant'];
$unit = $_POST['unit'];
$amtpaid = $_POST['amtpaid'];
$rentdue = $_POST['rentdue'];
$prevbal = $_POST['prevbal'];
$hudpay = $_POST['hudpay'];
$tentpay = $_POST['tentpay'];
$datepaid = $_POST['datepaid'];
$late = $_POST['late'];
$comments = $_POST['comments'];
$paidsum = $_POST['paidsum'];

if(!empty($_POST["update"]))
{
$mysqli->query("Update payments SET
amtpaid = '$amtpaid', rentdue = '$rentdue', prevbal = '$prevbal', hudpay = '$hudpay',
tentpay = '$tentpay', datepaid = '$datepaid', late = '$late', comments = '$comments',
paidsum = '$paidsum'
Where id = $id'") ;
echo "Record for unit ".$_POST["unit"]." has been updated";
}
?>
</center></body></html>
----------------------------------------------------------------------
here is output:

03/09/2021

no message, no update

code:

Cromewell · Mar 9, 2021

Now it looks like you are not setting $id after initializing it to an empty string. $id = "''"; So when you say "where id = $id'" in your query it will probably not work. This results in a quoting issue around " where id = $id' " in your query. It effectively is " where id = ''' " now (3 single quotes in a row) which your database will be unhappy with.

1Tsme1941 · Mar 10, 2021

Hi, Further attempt. I'm trying to update a database record from a form. I fill out the form and submit,
The file table appears, I select the unit I want to update and click "update". RESULT follows.
-------------------------------------------------
CODE:

<?php
//Open a new connection to the MySQL server
require_once "getprerentdb.php";

//MySqli Select Query
$results = $mysqli->query ('SELECT * FROM payments');

$tenant = "tenant";
$unit = "unit";
$amtpaid = "amtpaid";
$rentdue = "rentdue";
$prevbal = "prevba";
$hudpay = "hudpay";
$tentpay = "tentpay";
$datepaid = "datepaid";
$late = "late";
$comments = "comments";
$paidsum = "paidsum";
?>
<html>
<head></head>
<body><center>
<hr>
<h2>payment update</h2>

<form method="post" action="#"><br />
<input type="text" name="unit"/>
<input type="submit" name="submit" value="select unit"/>

<table border='1' cellpadding='4'>
<thead>
<tr>
<th>tenant</th>
<th>unit</th>
<th>paid</th>
<th>due</th>
<th>prev</th>
<th>hud</th>
<th>tent</th>
<th>date</th>
<th>late</th>
<th>comments</th>
<th>paidsum</th>
</tr>
</thead>
<tbody>
<?php
// ---------------------------------------
while($row = mysqli_fetch_array($results)) {
// ----------------------------------------
echo "<tr>
<td>{$row['tenant']}</td>
<td>{$row['unit']}</td>
<td>{$row['amtpaid']}</td>
<td>{$row['rentdue']}</td>
<td>{$row['prevbal']}</td>
<td>{$row['hudpay']}</td>
<td>{$row['tentpay']}</td>
<td>{$row['datepaid']}</td>
<td>{$row['late']}</td>
<td>{$row['comments']}</td>
<td>{$row['paidsum']}</td>
</tr>";
}
?>
</tbody></table>
<input type='submit' name='update' value='Update Record' />
<?php

if(!empty($_POST["update"]))
{
$mysqli->query("Update payments SET
amtpaid = '$amtpaid', rentdue = '$rentdue', prevbal = '$prevbal', hudpay = '$hudpay',
tentpay = '$tentpay', datepaid = '$datepaid', late = '$late', comments = '$comments',
paidsum = '$paidsum'
Where unit = $unit'") ;
echo "Record for unit ".$_POST["unit"]." has been updated";
}
?>
</form><center></body></html>
-------------------------------------------------
RESULT:
Record for unit apt3 has been updated (Not true)

Cromewell · Mar 11, 2021

Anything in $msqli->error after the update query? How about the anything in your php log?

1Tsme1941 · Mar 11, 2021

Php log says "system cannot find the path specified".
Ok guys I'm lost. I cannot find how to code update confirmation.
--------------------------------------------
here is the new code:

<?php
//Open a new connection to the MySQL server
require_once "getprerentdb.php";

//MySqli Select Query
$results = $mysqli->query ('SELECT * FROM payments');

$tenant = "tenant";
$unit = "unit";
$amtpaid = "amtpaid";
$rentdue = "rentdue";
$prevbal = "prevba";
$hudpay = "hudpay";
$tentpay = "tentpay";
$datepaid = "datepaid";
$late = "late";
$comments = "comments";
$paidsum = "paidsum";
?>
<html>
<head></head>
<body><center>
<hr>
<h2>payment update</h2>

<form method="post" action="#"><br />
<input type="text" name="unit"/>
<input type="submit" name="submit" value="select unit"/>

<table border='1' cellpadding='4'>
<thead>
<tr>
<th>tenant</th>
<th>unit</th>
<th>paid</th>
<th>due</th>
<th>prev</th>
<th>hud</th>
<th>tent</th>
<th>date</th>
<th>late</th>
<th>comments</th>
<th>paidsum</th>
</tr>
</thead>
<tbody>
<?php
// ---------------------------------------
while($row = mysqli_fetch_array($results)) {
// ----------------------------------------
?>
<tr>
<td><?php echo $data['id']; ?></td>
<td><?php echo $data['tenant']; ?></td>
<td><?php echo $data['unit']; ?></td>
<td><?php echo $data['amtpaid']; ?></td>
<td><?php echo $data['rentdue']; ?></td>
<td><?php echo $data['prevbal']; ?></td>
<td><?php echo $data['hudpay']; ?></td>
<td><?php echo $data['tentpay']; ?></td>
<td><?php echo $data['datepaid']; ?></td>
<td><?php echo $data['late']; ?></td>
<td><?php echo $data['comments']; ?></td>
<td><?php echo $data['paidsum']; ?></td>
</tr>
<?php
}
?>
</tbody></table>
<input type='submit' name='update' value='Update Record' />
<?php

if(!empty($_POST["update"]))
{

// Perform a query, check for error
if (!$mysqli -> query("Update payments SET
amtpaid = '$amtpaid', rentdue = '$rentdue', prevbal = '$prevbal', hudpay = '$hudpay',
tentpay = '$tentpay', datepaid = '$datepaid', late = '$late', comments = '$comments',
paidsum = '$paidsum'

// ---------------------------------------------
WHERE id='".$_POST['id']."'")
mysqli_query($sql) or die(mysql_error());
// -------------------------------------------------

echo "Record for unit ".$_POST["unit"]." has been updated";
echo "</form>";
?>

</b></center></body></html>

Cromewell · Mar 12, 2021

1Tsme1941 said:
Php log says "system cannot find the path specified".

Sounds like the same error you were getting in the other thread. It is probably worth trying to find what file it is looking for and fixing that.

1Tsme1941 said:
// Perform a query, check for error
if (!$mysqli -> query("Update payments SET
amtpaid = '$amtpaid', rentdue = '$rentdue', prevbal = '$prevbal', hudpay = '$hudpay',
tentpay = '$tentpay', datepaid = '$datepaid', late = '$late', comments = '$comments',
paidsum = '$paidsum'

// ---------------------------------------------
WHERE id='".$_POST['id']."'")
mysqli_query($sql) or die(mysql_error());
// -------------------------------------------------

This looks like an error. $sql is only mentioned here. You are executing a query, that likely is a syntax error because of the //----- in the string, then executing a second query with a empty variable (uninitialized, so should be created as a blank) then checking the error.
I do not know if // comments inside a query either, pretty sure it does not. /* comment */ will or -- to comment out a line.

1Tsme1941 · Mar 13, 2021

Cromewell said:
Sounds like the same error you were getting in the other thread. It is probably worth trying to find what file it is looking for and fixing that.

This looks like an error. $sql is only mentioned here. You are executing a query, that likely is a syntax error because of the //----- in the string, then executing a second query with a empty variable (uninitialized, so should be created as a blank) then checking the error.
I do not know if // comments inside a query either, pretty sure it does not. /* comment */ will or -- to comment out a line.

Alright, I suspected the code but don't know how to correct. Didn't suspect the incorrect comments.
. I removed the comments, now message reads
"Parse error: syntax error, unexpected 'mysqli_query' (T_STRING) in C:\xampp\htdocs\oldproperty\payment.php on line 82" .

Cromewell · Mar 13, 2021

You are missing a closing bracket ), after the $mysqli->query(....). There should be 2 at the end, not just 1.

1Tsme1941 · Apr 15, 2021

thanks solved

help w/mysqli update

1Tsme1941

Member

Cromewell

Administrator

1Tsme1941

Member

Cromewell

Administrator

1Tsme1941

Member

Cromewell

Administrator

1Tsme1941

Member

Cromewell

Administrator

1Tsme1941

Member

Cromewell

Administrator

1Tsme1941

Member