Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Working with MySQL and PHP

DZone's Guide to

Working with MySQL and PHP

· Java Zone ·
Free Resource

Download Microservices for Java Developers: A hands-on introduction to frameworks and containers. Brought to you in partnership with Red Hat.

We are getting requests for information on how our base server images are configured, specifically regarding database support. This post is all about showing how easy it is to leverage databases - in this case MySQL, although many other databases are also supported.

The short story

The base server image provided by Bowery has MySQL and MongoDB configured and running by default, and MySQL in particular has a user and database created for you. Here are the details of the base setup:

  • database: defaultdb
  • user: root
  • pass: changeme
  • host: localhost

As long as your code is using that configuration you should be able to connect and use the database that is already up and running on your instance.

The long story

We already discussed using phpMyAdmin which allows you to have a web-based database browser for your PHP projects. Here’s a dead-simple example PHP script that shows how you can work with your database. Feel free to also add phpMyAdmin to your project and you can check the database at each step.

CODE SAMPLE

<?php
/**
* This file demonstrates connecting and working with a MySQL
* database via mysqli interface, using standard configuration
* provided by Bowery (http://bowery.io).
* If you are wanting to run a totally different stack, you
* are encouraged to do so, and share your setup.
* REMEMBER: You are also free to drop a copy of phpMyAdmin
* right into your app folder, and then you can connect and
* use it from the server at your IP address.
**/
/**
* Configuration
**/
error_reporting(E_ERROR);
ini_set('display_errors', 1);
$config = new stdClass;
$config->host = 'localhost';
$config->db = 'defaultdb';
$config->user = 'root';
$config->pass = 'changeme';
// Welcome text
function welcome() {
$content = <<<EOT
<h2>Example MySQL Script</h2>
<p>This is just a simple little script to demonstrate how you can play around with MySQL and PHP with Bowery.io.</p>
<ul>
<li>Use <a href="?task=info">task=info</a> for phpinfo() output.</li>
<li>Use <a href="?task=checkConnection">task=checkConnection</a> to see if your database connection is correct.</li>
<li>Use <a href="?task=createTest">task=createTest</a> to create the test table.</li>
<li>Use <a href="?task=loadTest">task=loadTest</a> to load some data into the test table.</li>
<li>Use <a href="?task=listTest">task=listTest</a> to list the contents of the test table.</li>
<li>Use <a href="?task=dropTest">task=dropTest</a> to drop the contents of the test table.</li>
<li>Use <a href="/phpmyadmin">phpMyAdmin</a> just to be sure all is good and well in database land. REMEMBER: your database account is root / changeme and your database is defaultdb.</li>
</ul>
EOT;
echo $content;
}
// Use this to see if your MySQL connection is active
function checkConnection($config) {
$mysqli = new mysqli($config->host, $config->user, $config->pass, $config->db);
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
echo $mysqli->host_info . "\n";
}
// Use this to create the test table
function createTest($config){
$mysqli = new mysqli($config->host, $config->user, $config->pass, $config->db);
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
$sql = "CREATE TABLE test (c1 INT AUTO_INCREMENT NOT NULL PRIMARY KEY, c2 VARCHAR(64))";
if (!$mysqli->query( $sql )) {
echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
} else {
echo 'Table created successfully. Want to <a href="?task=loadTest">load some data</a>?';
}
}
// Use this to load some test data into the test table
function loadTest($config){
$mysqli = new mysqli($config->host, $config->user, $config->pass, $config->db);
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
$sql = "INSERT INTO test (c2) VALUES ('one'), ('two'), ('three'), ('four'), ('bet you were looking for five')";
if (!$mysqli->query( $sql )) {
echo "Data insert failed: (" . $mysqli->errno . ") " . $mysqli->error;
} else {
echo 'Data populated successfully. Want to <a href="?task=listTest">see some data</a>?';
}
}
// Use this to list the data in the test table
function listTest($config){
$mysqli = new mysqli($config->host, $config->user, $config->pass, $config->db);
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
$sql = "SELECT * FROM test ORDER BY c1, c2";
$mysqli->real_query($sql);
$res = $mysqli->use_result();
while ($row = $res->fetch_assoc()) {
echo " c1 = " . $row['c1'] . ", c2 = " . $row['c2'] . "<br>\n";
}
echo '<p>Table listed successfully. Want to <a href="?task=dropTest">drop the data</a>?</p>';
}
// Use this to drop the test table
function dropTest($config){
$mysqli = new mysqli($config->host, $config->user, $config->pass, $config->db);
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
$sql = "DROP TABLE test";
if (!$mysqli->query( $sql )) {
echo "Table drop failed: (" . $mysqli->errno . ") " . $mysqli->error;
} else {
echo 'Table dropped successfully. Want to <a href="/">start over</a>?';
}
}
// Hey, this is an example script, whadda you want, a CMS?
switch ($_GET['task']) {
case 'info':
phpinfo();
break;
case 'checkConnection':
checkConnection($config);
break;
case 'createTest':
createTest($config);
break;
case 'loadTest':
loadTest($config);
break;
case 'listTest':
listTest($config);
break;
case 'dropTest':
dropTest($config);
break;
default:
welcome();
} 


But wait, there’s more

If you are using a language other than PHP (such as Node.js or Ruby on Rails), or you need something other than MySQL, such as MongoDB right out of the box or Orchestrate.io, we have you covered. As our base image is Ubuntu 14.04 LTS, you can install just about whatever database and driver you could imagine.

We are working on a new release that will make customizing your environment very easy, and give you a ton of control. Stay tuned!

Having trouble getting this running? We are here to help, and we’re always looking for requests! Reach out to us at @boweryio.

Download Building Reactive Microservices in Java: Asynchronous and Event-Based Application Design. Brought to you in partnership with Red Hat

Topics:

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}