wordpress custom_post_type insert data to existing table wp_posts

Question

i've created plugin that works on existing table wp_posts inside wordpress database. for that i've created code for plugin that adds 3 fields to existing database when when activate plugin and also drop that 3 fields from existing database when deactivate plugin.

this is screenshot of my layout

i want when i click on update after adding values in fields then address,latitude and longitude fields displays that data to database.

here is code for it:

<?php
/**
 * Plugin Name: Hello World
 * Plugin URI: https://example.com/my-plugin
 * Description: This is a description of my hello world sample plugin.
 * Version: 2.3.1
 * Author: Selin
 * Author URI: https://example.com
 * License: GPL v2 or later
 * License URI: https://www.gnu.org/licenses/gpl-2.0.html
 */
include_once WP_PLUGIN_DIR . '/form-data/connection.php';
include_once(__DIR__. '\Admin\Store\createfile.php');
include_once(__DIR__ . '\Admin\Store\view.php');
//global $post;

function db_activate() {
  global $wpdb;
  $table = $wpdb->prefix . 'posts'; 
  $charset_collate = $wpdb->get_charset_collate();
  $sql = "ALTER TABLE $table
      ADD `address` VARCHAR(255) NOT NULL,
      ADD `longitude` VARCHAR(20) NOT NULL,
      ADD `latitude` VARCHAR(20) NOT NULL
  ;";
  $wpdb->query($sql);
  $wpdb->get_results("DESCRIBE $table");
}
register_activation_hook(__FILE__, 'db_activate');

function db_deactivate() {
    global $wpdb;
    $table = $wpdb->prefix . 'posts';
    $sql = "ALTER TABLE $table DROP COLUMN `address`, DROP COLUMN `longitude`, DROP COLUMN `latitude`";
    $wpdb->query($sql);
}
register_deactivation_hook(__FILE__, 'db_deactivate');


// custom-post-type
function custom_store_metaboxes() {
  add_meta_box(
      'store_location',
      'Store Location',
      'display_store',
      'store',
      'normal',
      'high'
  );
}

function display_store($post) {
  // Get existing address, latitude, and longitude values (if any)
  $address = get_post_meta($post->ID, 'address', true);
  $latitude = get_post_meta($post->ID, 'latitude', true);
  $longitude = get_post_meta($post->ID, 'longitude', true);
  ?>
  <label for="address">Address:</label><br>
  <input type="text" id="address" name="address" value="<?php echo esc_attr($address); ?>"/><br><br>

  <label for="latitude">Latitude:</label><br>
  <input type="text" name="latitude" id="latitude" value="<?php echo esc_attr($latitude); ?>" /><br><br>

  <label for="longitude">Longitude:</label><br>
  <input type="text" name="longitude" id="longitude" value="<?php echo esc_attr($longitude); ?>" /><br><br>
  <?php
}

function save_store_location_fields($post_id) {
  if (defined('DOING_AUTOSAVE') && DOING_AUTOSAVE) {
      return;
  }

  if (!current_user_can('edit_post', $post_id)) {
      return;
  }
    // Insert or update the address
  if (isset($_POST['address'])) {
    update_post_meta($post_id, 'address', sanitize_text_field($_POST['address']));
  }

  // Insert or update the latitude
  if (isset($_POST['latitude'])) {
      update_post_meta($post_id, 'latitude', sanitize_text_field($_POST['latitude']));
  }

  // Insert or update the longitude
  if (isset($_POST['longitude'])) {
      update_post_meta($post_id, 'longitude', sanitize_text_field($_POST['longitude']));
  }  
}
add_action('save_post_store', 'save_store_location_fields');

//wp_insert_post($address,$latitude,$longitude);
add_action('add_meta_boxes_store', 'custom_store_metaboxes');
//add_action('save_post_store', 'save_store_location_fields');

