How to enter data into a tables field using PHP

Hi,

I think it is better to work with WordPress database functions. $wpdb

I gave an example of UPDATE. It is a bit laborious example but it works. I tried. UpdateData () is your function.

<?php
// Exemple CREATE TABLE and UPDATE data on  WP

function CreateTestTable (){
    global $wpdb;
    $table_name = $wpdb->prefix . 'rcpPay_Test';
    $charset_collate = $wpdb->get_charset_collate();

    $sqlTable = "CREATE TABLE IF NOT EXISTS $table_name (
      ID int(11) NOT NULL AUTO_INCREMENT,
      amount_vat varchar(255),
      vario varchar(255),
      PRIMARY KEY  (id)
    ) $charset_collate;";

    if ( !function_exists('\dbDelta')) { require_once(ABSPATH . 'wp-admin/includes/upgrade.php'); }

    dbDelta( $sqlTable );
}
CreateTestTable();

function InsertData ()  { 
    global $wpdb;
    $table_name = $wpdb->prefix . 'rcpPay_Test';

    $wpdb->insert( $table_name, [ 'amount_vat' => '0,05', 'vario' => 'ins 1' ] );

    $wpdb->insert( $table_name, [ 'amount_vat' => '0,10', 'vario' => 'ins 2' ] );

    $wpdb->insert( $table_name, [ 'amount_vat' => '0,15', 'vario' => 'ins 3' ] );
}
InsertData();


//And therefore, finally, your code :
function UpdateData () { 
    $vat = '0,22'; $payment_ID = 3;

    global $wpdb;
    $table_name = $wpdb->prefix . 'rcpPay_Test';

    $wpdb->update(
        $table_name,
        [ 'amount_vat' => $vat, 'vario'  => 'modified' ],
        [ 'ID' => $payment_ID ] 
    );
}
UpdateData();

Greetings.

alessandro12’s example is very informative and helpful, but in case it was not already obvious to you, they are creating a table to test with since we don’t have your table in our DBs. You don’t need to create a table, but you do need to use your correct table name.

We’re assuming your table is within your WP DB, so they use the $wpdb global connection object. If your table lies within a different DB, you’d need to make your own connection.

I agree that using $wpdb->insert() is preferable, but if you really want to use your SQL instead, you could use $wpdb->query( $wpdb->prepare());

I suggest $wpdb->prepare() because it sanitizes any variables. It is essential that any values entered into your table to first be sanitized at some point. prepare() requires sprintf() like syntax. For example:
$wpdb->prepare("UPDATE $rcp_payment SET amount_vat = %f WHERE ID = %d;", $vat, $payment_ID );

If you are sure the variables are already sanitized somewhere and you want to use your original SQL, because of how PHP parses double quoted strings, you could simplify your code to make it easier to read. FYI, this would have the exact same result as your code:
$update_sql = "UPDATE $rcp_payment SET amount_vat = '$vat' WHERE ID = '$payment_ID'";

In double quoted strings, PHP will automatically concatenate variable values without the need to break up the sting into fragments and use of the . operator.

Neither of the two suggestions works.

The fields that should be updated are in the same table (belonging to a wp plugin) that is used throughout the context of the PHP file. The values are calculated and saved as variables in the file (the tax and the net amount of the total amount), and should be saved in addition to the existing values in other fields of the data record (‘amount_vat’ and ‘amount_net’).
The table is called lnKzptkWrcp_payments or rcp_payments (“lnKzptkW” is the prefix).

In the meantime, I’ve managed it. There were still a few basic syntax errors in PHP, which I have now found and fixed.
Thank you for your tips.

• This reply was modified 1 year, 1 month ago by almute.