utilizarea $wpdb in wordpress

How to control the database using the global object $wpdb in WordPress

The modification of the database in WordPress is done through the ‘wpdb’ class. This class is used to be able to interact with the tables in the database that the site uses, using the global object $wpdb. Through it we can have full access to the WordPress database.

In other words, the wpdb class can communicate with any existing table in the database assigned to the site. The initialization of the global object $wpdb is done through the file ‘db.php’ in the folder ‘wp-content’.

When we use operations to add, delete or edit content in the WordPress database, we will have to use the prepare () function as much as possible for the security of the site. Through this function we can prevent the attacks by which we want to inject some unwanted sql codes.

What functions does $wpdb use?


query () – executes an SQL query

//sintax:
$wpdb->query( "SELECT * FROM table_name" );

prepare() – prepares an SQL query preventing the injection of insecure code

//sintax:
$wpdb->query( $wpdb->prepare( "SELECT * FROM table_name" ));

get_var() – takes the value of a single variable from the database

//sintax:
$wpdb->get_var( "SELECT col_name FROM table_name WHERE ID = 1" );

get_row() – takes as an array () the values ​​of a row in a table

//sintax:
$wpdb->get_row( "SELECT * FROM table_name WHERE ID = 1" );

get_col() – takes as an array() the values ​​in the column of a table

//sintax:
$wpdb->get_col( "SELECT col_name FROM table_name" );

get_results() – retrieves values ​​from a table as an associative array ()

//sintax:
$wpdb->get_results( "SELECT * FROM table_name" );

insert() – inserts values ​​on a row of a table

//sintax:
$wpdb->insert( 'table_name', array( 'col_name' => 'value', 'cole_name2' => 'value'), array( '%s', '%s') );

update() – modify existing data in a table

//sintax:
$wpdb->update( 'table_name', array( 'col_name' => 'value'), array( 'ID' => 'value' ), array( '%s' ) );

delete() – delete rows from database

//sintax:
$wpdb->delete( table_name, array('id'=>value));

show_errors() and print_errors() 

//sintax:
$wpdb->show_errors(); 
$wpdb->print_error();

Example of using the global object $ wpdb through a custom plugin


If you know the MySql language then you will surely be familiar with the use of this class and you will only have to get used to certain default functions of the WordPress platform.

In this tutorial we will show you how to use the global object $ wpdb. Next we will create a plugin through which users will be able to enter a contest. Also, we, as administrators, will set all the data of the people who register for the contest to be displayed in a new page, created in the WordPress panel.

We will structure the tutorial in a few simple steps:

1.Create files

To start we will log in to the cpanel panel following the path ‘public_html / wp-content / plugins’ and we will create a folder containing the new module. In this tutorial we will call the folder ‘test_DB’. In the created folder we will add two files: index.php and style.css.

In order for WordPres to read the index.php file as a plugin, we will first need to add a header to it. If you want to find out more about a module’s page header, you can read this article.

<?php

// Plugin Name: Formular Concurs 
// Plugin URI: https://websitestools.ro
// Description: This is my first plugin, enjoy it!
// Author: Talmatchi Marius
// Version: 0.1
// Author URI: https://websitestools.ro

After adding this header we will be able to see the new module in the ‘plugins’ page of the panel, as follows:

We can select the activate option, but for now it will not do anything because I did not say anything in the file, other than I created the header so that this file can be interpreted by WordPress as a plugin. Okay, now we can start adding features to the module.

2.CSS file assignment

Before we start adding functionality for the Frontend or Backend part, we will first link the CSS file to the main page of the module, respectively index.php using the following code:

// ======================= insert css file ======================
add_action('admin_print_styles','register_scripts2');
function register_scripts2 () {
    wp_register_style( 'test_DB', plugins_url('style.css', __FILE__ . 'stye.css'),"","","all");
    wp_enqueue_style (  'test_DB' , plugins_url( 'style.css' ,  __FILE__ )  ) ; 

    // wp_enqueue_style('test_DB');
}

In the code above we used a hook (“admin_print_styles”) through which we add a CSS file (style.css) through the function we created (“register_scripts2”). Inside that function I used two other default functions namely “wp_register_style” and “wp_enqueue_style”. The parameters of these two functions are as follows:

  • $parameter 1 = Style sheet name. It should be unique.
  • $parameter 2 = Full style sheet URL.
  • $parameter 3 = A matrix of registered style sheets deals with this style sheet on which it depends, we left the space free.
  • $parameter 4 = Sir specifying the version number of the CSS file, we left free space. Default value: false.
  • $parameter 5 = The average for which this style sheet was defined. Supports media types such as “all”, “print” and “screen”.

We will test this code in the continuation of the article, immediately after we bring content to the index.php file.

3.Creating plugin pages in the WordPress dashboard menu

In order to enter data into the database we will use a form through which users can enter the contest. However, before entering this form we will add a few pages as well as subpages in the WordPress control panel, from where we will be able to retrieve information from the database. Next we will use another hook called ‘admin_menu’ through which we will create pages and subpages in the control panel menu. The function that will create this page will be named my_menu_pages (), but it can have any other name you want.

