Create Custom Post Type in Wordpress from database data

Question

I have a table on the database, called a garage, made up like this

_____________________________________________
|   Brand   | Model   | Color     | Km      |
--------------------------------------------
| Fiat      | Panda   | Black     | 55448   |
---------------------------------------------
| Opel      | Corsa   | Red       | 441155  |

I would like to know if I can create a custom post type to view this data.

I created the custom post type, the code is

function create_auto_cpt() {

    $labels = array(
        'name' => _x( 'Auto', 'Post Type General Name', 'Auto' ),
        'singular_name' => _x( 'Auto', 'Post Type Singular Name', 'Auto' ),
        'menu_name' => _x( 'Auto', 'Admin Menu text', 'Auto' ),
        'name_admin_bar' => _x( 'Auto', 'Add New on Toolbar', 'Auto' ),
        'archives' => __( 'Archivi Auto', 'Auto' ),
        'attributes' => __( 'Attributi delle Auto', 'Auto' ),
        'parent_item_colon' => __( 'Genitori Auto:', 'Auto' ),
        'all_items' => __( 'Tutti le Auto', 'Auto' ),
        'add_new_item' => __( 'Aggiungi nuova Auto', 'Auto' ),
        'add_new' => __( 'Nuovo', 'Auto' ),
        'new_item' => __( 'Auto redigere', 'Auto' ),
        'edit_item' => __( 'Modifica Auto', 'Auto' ),
        'update_item' => __( 'Aggiorna Auto', 'Auto' ),
        'view_item' => __( 'Visualizza Auto', 'Auto' ),
        'view_items' => __( 'Visualizza le Auto', 'Auto' ),
        'search_items' => __( 'Cerca Auto', 'Auto' ),
        'not_found' => __( 'Nessun Auto trovato.', 'Auto' ),
        'not_found_in_trash' => __( 'Nessun Auto trovato nel cestino.', 'Auto' ),
        'featured_image' => __( 'Immagine in evidenza', 'Auto' ),
        'set_featured_image' => __( 'Imposta immagine in evidenza', 'Auto' ),
        'remove_featured_image' => __( 'Rimuovi immagine in evidenza', 'Auto' ),
        'use_featured_image' => __( 'Usa come immagine in evidenza', 'Auto' ),
        'insert_into_item' => __( 'Inserisci nelle Auto', 'Auto' ),
        'uploaded_to_this_item' => __( 'Caricato in questo Auto', 'Auto' ),
        'items_list' => __( 'Elenco degli Auto', 'Auto' ),
        'items_list_navigation' => __( 'Navigazione elenco Auto', 'Auto' ),
        'filter_items_list' => __( 'Filtra elenco Auto', 'Auto' ),
    );
    $args = array(
        'label' => __( 'Auto', 'Auto' ),
        'description' => __( 'Auto', 'Auto' ),
        'labels' => $labels,
        'menu_icon' => 'dashicons-admin-tools',
        'supports' => array(),
        'taxonomies' => array(),
        'public' => true,
        'show_ui' => true,
        'show_in_menu' => true,
        'menu_position' => 5,
        'show_in_admin_bar' => true,
        'show_in_nav_menus' => true,
        'can_export' => true,
        'has_archive' => true,
        'hierarchical' => false,
        'exclude_from_search' => false,
        'show_in_rest' => true,
        'publicly_queryable' => true,
        'capability_type' => 'post',
    );
    register_post_type( 'auto', $args );

}
add_action( 'init', 'create_auto_cpt', 0 );

now how do I go about populating it with the data in the db? Can you help me ?

Code to create 2 metabox.

add_action( 'admin_init', 'my_admin' );

function my_admin() {
    add_meta_box( 'Car_review_meta_box',
        'Informazioni Auto',
        'display_Car_review_meta_box',
        'Car_reviews', 'normal', 'high'
    );
}

?>
<?php

function display_Car_review_meta_box( $Car_review ) 
{

    ?>
    <table>
        <tr>
            <td style="width: 50%">Marca</td>
            <td><input type="text" size="40" name="garage" value="" /></td>
        </tr>
        <tr>
            <td style="width: 50%">Modello</td>
            <td><input type="text" size="40" name="garage" value="" /></td>       
        </tr>
    </table>
    <?php
}
?>