function register_custom_store_post_type() {
    $labels = array(
        'name'                  => _x( 'Store Locator', 'Post type general name', 'textdomain' ),
        'singular_name'         => _x( 'Store', 'Post type singular name', 'textdomain' ),
        'menu_name'             => _x( 'Stores', 'Admin Menu text', 'textdomain' ),
        'name_admin_bar'        => _x( 'Store', 'Add New on Toolbar', 'textdomain' ),
        'add_new'               => __( 'Add New', 'textdomain' ),
        'add_new_item'          => __( 'Add New Store', 'textdomain' ),
        'new_item'              => __( 'New Store', 'textdomain' ),
        'edit_item'             => __( 'Edit Store', 'textdomain' ),
        'view_item'             => __( 'View Store', 'textdomain' ),
        'all_items'             => __( 'All Stores', 'textdomain' ),
        'search_items'          => __( 'Search Stores', 'textdomain' ),
        'parent_item_colon'     => __( 'Parent Stores:', 'textdomain' ),
        'not_found'             => __( 'No stores found.', 'textdomain' ),
        'not_found_in_trash'    => __( 'No stores found in Trash.', 'textdomain' ),
        'featured_image'        => _x( 'Store Cover Image', 'Overrides the “Featured Image” phrase for this post type. Added in 4.3', 'textdomain' ),
        'set_featured_image'    => _x( 'Set cover image', 'Overrides the “Set featured image” phrase for this post type. Added in 4.3', 'textdomain' ),
        'remove_featured_image' => _x( 'Remove cover image', 'Overrides the “Remove featured image” phrase for this post type. Added in 4.3', 'textdomain' ),
        'use_featured_image'    => _x( 'Use as cover image', 'Overrides the “Use as featured image” phrase for this post type. Added in 4.3', 'textdomain' ),
        'archives'              => _x( 'Store archives', 'The post type archive label used in nav menus. Default “Post Archives”. Added in 4.4', 'textdomain' ),
        'insert_into_item'      => _x( 'Insert into store', 'Overrides the “Insert into post”/”Insert into page” phrase (used when inserting media into a post). Added in 4.4', 'textdomain' ),
        'uploaded_to_this_item' => _x( 'Uploaded to this store', 'Overrides the “Uploaded to this post”/”Uploaded to this page” phrase (used when viewing media attached to a post). Added in 4.4', 'textdomain' ),
        'filter_items_list'     => _x( 'Filter stores list', 'Screen reader text for the filter links heading on the post type listing screen. Default “Filter posts list”/”Filter pages list”. Added in 4.4', 'textdomain' ),
        'items_list_navigation' => _x( 'Stores list navigation', 'Screen reader text for the pagination heading on the post type listing screen. Default “Posts list navigation”/”Pages list navigation”. Added in 4.4', 'textdomain' ),
        'items_list'            => _x( 'Stores list', 'Screen reader text for the items list heading on the post type listing screen. Default “Posts list”/”Pages list”. Added in 4.4', 'textdomain' ),
    );

    $args = array(
        'labels'             => $labels,
        'description'        => 'Store custom post type.',
        'public'             => true,
        'publicly_queryable' => true,
        'show_ui'            => true,
        'show_in_menu'       => true,
        'query_var'          => true,
        'rewrite'            => array( 'slug' => 'store' ),
        'capability_type'    => 'post',
        'has_archive'        => true,
        'hierarchical'       => false,
        'menu_position'      => 10,
        'supports'           => array( 'title', 'editor', 'thumbnail', 'custom-fields' ),
        'menu_icon'          => 'dashicons-location', 
        'show_in_rest'       => true,
        'register_meta_box_cb' => 'custom_store_metaboxes'
    );  
    register_post_type('store', $args);
}
add_action('init', 'register_custom_store_post_type');
?>

i want to insert record at the fields address,latitude and longitude please help me with this code

here is my updated code as per your comments and reply i've created my new table :

