Create MySQL on host server with PHP

Creating the Database

The first step of this project is to create the database tables you’ll need. For the purposes of this tutorial, you’ll need three database tables:

  • rw_app: A table to keep track of the apps we’re using the promo code system for. This way, you can use the same database tables for multiple apps.
    • id: Unique id for the app.
    • app_id: Unique string identifying the app (mainly for your own purposes).
  • rw_promo_code: A table to keep track of what promo codes are available.
    • id: Unique id for the code.
    • rw_app_id: The app id this code is for (from rw_app).
    • code: Alphanumeric code that the user types in to unlock something.
    • unlock_code: Alphanumeric string you’ll pass back to the app so it can know what to unlock.
    • uses_remaining: You’ll set things up so that codes can be used more than once – this way you can give out the same code to all of our friends on Twitter, for example. You’ll use this field to specify how many uses the code should have, and every time it is used this will be decremented by one. When iti hits 0, the code is no longer valid.
  • rw_promo_code_redeemed: A table to keep track some info each time a promo code that is redeemed. This will help us prevent one device from redeeming the same code multiple times (if it’s a multi-use code), by simply checking to see if the device has already used the code.
    • id: Unique id for the app.
    • rw_promo_code_id: The id of the promo code redeemed (from rw_promo_code).
    • device_id: The device identifier of the redeemer.
    • redeemed_time: A timestamp of when the code was redeemed.

Here are the MySQL statements you’ll need to create these tables:

DROP TABLE IF EXISTS rw_promo_code;
DROP TABLE IF EXISTS rw_app;
DROP TABLE IF EXISTS rw_promo_code_redeemed;

CREATE TABLE rw_promo_code (
    id mediumint NOT NULL AUTO_INCREMENT PRIMARY KEY,	
    rw_app_id tinyint NOT NULL, 
    code varchar(255) NOT NULL,
    unlock_code varchar(255) NOT NULL,
    uses_remaining smallint NOT NULL
);

CREATE TABLE rw_app (
    id mediumint NOT NULL AUTO_INCREMENT PRIMARY KEY,	
    app_id varchar(255) NOT NULL
);