now how can I populate them with data from the database?

Answer 1

Ok. Basing on the details you shared above, this tool me a little time to debug. I hope it helps you. I have optimized the code to have some performance.

I added one item to your DB so we can use it as a unique Identifier. See image below. The ID auto increments or adds itself.

I made a plugin so that deactivating it would turn off the functionality. Of course you can make the plugin better. However, the overall idea,

1. You query the custom post type to find the exiting CPTS,
2. if it empty then DB returns all the cars in it and inserts them in the custom post type
3. Else is get the id of the cars is contrasted to find those already as CPTs and ignores them.

<?php
/**
 * Plugin Name: Stackoverflow Question
 * Plugin URI: https://omukiguy.com
 * Author: Laurence B.
 * Author URI: https://omukiguy.com
 * Description: Get the cars from the DB and show case on CPT.
 * Version: 0.1.0
 * License: GPL2
 * License URL: http://www.gnu.org/licenses/gpl-2.0.txt
 * text-domain: prefix-plugin-name
*/

function create_auto_cpt() {

    $labels = array(
        'name' => _x( 'Auto', 'Post Type General Name', 'plugin-auto-text' ),
        'singular_name' => _x( 'Auto', 'Post Type Singular Name', 'plugin-auto-text' ),
        'menu_name' => _x( 'Auto', 'Admin Menu text', 'plugin-auto-text' ),
        'name_admin_bar' => _x( 'Auto', 'Add New on Toolbar', 'plugin-auto-text' ),
        'archives' => __( 'Archivi Auto', 'plugin-auto-text' ),
        'attributes' => __( 'Attributi delle Auto', 'plugin-auto-text' ),
        'parent_item_colon' => __( 'Genitori Auto:', 'plugin-auto-text' ),
        'all_items' => __( 'Tutti le Auto', 'plugin-auto-text' ),
        'add_new_item' => __( 'Aggiungi nuova Auto', 'plugin-auto-text' ),
        'add_new' => __( 'Nuovo', 'plugin-auto-text' ),
        'new_item' => __( 'Auto redigere', 'plugin-auto-text' ),
        'edit_item' => __( 'Modifica Auto', 'plugin-auto-text' ),
        'update_item' => __( 'Aggiorna Auto', 'plugin-auto-text' ),
        'view_item' => __( 'Visualizza Auto', 'plugin-auto-text' ),
        'view_items' => __( 'Visualizza le Auto', 'plugin-auto-text' ),
        'search_items' => __( 'Cerca Auto', 'plugin-auto-text' ),
        'not_found' => __( 'Nessun Auto trovato.', 'plugin-auto-text' ),
        'not_found_in_trash' => __( 'Nessun Auto trovato nel cestino.', 'plugin-auto-text' ),
        'featured_image' => __( 'Immagine in evidenza', 'plugin-auto-text' ),
        'set_featured_image' => __( 'Imposta immagine in evidenza', 'plugin-auto-text' ),
        'remove_featured_image' => __( 'Rimuovi immagine in evidenza', 'plugin-auto-text' ),
        'use_featured_image' => __( 'Usa come immagine in evidenza', 'plugin-auto-text' ),
        'insert_into_item' => __( 'Inserisci nelle Auto', 'plugin-auto-text' ),
        'uploaded_to_this_item' => __( 'Caricato in questo Auto', 'plugin-auto-text' ),
        'items_list' => __( 'Elenco degli Auto', 'plugin-auto-text' ),
        'items_list_navigation' => __( 'Navigazione elenco Auto', 'plugin-auto-text' ),
        'filter_items_list' => __( 'Filtra elenco Auto', 'plugin-auto-text' ),
    );
    $args = array(
        'label' => __( 'Auto', 'plugin-auto-text' ),
        'description' => __( 'Auto', 'plugin-auto-text' ),
        'labels' => $labels,
        'menu_icon' => 'dashicons-admin-tools',
        'supports' => array(),
        'taxonomies' => array(),
        'public' => true,
        'show_ui' => true,
        'show_in_menu' => true,
        'menu_position' => 5,
        'show_in_admin_bar' => true,
        'show_in_nav_menus' => true,
        'can_export' => true,
        'has_archive' => true,
        'hierarchical' => false,
        'exclude_from_search' => false,
        'show_in_rest' => true,
        'publicly_queryable' => true,
        'capability_type' => 'post',
    );
    register_post_type( 'auto', $args );

}
add_action( 'init', 'create_auto_cpt', 0 );