<?php
/**
 * Plugin Name: Store Plugin 
 * Plugin URI: https://example.com/my-plugin
 * Description: This is a description of my hello world sample plugin.
 * Version: 2.3.1
 * Author: Khushbu 
 * Author URI: https://example.com
 * License: GPL v2 or later
 * License URI: https://www.gnu.org/licenses/gpl-2.0.html
 */
function db_active() {
  global $wpdb;
  $table = $wpdb->prefix . 'store_db'; 
  $charset_collate = $wpdb->get_charset_collate();
  $sql = "CREATE TABLE $table (
    store_id int(20) AUTO_INCREMENT PRIMARY KEY,
    store_name VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    image VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL,
    latitude VARCHAR(10) NOT NULL,
    longitude VARCHAR(10) NOT NULL
  ) $charset_collate;";  
  require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
  $result = dbDelta($sql);
}
register_activation_hook(__FILE__, 'db_active');

function db_deactive() {
  global $wpdb;
  $table = $wpdb->prefix . 'store_db'; 
  $sql = "DROP TABLE $table;";
  $wpdb->query($sql);
}
register_deactivation_hook(__FILE__, 'db_deactive');

function add_your_fields_meta_box() {
  add_meta_box(
    'your_custom_meta_box',          // ID of the meta box
    'Location Information',          // Title of the meta box
    'your_custom_meta_box_content',  // Callback function to generate the content
    'store_post',                     // Custom post type (your_post)
    'normal',                        // Placement - 'normal', 'advanced', 'side'
    'high'                           // Priority - 'high', 'core', 'default', 'low'
  );
}
add_action('add_meta_boxes', 'add_your_fields_meta_box');

function create_post_your_post() {
    register_post_type('store_post',
        array(
            'labels'       => array(
      'name'         => __('Store Post'),
            ),
            'public'       => true,
            'hierarchical' => true,
            'has_archive'  => true,
            'supports'     => array(
      'title',
      'editor',
      'excerpt',
      'thumbnail',
            ),
            'taxonomies'   => array(
      'post_tag',
      'category',
            )
        )
    );
    register_taxonomy_for_object_type('category', 'store_post');
    register_taxonomy_for_object_type('post_tag', 'store_post');
}

add_action('init', 'create_post_your_post');

