WordPress.org

Support

Support » Plugins and Hacks » Hacks » [Resolved] MySQL database installation is not working

[Resolved] MySQL database installation is not working

  • For my first WordPress plugin I chose to make it my self difficult because I want to use a new table in the WordPress database installation.

    However if I install the plugin the database tables are not created for some sort of reason. I checked the query in phpMyAdmin and it runs fine in there so there aren’t any errors in the query, but what am I doing wrong?

    <?php
    // database creation
    function create_rozenactie(){
    
    $sql1 = "CREATE TABLE rozenactie (
      order_id int(11) NOT NULL AUTO_INCREMENT,
      ontvanger varchar(225) NOT NULL,
      klas_ontvanger varchar(50) NOT NULL,
      boodschap varchar(250) NOT NULL,
      aantal int(11) NOT NULL,
      bedrag int(11) NOT NULL,
      afzender varchar(225) NOT NULL,
      klas_afzender varchar(225) NOT NULL,
      email varchar(250) NOT NULL,
      betaalmoment varchar(250) NOT NULL,
      betaald varchar(200) NOT NULL,
      anoniem varchar(20) NOT NULL,
      PRIMARY KEY  (order_id)
    );";
    
    require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
    dbDelta($sql1) or die("Instalatie tabel Rozenactie mislukt");
    
    }
    
    function create_algemeen(){
    
    $sql2 = "CREATE TABLE algemeen(
      actief varchar(25) NOT NULL,
      prijs varchar(11) NOT NULL
    );";
    
    require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
    dbDelta($sql2) or die("Instalatie tabel Algemeen mislukt");
    }
    
    function insert_general_settings(){
    global $wpdb;
    
    $aanpassen = $wpdb->insert( algemeen, array('actief' => 'true', 'prijs' => '1') );
    $aanpassen;
    }
    ?>
