How to properly insert data into custom table via plugin

Question

I have a plugin that reads data from an uploaded file and runs queries on the wp database. These inserts are into a table created by my plugin, so they aren't the post type.

This is how I got it working:

$connection = mysqli_connect($db_host, $db_user, $db_pass, $db_name);
mysqli_query($connection, $query);

The problem with this is that I have to declare these variables ($db_host, $db_user, $db_pass, $db_name) inside the plugin source code.

Is there a way I can insert data into my custom tables with the wp api? Or is there a way I can pull these variables from an external file that I can ignore in a version control setup?

Answer 1

That's not a good practice trying to connect the DB by your own methods while WP does it for you initially.

---

The problem with this is that I have to declare these variables ($db_host, $db_user, $db_pass, $db_name) inside the plugin source code.

All these properties are defined in wp-config.php file, located in the root area.

If you were to get these constants then, just include the file and call the constants, or use REGEX (better use REGEX, because there might be other callbacks that require loading WordPress)

// loading the config file to pull the DB_* constants
$dirname = dirname(__FILE__);
$root = false !== mb_strpos( $dirname, 'wp-content' ) ? mb_substr( $dirname, 0, mb_strpos( $dirname, 'wp-content' ) ) : $dirname;
// if $root is not correct, provide a static path then, $root = '/path/to/root/dir'
// assuming constants are ready (wp is configured), let's get them.
require_once( $root . "wp-config.php" );
echo var_dump(
    'DB name', DB_NAME,
    'DB user', DB_USER,
    'DB password', DB_PASSWORD,
    'DB host', DB_HOST
);

---

Here's a better solution:

Load WordPress

require( '/wp-blog-header.php' ); You should provide a working path to that file!

To test if you have loaded WordPress successfully, dump out something:

add_action("wp", function() { 
    echo sprintf( "Yes! I am creating with WordPress v. %s!\n", get_bloginfo("version") );
    exit("I exits\n");
});

Now use WordPress DB API

To insert data, there's this wpdb::insert you should use. Here the syntax

$wpdb->insert( $table, $data, $format ); and example use:

$wpdb->insert( 
    'messages', 
    array( 
        'PM_ID'     => (int) $pm_id,
        'sender'    => $current_user->ID,
        'recipient' => (int) $recipient,
        'message'   => "Hello!\n",
        'date'      => time()
    )
);
$record_id = $wpdb->insert_id;

In the example, the array in $wpdb->insert's 2nd param is an array with indexes as columns names, and values to be inserted for these cols in an independent record that you can get its ID with $wpdb->insert_id which gets last record insert ID in that table.

Hope that helps, if at least you stay away from SQL injection using $wpdb::insert or prepared statements instead of direct queries.

Answer 2

Why not study about how $wpdb works. Visit https://codex.wordpress.org/Class_Reference/wpdb

Here is a example of how to insert data to database using $wpdb

<?php
  global $wpdb;

  $wpdb->insert( 
    'table_name_here', 
    array( 
        'column1' => 'value1', 
        'column2' => 123 
    ), 
    array( 
        '%s', 
        '%d' 
    ) 
 );
?>

Visit https://codex.wordpress.org/Creating_Tables_with_Plugins for more .

Answer 3

<?php 
global $wpdb;
 $table="test_table";
$store_arr["name"]="test";
$store_arr["email"]="t[email protected]";

$wpdb->insert( $table, $store_arr);
 ?>