function your_custom_meta_box_content($post) {
  $store_id = $post->ID;
  $image = get_post_meta($store_id,'image',true);
  $address = get_post_meta($store_id, 'address', true);
  $latitude = get_post_meta($store_id, 'latitude', true);
  $longitude = get_post_meta($store_id, 'longitude', true);
  ?>  
  <b><label for="your_meta_field">Image:</label></b>
  <?php
  if (!empty($image)) {
    echo '<br><img src="' . esc_url($image) . '" style="max-width: 200px;" /><br>';
  }
  ?>
  <input type="file" name="your_meta_field[image]" id="your_meta_field[image]">
  <?php
  // Check if there's a previously saved image URL and display it in the file input field
  if (!empty($image)) {
    echo '<br><small>Previously chosen image: ' . esc_html($image) . '</small>';
  } ?> <br><br>

  <b><label for="your_meta_field">Address:</label></b>
  <textarea name="your_meta_field[address]" id="your_meta_field[address]" rows="3" cols="30" style="width:500px;"><?php echo esc_attr($address);?></textarea><br><br>

  <b><label for="your_meta_field">Latitude:</label></b>
  <input type="text" name="your_meta_field[latitude]" id="your_meta_field[latitude]" value="<?php echo esc_attr($latitude); ?>"><br><br>

  <b><label for="your_meta_field">Longitude:</label></b>
  <input type="text" name="your_meta_field[longitude]" id="your_meta_field[longitude]" value="<?php echo esc_attr($longitude); ?>"><br><br>

  <?php wp_nonce_field(basename(__FILE__), 'your_meta_box_nonce'); ?>
  <?php
}
function save_your_custom_meta_box_data($post_id) {
  if (defined('DOING_AUTOSAVE') && DOING_AUTOSAVE) {
    return;
  }
  if (!isset($_POST['your_meta_box_nonce']) || !wp_verify_nonce($_POST['your_meta_box_nonce'], basename(__FILE__))) {
    return;
  }
  if ('store_post' !== get_post_type($post_id)) {
    return;
  }
  $meta_fields = isset($_POST['your_meta_field']) ? $_POST['your_meta_field'] : array();
  if (isset($_FILES['your_meta_field']['name'])) {  
      $image_url = media_handle_upload('your_meta_field', $post_id);
      if (!is_wp_error($image_url)) {
        update_post_meta($post_id, 'image', $image_url);
    }
 }
  if ( isset( $meta_fields['address'] ) ) {
    update_post_meta( $post_id, 'address', sanitize_text_field( $meta_fields['address'] ) );  
  }
  if ( isset( $meta_fields['latitude'] ) ) {
    update_post_meta( $post_id, 'latitude', sanitize_text_field( $meta_fields['latitude'] ) );  
  }
  if ( isset( $meta_fields['longitude'] ) ) {
    update_post_meta( $post_id, 'longitude', sanitize_text_field( $meta_fields['longitude'] ) );
  }

  if (isset($meta_fields['image']) && isset($meta_fields['address']) && isset($meta_fields['latitude']) && isset($meta_fields['longitude'])) {
    $store_name = get_the_title($post_id);
    $description = get_post_field('post_content', $post_id);
    $image = _sanitize_text_fields($meta_fields['image']);
    $address = sanitize_text_field($meta_fields['address']);
    $latitude = sanitize_text_field($meta_fields['latitude']);
    $longitude = sanitize_text_field($meta_fields['longitude']);

    global $wpdb;
    $table = $wpdb->prefix . 'store_db';
    $existing_record = $wpdb->get_row(
      $wpdb->prepare("SELECT * FROM $table WHERE store_id = %d", $post_id)
    );

    if ($existing_record) {
      // If a record exists, update the existing record
      $wpdb->update(
        $table,
        array(
          'store_name' => $store_name,
          'description' => $description,
          'image' => $image,
          'address' => $address,
          'latitude' => $latitude,
          'longitude' => $longitude,
        ),
        array('store_id' => $post_id),
        array('%s', '%s', '%s', '%s', '%s', '%s'),
        array('%d')
      );
    } else {
      // If no record exists, insert a new record
      $wpdb->insert(
        $table,
        array(
          'store_id' => $post_id,
          'store_name' => $store_name,
          'description' => $description,
          'image' => $image,
          'address' => $address,
          'latitude' => $latitude,
          'longitude' => $longitude,
        ),
        array('%d', '%s', '%s', '%s', '%s', '%s', '%s')
      );
    }
  }
}
add_action('save_post', 'save_your_custom_meta_box_data');
// my changes completed
?>

Answer 1

Oh my my my. Alarm bells are clanging loudly in my head. Please don't add columns to core tables. Why not?

• Core version updates (for example the soon-to-appear 6.2.2 to 6.3 update) have a procedure to restore the structure of core tables to standard. Either the core update will fail or it will helpfully remove your extra columns.

• Core has a caching subsystem that handles metadata like yours in the wp_postmeta table. You may as well take advantage of that and get the performance benefits.

• The WordPress ecosystem is full of code (core, plugins, themes) that use these tables. There are tens of thousands of plugins in the plugin repository, for example. There's no standard for correctness of that code except does it work? You'll encounter code that assumes it knows what columns are in the posts table and does strange things if other columns are present. I'm trying to be polite here. The truth is that the WordPress ecosystem contains a lot of bad code that basically works. You're inviting that bad code to bite you by doing something as strange as adding columns to a core table.

Somebody pointed out in comments that the wp_postmeta table is the place to put your data. That is correct. You're already using update_post_meta() to do that, more-or-less correctly.

By the way, the convention for WordPress plugins is to delete extra data when deleting a plugin, not when deactivating it.