//============= Creating plugin pages in the WordPress dashboard menu ===============
add_action('admin_menu', 'my_menu_pages');
function my_menu_pages(){
    add_menu_page('concurs', 'Concurs', 'manage_options', 'concurs', 'pagina_principala_concurs' );
    add_submenu_page('concurs', 'Concurenti inscrisi', 'Concurenti inscrisi', 'manage_options','concurs2', 'pagina_afisare_concurenti' );
    add_submenu_page('concurs', 'Stergere concurenti', 'Stergere concurenti', 'manage_options', 'concurs3', 'pagina_stergere_concurenti' );
    add_submenu_page('concurs', 'Alege castigator', 'Alege castigator', 'manage_options', 'concurs4', 'pagina_alegere_castigator' );
}

In the code shown we use two default functions namely add_menu_page () and add_submenu_page.

The first function will create a page in the panel menu, and the second a subpage. In this example, a parent page was created, with 3 child pages.

Next, we will explain the parameters of each function:

a. add_menu_page()

  • $page_title = The text to be displayed in the page title tags when the menu is selected.
  • $menu_title = The text to use for the menu.
  • $capacity = The capacity required for this menu to be displayed to the user.
  • $menu_slug = The name of the slug that refers to this menu. It should be unique to this menu page and include only lowercase alphanumeric characters, hyphens, and underscores to be compatible with sanitize_key ().
  • $function = The function to be called to display the contents of this page.

b. add_submenu_page()

  • $parent_slug = The name of the slug for the parent menu.
  • $page_title = The text to be displayed in the page title tags when the menu is selected.
  • $menu_title = The text to use for the menu.
  • $capacity = The capacity required for this menu to be displayed to the user.
  • $menu_slug = The name of the slug that refers to this menu. It should be unique to this menu and include only lowercase alphanumeric characters, hyphens, and underscores to be compatible with sanitize_key ().

4. Testing style.css file

To test if the style.css file works well and is embedded in our plugin we will create an H4 subtitle on the main page (parent page created in the panel) to which we will assign a class entitled ‘page title’.

//========setting the parent page for dasboard =================
function pagina_principala_concurs () {
    echo "<h4 class='titlu-pagina'>Buna ziua, aceasta este prima pagina a modulului de inscriere concurenti</h4>";
}

In the style.css file we will try to add a color to the font, to see if it works.

.titlu-pagina {
    color: red;
}

Now the page looks like this:

As you can see, it works perfectly.

5.Creating a form to enter data into the database

The first thing we need to do is create a user registration form as well as a table in the database using the form_frontend () function. In this function we will enter the following code:

//======================== frond-end form ==========================
function formular_frontend () {
    
    //========================create tabel in DB==========================
    global $wpdb;
    $tabel_concurenti = $wpdb->prefix. "concurenti";
    $check_table_query = "SHOW TABLES LIKE '" . $tabel_concurenti . "'";
    $tabel = $wpdb->query($check_table_query);

    if (!$tabel) {  
        //creare tabel in baza de date
        $charset_collate = $wpdb->get_charset_collate();
        $creare_tabel = "CREATE TABLE $tabel_concurenti (
                  id mediumint(9) NOT NULL AUTO_INCREMENT,
                  nume varchar(255) DEFAULT '' NOT NULL,
                  email varchar(255) DEFAULT '' NOT NULL,
                  telefon varchar(255) DEFAULT '' NOT NULL,
                  PRIMARY KEY  (id)
              ) $charset_collate;";
         $wpdb->query($creare_tabel);
         }

Code explained:

  • Depending on the form, form_frontend () called the global object $wpdb.
  • We created a $ competitor_table variable whose value we set the name of the table we want to create
  • I requested to check beforehand if there is a table with such a name using the $check_table_query variable using the query () function
  • I requested that if the table does not exist, it be created automatically with the following columns:

1.id

2.nume

3.email

4.telefon

It is very important to set “PRIMARY KEY” to the “ID” column. Thus, it will be unique for each row in the table.

The basic syntax we use in this example is represented by the insert() function through which we enter new data in the specified table. For data security we use the default prepare () function with which we protect ourselves from the codes entered in the database with the intention of injecting other codes than the usual ones, such as “string”, “number” or “data”. To check if the written code is correct, we have introduced a conditional if() function through which we will call a JavaScript alert to let us know if the form works or not.

If we look in the phpmyadmin section of Cpanel, we will be able to see the table created.

6.Create a frontend form

Next, we will create the contact form:

 // =========   begining the form  =================
    
    echo '<h2>Formular de inscriere in concurs</h2>';
    echo '<form id="regForm" action="'. esc_url( $_SERVER['REQUEST_URI'] ).'" method="POST">';
    echo 'Introdu numele tau <br/>';
    echo '<input type="text" name="nume"><br/>';
    echo 'Introdu adresa ta de email <br/>';
    echo '<input type="text" name="email"><br/>';
    echo 'Introdu nr. tau de telefon <br/><br/>';
    echo '<input type="text" name="telefon"><br/><br/>';
    echo '<input type="submit" name="trimite" value="Trimite">';
    echo '</form>';

