Updating database records not working

PHP
1

Hi guys, I blinked and got lost. All I want to do is copy Payfile to Pmtfile and then update records in
Payfile that have been paid(amtpaid !=‘’). The update doesn’t happen and the insert-note comments at botom of post.

<?php
$link = mysqli_connect("localhost", "root", "", "prerentdb"); 
// Check connection
if($link === false){ die("ERROR: Could not connect. " . mysqli_connect_error()); } 

 $id = ' ';
     $tenant = 'tenant';
     $unit = 'unit';
     $amtdue = 'amtdue';
     $amtpaid = 'amtpaid';          
     $dueday = 'dueday';
     $late = 'late';
     $prevbal = 'prevbal'; 
     $latechg = 'latechg';
     $secdep = 'secdep';
     $damage = 'damage'; 
     $courtcost = 'courtcost';
     $nsf = 'nsf';
     $chgmoyr = 'chgmoyr';
     $hudpay = 'hudpay';
     $datepaid = 'datepaid';
     $paidsum = 'paidsum';
     $comments = 'comments';
     $phone = 'phone';
     $cell = 'cell';
    
// Attempt select query execution
$result = mysqli_query($link,"SELECT * FROM payfile");
 while($row = mysqli_fetch_array($result))  {

//
//MySqli Insert Query *** inserts strings, not values ***
 $sql = "INSERT INTO pmthist (tenant, unit, amtdue, amtpaid, dueday, late, hudpay, paidsum, 
 datepaid, prevbal, latechg, secdep, damage, courtcost, nsf, chgmoyr, comments, phone, cell) 
VALUES('$tenant', '$unit', '$amtdue', '$amtpaid', '$dueday', '$late',  '$hudpay', '$paidsum', 
'$datepaid', '$prevbal', '$latechg', '$secdep', '$damage', '$courtcost', '$nsf', '$chgmoyr',
'$comments', '$phone', '$cell')"; 
if ($link->query($sql) === TRUE) { echo "New records created"; } 
else { echo "Error: " . $sql . "<br>" . $link->error; }

// Perform a query  *** doesn't update ***
$sql = "UPDATE payfile set 
amtpaid = '', late ='', hudpay ='',  paidsum ='', datepaid ='', 
latechg = '', secdep = '', damage = '', courtcost = '', nsf = '', chgmoyr = ' ', comments =' '  
WHERE amtpaid =!''";
if ($link->query($sql) === TRUE) { echo "payfile refreshed"; } 
else { echo "Error: " . $sql . "<br>" . $link->error; }
}
?>

This is a record that was supposed to be inserted:
SELECT * FROM `payfile`
id  tenant  unit amtdue	amtpaid	
1   tenant1 apt1 530.00  530.00
------------------------------------
This is a record that was inserted:
SELECT * FROM `pmthist`
id tenant unit amtdue amtpaid
44 tenant unit 	0.00  0.00
2

INSERT INTO SELECT syntax. No php looping required; make the database do the work.

