Code to drop/create/access mysql table in wordpress

<?php function test_db() {
echo '<h2>Testing MySQL Inserts</h2>';
global $wpdb;
echo "<p>Dropping mac_test1</p>";
$sql = "DROP TABLE IF EXISTS mac_test1";
$result = $wpdb->query($sql);
echo "<p>Creating mac_test1</p>";
$sql = "CREATE TABLE mac_test1 (
mac_ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
mac_text tinytext NOT NULL,
PRIMARY KEY (mac_ID)
) ENGINE=MyISAM AUTO_INCREMENT=289 DEFAULT CHARSET=utf8 ;
";
$result = $wpdb->get_results($sql);
echo "<p>Result:";var_dump($result);echo '</p>';
echo "<p>Dropping mac_test2</p>";
$sql = "DROP TABLE IF EXISTS mac_test2";
$result = $wpdb->query($sql);
echo "<p>Creating mac_test2</p>";
$sql = "CREATE TABLE mac_test2 (
mac_ID2 bigint(20),
mac_text2 tinytext NOT NULL,
PRIMARY KEY (mac_ID2)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
";
$result = $wpdb->get_results($sql);
echo "<p>Inserting mac_test1</p>";
$sql = "INSERT INTO mac_test1 VALUES (null,'first insert')";
$result = $wpdb->get_results($sql);
echo "<p>Result:";var_dump($result);echo '</p>';
$id = mysql_insert_id();
echo "<p>Inserted id:";var_dump($id);echo '</p>';
$sql = "INSERT INTO mac_test2 VALUES ($id,'123')";
$result = $wpdb->get_results($sql);
echo "<p>Inserting mac_test1</p>";
$sql = "INSERT INTO mac_test1 VALUES (null,'2 insert')";
$result = $wpdb->get_results($sql);
$id = mysql_insert_id();
echo "<p>Inserted id:";var_dump($id);echo '</p>';
$sql = "INSERT INTO mac_test2 VALUES ($id,'456')";
$result = $wpdb->get_results($sql);
echo '<p>Retrieve from mac_test1</p>';
$sql = "SELECT * FROM mac_test1";
$rows1 = $wpdb->get_results($sql);
foreach ($rows1 as $row) {
echo "ID: $row->mac_ID  TEXT: $row->mac_text<br/>";
}
}
test_db();
?>
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s