Viewing 7 replies - 1 through 7 (of 7 total)
  • Moderator bcworkz

    @bcworkz

    You should use register_activation_hook() to register a function to run when the plugin is activated. Such functions are where create table script should reside.

    In addition to the advice above about ensuring your function is called within an activation hook, I would remove the dbDelta() call and handle it manually.

    I gave up on dbDelta several years ago because it is very very fussy about the format of the SQL and rarely reported or issued any errors in the event of a problem.

    In any case, try replacing the require/dbDelta with,

    $ret = $wpdb->query($sql);
    if ($ret === false) {
      error_log($wpdb->last_error);
    }

    Also don’t forget to use wpdb->prefix and ideally your own plugin’s prefix to avoid namespace clashes, e.g.

    $sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}{$this->prefix}mytable (...)";

    Ian.

    Thanks for your answers, however none of the fixes seem to work. I already had my registration_hooks placed in the main plugin file, now I placed them in the database file.
    But still no creation of the database tables.

    Is the use of the WordPress prefix required or just a suggestion to prevent errors? I only use the plugin for my own purposes and taught a prefix wasn’t a requirement.

    The modified code:

    <?php
    function create_rozenactie(){
    global $wpdb;
    
    $sql1 = "CREATE TABLE rozenactie (
      order_id int(11) NOT NULL AUTO_INCREMENT,
      ontvanger varchar(225) NOT NULL,
      klas_ontvanger varchar(50) NOT NULL,
      boodschap varchar(250) NOT NULL,
      aantal int(11) NOT NULL,
      bedrag int(11) NOT NULL,
      afzender varchar(225) NOT NULL,
      klas_afzender varchar(225) NOT NULL,
      email varchar(250) NOT NULL,
      betaalmoment varchar(250) NOT NULL,
      betaald varchar(200) NOT NULL,
      anoniem varchar(20) NOT NULL,
      PRIMARY KEY  (order_id)
    );";
    $run = $wpdb->query($sql1);
    
    if ($run === false) {
     error_log($wpdb->last_error);
    }
    
    }
    
    function create_algemeen(){
    
    global $wpdb;
    
    $sql2 = "CREATE TABLE algemeen(
      actief varchar(25) NOT NULL,
      prijs varchar(11) NOT NULL
    );";
    
    $run2 = $wpdb->query($sql2);
    
    if ($run2 === false) {
      error_log($wpdb->last_error);
    }
    
    }
    
    function insert_general_settings(){
    global $wpdb;
    
    $aanpassen = $wpdb->insert( algemeen, array('actief' => 'true', 'prijs' => '1') );
    $aanpassen;
    }
    
    // Installatie van de plugin
    $url = 'wp-content/plugins/rozenactie/rozenactie.php';
    
    register_activation_hook( $url, 'create_rozenactie') or die($wpdb->last_error);
    register_activation_hook( $url, 'create_algemeen') or die($wpdb->last_error);
    register_activation_hook( $url, 'insert_general_settings') or die($wpdb->last_error);
    ?>

    I haven’t checked but I suspect register_activation_hook should only be called once. If my guess is correct, you code never calls the create_ functions and so only the insert_ function is only called on activation.

    Use a debugger or echo/error_log statements to confirm.

    I tried several things.

    First thing was creating one function for the database creation and the placing of the registrationhook in my main pluginfile

    database.php

    <?php
    global $wpdb; // added after the error further down...
    
    function create_db(){
    global $wpdb;
    
    $sql1 = "CREATE TABLE rozenactie (
      order_id int(11) NOT NULL AUTO_INCREMENT,
      ontvanger varchar(225) NOT NULL,
      klas_ontvanger varchar(50) NOT NULL,
      boodschap varchar(250) NOT NULL,
      aantal int(11) NOT NULL,
      bedrag int(11) NOT NULL,
      afzender varchar(225) NOT NULL,
      klas_afzender varchar(225) NOT NULL,
      email varchar(250) NOT NULL,
      betaalmoment varchar(250) NOT NULL,
      betaald varchar(200) NOT NULL,
      anoniem varchar(20) NOT NULL,
      PRIMARY KEY  (order_id)
    );";
    $wpdb->query($sql1);
    
    $sql2 = "CREATE TABLE algemeen(
      actief varchar(25) NOT NULL,
      prijs varchar(11) NOT NULL
    );";
    
    $wpdb->query($sql2);
    
    $aanpassen = $wpdb->insert( 'algemeen', array('actief' => 'true', 'prijs' => '1') );
    $aanpassen;
    }
    ?>

    Activation Hook

    register_activation_hook( __FILE__, 'create_db');

    At first this gave an PHP Error:
    Notice: Undefined variable: wpdb in /home/leerlin/domains/leerlingenraadcce.nl/public_html/wp-content/plugins/rozenactie/rozenactie.php on line 37 Notice: Trying to get property of non-object in /home/leerlin/domains/leerlingenraadcce.nl/public_html/wp-content/plugins/rozenactie/rozenactie.php on line 37

    This came from the fact I forgot to call the $wpdb outside my function so I did that. But still there’s no sign of the creation of database tables.

    Does anybody have an idea of what I’m doing wrong?

    I was also such issues

    <?php
    /*
    Plugin Name: finish
    Plugin URI: http://wordpress/
    Version:1.0
    Author: Tong Hao Nhien
    Autor URI: tongvanphanktpm@gmail.com
    Description: This is my the first plugin
    */
    ?>
    <?php
    
    function testdb_install() {
    /* Creates new database field */
    global $wpdb;
    $table_name = $wpdb->prefix . "slideshow";
    
        $sql='CREATE TABLE wp_slideshow (
            id VARCHAR  NOT NULL AUTO_INCREMENT,
            url_image VARCHAR  NOT NULL,
    
            )ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;';
    
    		 $wpdb->query($sql);
    
    		//require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    
            //dbDelta($sql);
    		 echo('installed');       
    
        add_option('version_db_testdb','2.1');
    
    }
    function testdb_remove(){
    	global $wpdb;
    	$table_name = $wpdb->prefix."slideshow";
    	delete_option('version_db_testdb');
    	$wpdb->query("DROP TABLE IF EXISTS '.$table_name.'");
    }
    register_activation_hook(__FILE__, 'testdb_install' );
    register_activation_hook(__FILE__,'testdb_remove');
    if( is_admin()){
    	add_action('admin_menu','add_menu_admin');
    	function add_menu_admin(){
    		add_menu_page('Demo test db','Demo test db','administrator','testdb','testdb_html');
    		add_submenu_page('testdb',__('Setting'),__('Setting'),'administrator','testdb','testdb_html');
    		add_submenu_page('testdb',__('View'),__('View'),'administrator','testdb','testdb_html');
    	}
    	function testdb_html(){
    		echo 'Install database in wordpress';
    	}
    }
    
     //display fontend
    function testdb_fontend(){
    	echo "Hello wordpress";
    }

Viewing 7 replies - 1 through 7 (of 7 total)
  • The topic ‘[Resolved] MySQL database installation is not working’ is closed to new replies.
Skip to toolbar