add_action( 'admin_init', 'my_admin' );

function my_admin() {
    add_meta_box( 
        'Car_review_meta_box',
        'Informazioni Auto',
        'display_Car_review_meta_box',
        'auto', // Change the Car_reviews to custom post type = auto because  register_post_type( 'auto', $args );
        'normal', 
        'high'
    );
}

function display_Car_review_meta_box() 
{
    ?>
    <table>
        <tr>
            <td style="width: 50%">Marca</td>
            <td><input type="text" size="40" name="garage" value="<?php echo get_post_meta( get_the_ID() , 'model', true ); ?>" /></td>
        </tr>
        <tr>
            <td style="width: 50%">Modello</td>
            <td><input type="text" size="40" name="garage" value="<?php echo get_post_meta( get_the_ID() , 'brand', true ); ?>" /></td>       
        </tr>
        <tr>
            <td style="width: 50%">Color</td>
            <td><input type="text" size="40" name="garage" value="<?php echo get_post_meta( get_the_ID() , 'color', true ); ?>" /></td>       
        </tr>
        <tr>
            <td style="width: 50%">Mileage</td>
            <td><input type="text" size="40" name="garage" value="<?php echo get_post_meta( get_the_ID() , 'km', true ); ?>" /></td>       
        </tr>
    </table>
    <?php
}


add_action( 'wp', 'techiepress_insert_into_cpt');

function techiepress_verify_existing_cars_in_cpt() {

    $id_arrays_in_cpt = [];
    // Query all autos
    $args = array(
        'post_type'      => 'auto',
        'posts_per_page' => -1,
    );

    $loop = new WP_Query($args);
    while ( $loop->have_posts() ) {
        $loop->the_post();
        $id_arrays_in_cpt[] = get_post_meta( get_the_ID() , 'id', true );
    }
    return $id_arrays_in_cpt;
}

function techiepress_query_garage_table( $car_available_in_cpt_array ) {
    // Query Database
    global $wpdb;
    $table_name = 'garage'; // If the table has aprefix from WordPress then add $wpdb->prefix . 'garage';

    if ( NULL === $car_available_in_cpt_array || empty( $car_available_in_cpt_array ) || 0 === $car_available_in_cpt_array || '0' === $car_available_in_cpt_array) {
        $sql = "SELECT * FROM $table_name";
    } else {
        $ids = implode( ",", $car_available_in_cpt_array);
        $sql = "SELECT * FROM $table_name WHERE id NOT IN ( '$ids' )";
    }

    $results = $wpdb->get_results( $sql );
    return $results;

}

function techiepress_insert_into_cpt() {

    // if the queried results from dont match the existing autos
    $car_available_in_cpt_array = techiepress_verify_existing_cars_in_cpt();
    $database_results = techiepress_query_garage_table( $car_available_in_cpt_array );

    if ( NULL === $database_results || empty( $database_results ) || 0 === $database_results || '0' === $database_results) {
        return;
    }

    //Insert into CPT
    foreach( $database_results as $result ) {
         // Create post object
        $car_model = array(
            'post_title'  => wp_strip_all_tags( $result->Model . ' ' . $result->Brand ),
            'meta_input'  => array(
                'id'         => $result->id,
                'model'      => $result->Model,
                'brand'      => $result->Brand,
                'color'      => $result->Color,
                'km'         => $result->Km,
            ),
            'post_type'   => 'auto',
            'post_status' => 'publish',
        );
        // Insert the post into the database
        wp_insert_post( $car_model );
    }
}

Code tested and working.