CREATE TABLE rw_promo_code_redeemed (
    id mediumint NOT NULL AUTO_INCREMENT PRIMARY KEY,	
    rw_promo_code_id mediumint NOT NULL,
    device_id varchar(255) NOT NULL,
    redeemed_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

On your web server, you need to create a MySQL database and create the three tables as specified above. The way you do this varies depending on your web host, but just in case it’s useful I’ll tell you the steps I use on my web host, where I have full command-line access.

I save all of the above SQL statements to a file called create.sql, then I create and populate a new database with the following commands:

rwenderlich@kermit:~$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1286
Server version: 5.1.37-1ubuntu5.1-log (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database promos;
Query OK, 1 row affected (0.00 sec)

mysql> use promos;
Database changed
mysql> grant all privileges on promos.* to 'username'@'localhost' identified by 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

rwenderlich@kermit:~$ mysql -u username -p promos < create.sql
Enter password: 
rwenderlich@kermit:~$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1417
Server version: 5.1.37-1ubuntu5.1-log (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use promos;
Database changed
mysql> show tables ;
+------------------------+
| Tables_in_promos       |
+------------------------+
| rw_app                 | 
| rw_promo_code          | 
| rw_promo_code_redeemed | 
+------------------------+
3 rows in set (0.00 sec)

Now you should have three empty database tables in a new database. Next, go ahead and add a test app and a test code with the following statements:

INSERT INTO rw_app VALUES(1, 'com.razeware.test');
INSERT INTO rw_promo_code VALUES(1, 1, 'test', 'com.razeware.test.unlock.cake', 10000);

OK! Now that the database is connected and populated, on to writing the PHP web service!

Verifying PHP/MySQL functionality

Before you start implementing the PHP web service, first run a quick check to make sure PHP is working on your server OK. Create a new directory on your web server called promos, and create a new file inside called index.php with the following:

<?php

class RedeemAPI {
    // Main method to redeem a code
    function redeem() {
        echo "Hello, PHP!";
    }
}

// This is the first thing that gets called when this page is loaded
// Creates a new instance of the RedeemAPI class and calls the redeem method
$api = new RedeemAPI;
$api->redeem();

?>

This is a very basic PHP file that create an instance of a class (RedeemAPI) and calls a method on it that just outputs “Hello, PHP!”

You can test this by navigating to the URL on your web server with your browser. Even better, you can test this on the command line with a handy utility called curl similar to the following (but replace the URL with your own):

Ray-Wenderlichs-Mac-mini-2:~ rwenderlich$ curl http://www.wildfables.com/promos/
Hello, PHP!

Next, extend the class to make sure the service can connect to your database OK by replacing the RedeemAPI class with the following:

class RedeemAPI {
    private $db;

    // Constructor - open DB connection
    function __construct() {
        $this->db = new mysqli('localhost', 'username', 'password', 'promos');
        $this->db->autocommit(FALSE);
    }

    // Destructor - close DB connection
    function __destruct() {
        $this->db->close();
    }

    // Main method to redeem a code
    function redeem() {
        // Print all codes in database
        $stmt = $this->db->prepare('SELECT id, code, unlock_code, uses_remaining FROM rw_promo_code');
        $stmt->execute();
        $stmt->bind_result($id, $code, $unlock_code, $uses_remaining);
        while ($stmt->fetch()) {
            echo "$code has $uses_remaining uses remaining!";
        }
        $stmt->close();
    }
}

This adds a constructor that connects to your database given a username and password and a destructor that closes the database connection. The redeem loop is modified to run a MySQL statement to select all of the entries in rw_promo_code, and loop through to print a line about each entry.

Once again you can test this with curl to make sure it’s working:

Ray-Wenderlichs-Mac-mini-2:~ rwenderlich$ curl http://www.wildfables.com/promos/
test has 10000 uses remaining!

Question: 127.10.80.2:3306 connect error: could not connect to host server

Answer: 127.10.80.2, no :3306 this time. then ‘connect to MySQL!’

Relative Web:

Failed to connect to MySQL: Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)

by oshihirii on August 19, 2013 at 09:13 AM
5E
Expert

I have a basic MySQL/PHP app and testing a connection with php file:

<?php
// create connection
$con=mysqli_connect("localhost","username","password","db");

// check connection
if (mysqli_connect_errno($con))
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  };

$result = mysqli_query($con,"SELECT * FROM table");

json_encode($result);
mysqli_close($con);
?>

And getting an error:

Failed to connect to MySQL: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

The user is not the default root user however has all privileges on the database.

PS I don’t seem to get notifications on posts here but I haven’t disabled them.

5E
Corey Daley, Red Hat, Inc.
OpenShift (PaaS) Senior Product Marketing Manager @ Red Hat with a focus on Customer Enablement
Posted August 19, 2013 at 9:27 AM #

Try sshing into your gear with rch app ssh <appname> and running the command echo $OPENSHIFT_MYSQL_DB_URL. You can then take the url it gives you and split it into the username, password, and ip address to use in your connection string, instead of localhost.

Posted August 19, 2013 at 9:53 AM #

I was just coming back to update, I’ve been looking at a few of the other posts on this topic:

I’ve ssh’ed into app and typed env and saw:

OPENSHIFT_MYSQL_DB_SOCKET = /var/lib/openshift/<long number>/mysql//socket/mysql.sock

There is a double forward slash after mysql.

I followed your suggestion and got back a value that I think represents the root user.

So I used the IP with the new user i set up, eg:

127.x.xx.x:xxxx

But get an error on the front end:

Failed to connect to MySQL: Unknown MySQL server host '127.x.xx.x:xxxx' (3)

Then I removed the colon and last four digits and got the error:

Failed to connect to MySQL: Access denied for user 'newuser'@'127.x.xx.x' (using password: YES)

Posted August 19, 2013 at 10:05 AM #

Hmm, yes also tried:

env | grep OPENSHIFT_MYSQL_DB

and the host details are what I have in the php file:

OPENSHIFT_MYSQL_DB_HOST=127.x.xx.x

Posted August 19, 2013 at 10:14 AM #

I changed new user’s password to double check I had it right and that’s not the issue.

Perhaps when I added the new user i had to do "newuser"@"ip-address-here" rather than localhost.

Will check.

5E
Corey Daley, Red Hat, Inc.
OpenShift (PaaS) Senior Product Marketing Manager @ Red Hat with a focus on Customer Enablement
Posted August 19, 2013 at 10:17 AM #

When you created a user in mysql, did you also grant it permissions on the database that you want to access?

Also, I am not sure why you are not using the OpenShift environment variables in your code for the default mysql user & pass that are provided when the MySQL cartridge is installed on your gear?

Posted August 19, 2013 at 10:49 AM #

Yes permissions were granted on the database.

When the app was created and MySQL created, there was a notice that displayed the user credentials and referred to them as root details.

Thinking it wasn’t good practise to use root user and password, I created a new user.

I think specifying the IP when creating the user was required and solved the issue – although I am still not getting results from the database.

Will do further troubleshooting tomorrow.

5E
Corey Daley, Red Hat, Inc.
OpenShift (PaaS) Senior Product Marketing Manager @ Red Hat with a focus on Customer Enablement
Posted August 19, 2013 at 11:27 AM #

Are you able to login as that user from the command line after sshing to your gear and see the database and run queries on your tables?

Posted August 19, 2013 at 6:23 PM #

Yes, once SSH’ed in I can mysql -u newuser -p and then get in and run the same query I am trying to test in the PHP file, ie from the console:

SELECT * from table;

Below is the exact contents of the PHP file I am accessing directly in the browser:

<?php
// create connection
$con=mysqli_connect("127.x.xx.x","newuser","password","db");

// make a query
$result = mysqli_query($con,"SELECT * FROM table");

// print the result
print $result;
?>

It is returning a blank page and viewing source is empty.

Posted August 19, 2013 at 7:04 PM #

More tests:

<?php
error_reporting(E_ALL);
// create a connection
$con=mysqli_connect("127.x.x.x","newuser","password","db");
// make a query
$result = mysqli_query($con,"SELECT column FROM table WHERE othercolumn='value'");
// print the result
print json_encode($result);
?>

Outputs:

{"current_field":null,"field_count":null,"lengths":null,"num_rows":null,"type":null}

I get the same result if I use the credentials of the default user.

var_dump($result);

Gets:

object(mysqli_result)#2 (5) { ["current_field"]=> int(0) ["field_count"]=> int(1) ["lengths"]=> NULL ["num_rows"]=> int(1) ["type"]=> int(0) }

Posted August 19, 2013 at 7:17 PM #

Update:

I must not understand something very basic about outputting data from SQL queries in PHP, the following works and I have no idea why the above tests didn’t:

<?php
$con=mysqli_connect("127.x.xx.x","newuser","password","db");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$result = mysqli_query($con,"SELECT * FROM table");

while($row = mysqli_fetch_array($result))
  {
  echo $row['column_1'] . " " . $row['column_2'];
  echo "<br>";
  }

mysqli_close($con);
?>
5E
Corey Daley, Red Hat, Inc.
OpenShift (PaaS) Senior Product Marketing Manager @ Red Hat with a focus on Customer Enablement
Posted August 19, 2013 at 7:21 PM #

$result, in this case, is a traversable object ( a resource?), not the actual rows of data. So you need to use the mysqli_fetch_array to loop through getting the actual results from the resource.

See here: http://www.php.net/manual/en/class.mysqli-result.php

Posted August 19, 2013 at 9:44 PM #

Thanks, yes I just discovered that and am learning more now.

Basically, in case anyone finds it useful, here is how to use jQuery’s getJSON method (which is shorthand for the Ajax function wheredataType:"json" is specified), to get JSON encoded data from a PHP file that is querying a MySQL database.

This will allow you to create variables where the values are from data in a database and then manipulate those variables with jQuery.

jQuery

$(document).ready(function() {
$(".click_me").click(function(){
$.getJSON("path/to/php-file.php",function(results){  
alert(results[0].json-key-name); // where the 0 is the index of the json object returned in the array
});

PHP

<?php
$con=mysqli_connect("127.x.xx.x","username","password","db");

// check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// set up an empty array where the JSON objects will be added
$data = array();

//make a sql query
$query = mysqli_query($con, "SELECT * FROM table");

// for each column in each row returned, create an equivalent JSON key name and give it the cells's value
while ($row = mysqli_fetch_array($query)) {
$json = array();
$json['key-name-1'] = $row['db_column_a'];
$json['key-name-2'] = $row['db_column_b'];
$json['key-name-3'] = $row['db_column_c'];
$data[] = $json;
}

header("Content-type: application/json");
echo json_encode($data); // encode the data of the array into JSON format

mysqli_close($con);
?>

For troubleshooting, you can access the php file directly in a browser to see if your JSON array is coming back with the desired keys and values.

5E
Corey Daley, Red Hat, Inc.
OpenShift (PaaS) Senior Product Marketing Manager @ Red Hat with a focus on Customer Enablement
Posted August 20, 2013 at 8:39 AM #

That looks great, thanks for posting your solution.

You might also be able to do something like this below and not have to write as much code if you have a lot of columns in your table…

WARNING!!! un-tested code, but gives you an idea of what I mean…

<?php
$con=mysqli_connect("127.x.xx.x","username","password","db");

// check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// set up an empty array where the JSON objects will be added
$data = array();

//make a sql query
$query = mysqli_query($con, "SELECT * FROM table");

// for each column in each row returned, create an equivalent JSON key name and give it the cells's value
while ($row = mysqli_fetch_array($query)) {
$json = array();
foreach ($row as $key => $value) {
     $json[$key] = $value;
}
$data[] = $json;
}

header("Content-type: application/json");
echo json_encode($data); // encode the data of the array into JSON format

mysqli_close($con);
?>

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