The last thing we will do in this function will be to set the data entered by the users to be stored in the created table.

//============= save data in table "concurenti" ===================
    if(isset($_POST['trimite'])){
      global $wpdb;
        $tabel_concurenti2 = $wpdb->prefix. "concurenti";  
        
        $nume = $_POST['nume'];
        $email = $_POST['email'];
        $telefon = $_POST['telefon'];
        
        $sql1 = $wpdb->insert($tabel_concurenti2, array("nume"=>$nume, "telefon"=>$telefon, "email"=>$email));
        $wpdb->query($wpdb->prepare($sql1));
        if ($sql1 == true) {
           echo "<script> alert('Ati fost inscris in concurs!')</script>";
        } else {
          echo "<script> alert('Ceva nu a mers bine, mai incercati odata')</script>";
        }
    }
}

7.Create a shortcode

Once we have created the form, we will create a page in which to enter the code. We will of course add the add_shortcode() function through which we will be able to display the form in the forntend part. To do this we will create a page directly from the WordPress menu in which we will enter the code. It will look like this:

//============Shortcode form ===============

add_shortcode('formular_inscriere_concurs', 'formular_frontend');

Create a new page to introduce:

After saving the changes, the result would be:

As I said at the beginning of the tutorial, before using or modifying the database we will need to invoke the global object $wpdb after which we can create a variable that has the value of the name of the table we want to work on. If the table does not exist we will be able to create it immediately after we have verified its existence through the get_charset_collate() function. We further implement the sql syntax via the $ table_create variable. This code will do nothing until no data is requested to be entered in the table. Therefore, in order for this table to be created automatically we will first have to implement a form through which to enter new data in the created table.

Now we can test the form by completing it several times from the Frontend to see if the data is inserted in the table created and inserted.

8. Retrieving data from database

Now that we’ve created a form that stores data in the table, we can try a code that displays the data in the ‘child’ pages of the new plugin.

Next we will create a new function called competitor_display_page() (the 5th parameter of the first child page created using the default function add_submenu_page(). As above we will first invoke the global object $wpdb and request the display an HTML table through which we will retrieve data from the database, using the get_results() function.

//========== add content from database in dasboard page ============
function pagina_afisare_concurenti () {
    global $wpdb;
echo '<table border="1">';
    echo '<tr>';
    echo '<th>ID</th>';
    echo '<th>NUME</th>';
    echo '<th>TELEFON</th>';
    echo '<th>EMAIL</th>';
    echo '</tr>';
    
    $tabel = $wpdb->prefix. "concurenti";
    $selectie_date_concurenti = $wpdb->get_results("SELECT * FROM $tabel order by id desc");
    foreach ($selectie_date_concurenti as $concurenti) {
        $id = $concurenti-> id;
        $nm = $concurenti->nume;
        $em = $concurenti->email;
        $tel = $concurenti->telefon;


        echo '<tr>';
        echo '<td>'.$id.'</td>';
        echo '<td>'.$nm.'</td>';
        echo '<td>'.$em.'</td>';
        echo '<td>'.$tel.'</td>';
        echo '</tr>';
}
echo '</table>';
}

The result of the code will be displayed on the ‘Registered competitors’ page:

9. How to delete rows in database

Next we will exemplify through a form how data can be deleted from the database using the delete() function.

//=============delete rows in DB=============
function pagina_stergere_concurenti() {
    global $wpdb;
    $tabel = $wpdb->prefix. "concurenti";
        echo '<br/>';
        echo '<form action="'. esc_url( $_SERVER['REQUEST_URI'] ).'" method="POST" class="sterge-date">';
        echo '<input type="text" name="alegeid">';
        echo '<input type="submit" name="buton_stergere" value="sterge">';
        echo '</form>';
                
         if(isset($_POST['buton_stergere'])){
            $alegeid = ($_POST['alegeid']);
            $wpdb->delete($tabel, array('id'=>$alegeid));
        }
}

The result will be displayed on the ‘Stergere concurenti’ page:

10. Select a winner

We will use the page created with the name ‘Choose winner’ to further exemplify how you can request a row from the table created in the database, chosen at random by the PHP rand() function.

function pagina_alegere_castigator () {
    global $wpdb;
    $tabel = $wpdb->prefix. "concurenti";
    $sql3 = $wpdb->get_results( "SELECT * FROM ".$tabel. " ORDER BY RAND() LIMIT 1" );
print_r ($sql3);
}

The result of this code will be displayed via the print_r() function which will return an associative array() with the column names and values ​​in the selected row.

I hope you enjoyed this tutorial, and I invite you to subscribe to stay up to date with the following articles.

Share: