The next step is to actually create the database table. Rather than executing an SQL query directly, we'll use the dbDelta function in wp-admin/includes/upgrade.php (we'll have to load this file, as it is not loaded by default). The dbDelta function examines the current table structure, compares it to the desired table structure, and either adds or modifies the table as necessary, so it can be very handy for updates (see wp-admin/upgrade-schema.php for more examples of how to use dbDelta). Note that the dbDelta function is rather picky, however. For instance:
- You must put each field on its own line in your SQL statement.
- You must have two spaces between the words PRIMARY KEY and the definition of your primary key.
- You must use the key word KEY rather than its synonym INDEX and you must include at least one KEY.
- KEY must be followed by a SINGLE SPACE then the key name then a space then open parenthesis with the field name then a closed parenthesis.
- You must not use any apostrophes or backticks around field names.
- Field types must be all lowercase.
- SQL keywords, like CREATE TABLE and UPDATE, must be uppercase.
- You must specify the length of all fields that accept a length parameter. int(11), for example.
With those caveats, here are the next lines in our function, which will actually create or update the table. You'll need to substitute your own table structure in the $sql variable:
global $wpdb;
$charset_collate = $wpdb->get_charset_collate();
$sql = "CREATE TABLE $table_name (
id mediumint(9) NOT NULL AUTO_INCREMENT,
time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
name tinytext NOT NULL,
text text NOT NULL,
url varchar(55) DEFAULT '' NOT NULL,
PRIMARY KEY (id)
) $charset_collate;";
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( $sql );Note: Above we set the default character set and collation for the table. If we don't do this, some characters could end up being converted to just ?'s when saved in our table. In this example we use $wpdb->get_charset_collate() to get the character set and collation. That function was introduced in WordPress 3.5, and if you need to support versions before that you will need create the charset/collate string yourself (you could copy the source of that function).
Finally, you may want to add some data to the table you just created. Here is an example of how to do that:
$welcome_name = 'Mr. WordPress';
$welcome_text = 'Congratulations, you just completed the installation!';
$table_name = $wpdb->prefix . 'liveshoutbox';
$wpdb->insert(
$table_name,
array(
'time' => current_time( 'mysql' ),
'name' => $welcome_name,
'text' => $welcome_text,
)
);NOTE: For more on using WPDB, see wpdb class. In this case, we're using $wpdb->insert, so our data will automatically be escaped. If you need to use another method like $wpdb->query instead, it's a good idea to run the variables through the $wpdb->prepare function before passing the query to the database to prevent security problems, even though we defined $welcome_name and $welcome_text in this function and know that there are no SQL special characters in them.
global $wpdb;
$table_name = $wpdb->prefix . "wplusersprofiles";
$user = $wpdb->get_results( "SELECT * FROM $table_name" );<?php foreach ($user as $row){ ?>
<tr>
<th><label for="gender"><?php _e("Gender"); ?></label></th>
<td>
<input type="text" name="gender" id="gender" value="<?php echo $row->gender ?>" class="regular-text" /><br />
</td>
</tr>
<?php } ?><?php
global $jal_db_version;
$jal_db_version = '1.0';
function jal_install() {
global $wpdb;
global $jal_db_version;
$table_name = $wpdb->prefix . 'liveshoutbox';
$charset_collate = $wpdb->get_charset_collate();
$sql = "CREATE TABLE $table_name (
id mediumint(9) NOT NULL AUTO_INCREMENT,
time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
name tinytext NOT NULL,
text text NOT NULL,
url varchar(55) DEFAULT '' NOT NULL,
PRIMARY KEY (id)
) $charset_collate;";
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( $sql );
add_option( 'jal_db_version', $jal_db_version );
}
function jal_install_data() {
global $wpdb;
$welcome_name = 'Mr. WordPress';
$welcome_text = 'Congratulations, you just completed the installation!';
$table_name = $wpdb->prefix . 'liveshoutbox';
$wpdb->insert(
$table_name,
array(
'time' => current_time( 'mysql' ),
'name' => $welcome_name,
'text' => $welcome_text,
)
);
}
function jal_get_data() {
global $wpdb;
$table_name = $wpdb->prefix . "liveshoutbox";
$data = $wpdb->get_results( "SELECT * FROM $table_name" );
return $data;
}