INSERT INTO pmthist (tenant, unit, amtdue, amtpaid, dueday, late, hudpay, paidsum, datepaid, prevbal, latechg, secdep, damage, courtcost, nsf, chgmoyr, comments, phone, cell) 
SELECT tenant, unit, amtdue, amtpaid, dueday, late, hudpay, paidsum, datepaid, prevbal, latechg, secdep, damage, courtcost, nsf, chgmoyr, comments, phone, cell 
FROM payfile;`

One of these things is not like the other~
(Also, shouldnt amtpaid be a number, not a string??)

3

this is actually the code: WHERE amtpaid =!''";

should I use :$tenant = 'tenant'; $unit = 'unit'; $amtdue = = (int)$c; $amtpaid = = (int)$c; $dueday = 'dueday'; $late = 'late'; $prevbal = = (int)$c; $latechg = = (int)$c; $secdep = (int)$c; $damage = = (int)$c; $courtcost = = (int)$c; $nsf = = (int)$c; $chgmoyr = 'chgmoyr'; $hudpay = = (int)$c; $datepaid = 'datepaid'; $paidsum = = (int)$c; $comments = 'comments'; $phone = 'phone'; $cell = 'cell';?
if so how, are the other variables coded right?

5

And this is me telling you the code is wrong:

What i’m saying is you shouldnt be doing that code at all. Use a single INSERT INTO SELECT query, and you dont need to SELECT, then loop through and INSERT each - the database will do it all for you with INSERT INTO SELECT (as long as the field names are the same…)

Then you can do the UPDATE query to reset payfile.

6

You should be using prepared statements, no matter what the source of a data that’s being used in a query

1 Like
7

Another problem with this code is the same as you have in other code that you have recently posted on here.

When you do this piece of code:

 $sql = "INSERT INTO pmthist (tenant, unit, amtdue, amtpaid, dueday, late, hudpay, paidsum, 
 datepaid, prevbal, latechg, secdep, damage, courtcost, nsf, chgmoyr, comments, phone, cell) 
VALUES('$tenant', '$unit', '$amtdue', '$amtpaid', '$dueday', '$late',  '$hudpay', '$paidsum', 
'$datepaid', '$prevbal', '$latechg', '$secdep', '$damage', '$courtcost', '$nsf', '$chgmoyr',
'$comments', '$phone', '$cell')"; 
if ($link->query($sql) === TRUE) { echo "New records created"; }

all you are doing is inserting a series of strings into your database table. You assign values to all of those variables at the top of your code:

     $tenant = 'tenant';
     $unit = 'unit';
     $amtdue = 'amtdue';
     $amtpaid = 'amtpaid';          
     $dueday = 'dueday';
     $late = 'late';
... and so on

and those are the strings that will be inserted into your pmthist table. You’re not using the values that you retrieve in your query to get values from payfile at all. I know that mysqli can use bind_result to retrieve query results into distinct variables like this, and I don’t know much about mysqli, but it doesn’t seem that you’re using that anywhere.

I’m not sure how it gets ‘44’ for the id value, but I can see why it inserts “tenant” and “unit” into those columns - because those are the values that you assign to the $tenant and $unit variables before you execute the INSERT query.

Leaving aside all the other comments about how you need to use prepared statements and can do this specific task inside a single query, the biggest issue is that your initial query returns values in the $row array, but you don’t use them anywhere.

Can you show your database table layouts? That might give some assistance with the other issues. As @m_hutley asked, surely those columns in the payfile table should be numeric, not strings? So you should reset them by setting them to zero, not to a null string. It may be that’s why you’re getting 0 in the last two columns in pmthist - you’re trying to insert strings "amtdue" and "amtpaid" into numeric columns, and something is obligingly converting them into numbers instead of just giving you an error.

As an exercise, if you write a short bit of code to just select * from payfile and display the results on the browser window, does it work for you?

8 
/*
 * As long as you have the correct field names as the key and
 * the correct values in the corresponding keys the following
 * procedural function should work with no problem.
 *
 */


function insertData(array $data, $pdo, $table) {
    try {
        /* Initialize an array */
        $attribute_pairs = [];

        /*
         * Set up the query using prepared states with the values of the array matching
         * the corresponding keys in the array
         * and the array keys being the prepared named placeholders.
         */
        $sql = 'INSERT INTO ' . $table . ' (' . implode(", ", array_keys($data)) . ')';
        $sql .= ' VALUES ( :' . implode(', :', array_keys($data)) . ')';

        /*
         * Prepare the Database Table:
         */
        $stmt = $pdo->prepare($sql);

        /*
         * Grab the corresponding values in order to
         * insert them into the table when the script
         * is executed.
         */
        foreach ($data as $key => $value)
        {
            if($key === 'id') { continue; } // Don't include the id:
            $attribute_pairs[] = $value; // Assign it to an array:
        }

        return $stmt->execute($attribute_pairs); // Execute and send boolean true:

    } catch (PDOException $e) {

        /*
         * echo "unique index" . $e->errorInfo[1] . "<br>";
         *
         * An error has occurred if the error number is for something that
         * this code is designed to handle, i.e. a duplicate index, handle it
         * by telling the user what was wrong with the data they submitted
         * failure due to a specific error number that can be recovered
         * from by the visitor submitting a different value
         *
         * return false;
         *
         * else the error is for something else, either due to a
         * programming mistake or not validating input data properly,
         * that the visitor cannot do anything about or needs to know about
         *
         * throw $e;
         *
         * re-throw the exception and let the next higher exception
         * handler, php in this case, catch and handle it
         */

        if ($e->errorInfo[1] === 1062) {
            return false;
        }

        throw $e;
    } catch (Exception $e) {
        echo 'Caught exception: ', $e->getMessage(), "\n"; // Not for a production server:
    }

    return true;
}

function updateData(array $data, $pdo, $table): bool
{
    /* Initialize an array */
    $attribute_pairs = [];

    /* Create the prepared statement string */
    foreach ($data as $key => $value)
    {
        if($key === 'id') { continue; } // Don't include the id:
        $attribute_pairs[] = "{$key}=:{$key}"; // Assign it to an array:
    }

    /*
     * The sql implodes the prepared statement array in the proper format
     * and updates the correct record by id.
     */
    $sql  = 'UPDATE ' . $table . ' SET ';
    $sql .= implode(", ", $attribute_pairs) . ' WHERE id =:id';

    /* Normally in two lines, but you can daisy-chain pdo method calls */
    $pdo->prepare($sql)->execute($data);

    return true;
}

I know something like this can be done with mysqli, but using PDO is so much simpler to do something like the above. Here’s a good resource for PDO → https://phpdelusions.net/pdo

Maybe you can use an array for the updating of the one database table then just use the update function or some kind of modification to it?

Anyways make an array like so?

$data = ['tenant' => 'Name of Tenant', 'unit' => 100, 'amtdue' => 150.25 // and so on];

Of course the array keys such as tenant would have to be name the very same way as the database table’s column name is in order for something like the above to work. That way you can use not only use it to transfer the data over to the new table (or whatever you are doing) and continue to use it?

9

You can even take in one step further in an HTML form

Here’s an example of what I’m talking about:

<form id="formData" class="checkStyle" action="create.php" method="post" enctype="multipart/form-data">
    <input type="hidden" name="cms[user_id]" value="3">
    <input type="hidden" name="cms[author]" value="<?= Login::full_name() ?>">
    <input type="hidden" name="action" value="upload">
    <div class="file-style">
        <input id="file" class="file-input-style" type="file" name="image">
        <label for="file">Select file</label>
    </div>
    <select class="select-css" name="cms[page]">
        <option value="index">Home</option>
        <option value="blog" selected>Blog</option>
        <option value="about">About</option>
    </select>
    <div class="heading-style">
        <label class="heading_label_style" for="heading">Heading</label>
        <input class="enter_input_style" id="heading" type="text" name="cms[heading]" value="" tabindex="1" required
               autofocus>
    </div>
    <div class="content-style">
        <label class="text_label_style" for="content">Content</label>
        <textarea class="text_input_style" id="content" name="cms[content]" tabindex="2"></textarea>
    </div>
    <div class="submit-button">
        <button class="form-button" type="submit" name="submit" value="enter">submit</button>
    </div>
</form>
10

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.