Connecting PHP to MySQL

php
mysql

#1

If you want the quick rundown, just scroll to Getting Credentials and read on from there. If you’re a complete beginner, keep reading as we’ll walk through a few things in detail.

Notes

  • You will have to setup a database via a terminal in Cloud9. With no experience doing anything in a terminal before, this is still very doable.
  • You can not use mysql functions, you have to use mysqli, since mysql functions are deprecated and Cloud9 will not run them.

Setting up MySQL


In your project, open up a New Terminal (click the plus-sign tab above the text editor space, select “New Terminal”). In the terminal, type mysql-ctl start and hit Enter. MySQL will start up in the back, but you won’t get any response back in the terminal.

Next, type mysql-ctl cli and hit Enter. You should see some text that starts off as Welcome to the MySQL monitor.... Congrats, you’ve setup MySQL on your Cloud9 project.

Create a test database


By default, Cloud9 creates a database titled “c9” for you, but you can actually go ahead and create your official database if you like, but for this sake I’ll just make a database that holds a table that holds an ID and a username. So here’s the steps to setting up a database and a table. If you’ve used MySQL and databases before, then this should be cake, but I’ll explain it in detail for those who might not fully understand MySQL .

  1. Type SHOW DATABASES; and hit Enter. This will show a list of current databases within your project. You can enter this any time you want to see a list of your databases on the current project.
  2. Type in CREATE DATABASE sample_db; and hit Enter. You should get a Query OK, 1 Row affected. which means the query was successful. You can name the database whatever you like, but for this little walk-through, I named it sample_db.
  3. Type in USE sample_db; and hit Enter. This selects sample_db from the list of databases.
  4. Type in CREATE TABLE users (id INT(11), username VARCHAR(20));, and hit Enter. This creates a table named users with two columns: id and username. The number in parentheses represents the character limit the column will store in the database. In this case for example, username won’t hold a string longer than 20 characters in length.
  5. Type in INSERT INTO users (id, username) VALUES (1, "graham12");, and hit Enter. This will add the id of 1 and a username graham12 in the table. Since the id column is an INT, we do not put quotes around it.
  6. Type in SELECT * FROM users;, and hit Enter. This will show everything that is in the users table. The only entry in there should be what we inserted from the last step we just did.

Getting Credentials


Now we have some data in our table that we can test our mysqli connection with. But first, we have to get the credentials we will need to connect to the database in PHP. In Cloud 9, we will need 5 credentials to connect:

  1. Host name
  2. Username
  3. Password
  4. Database name
  5. Port #

Username, password, database name, and port #, are practically already known to you by now. I’ll explain:

  1. Host name - Since your MySQL server is running on on the workspace, it should be “127.0.0.1”
  2. Username - Your username that you use to log in to Cloud 9. Please note that MySQL has a limitation of maximum 16 characters for a username, so in case you have a username longer than 16 characters, please use the first 16 characters only.
  3. Password - There is NO password by default for your database in Cloud 9.
  4. Database name - This would be c9 by default, but or whatever you named your database;
  5. Port # - is 3306. In Cloud9, all of your projects are wired to 3306. This is a universal constant of Cloud9. It will not be anything else. Write this as you would an integer, not as a string. mysqli_connect() will interpret the port # as a long data type.

Connect to the Database


In your PHP file, insert your credentials accordingly:

<?php
    
    //Connect to the database
    $host = "127.0.0.1";
    $user = "your_username";                     //Your Cloud 9 username
    $pass = "";                                  //Remember, there is NO password by default!
    $db = "c9";                                  //Your database name you want to connect to
    $port = 3306;                                //The port #. It is always 3306
    
    $connection = mysqli_connect($host, $user, $pass, $db, $port)or die(mysql_error());



    //And now to perform a simple query to make sure it's working
    $query = "SELECT * FROM users";
    $result = mysqli_query($connection, $query);

    while ($row = mysqli_fetch_assoc($result)) {
        echo "The ID is: " . $row['id'] . " and the Username is: " . $row['username'];
    }

?>

If you get a result and no error then you have successfully setup a database and formed a connection to it with PHP in Cloud9. You should now be able to make all the queries you can normally make! :tada:

Note: I demonstrated the last part without using parameterized queries for the sake of being simple. You should always use parameterized queries when working with real web applications. You can get more info on that here: MySQLi Prepared Statements.


Tutorials - Table of Contents
Using Cloud9 on a shared hosting site?
#2

A post was split to a new topic: Previewing file just shows the code


#3

I copied your PHP code verbatim, and I got this weird error…

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in /home/ubuntu/workspace/php001.php on line 18 Call Stack: 0.0016 234840 1. {main}() /home/ubuntu/workspace/php001.php:0 0.0046 243688 2. mysqli_fetch_assoc() /home/ubuntu/workspace/php001.php:18

line 18 is this:
while ($row = mysqli_fetch_assoc($result)) {

my guess from the language of the error is that there is a type mismatch… could that be true?
EDIT____
Haha… I didnt have a ‘users’ table in ‘c9’… fixed!


#4

This is a good intro tutorial. I had to use “localhost” as the $host instead of “127.0.0.1”, not sure why it makes a difference.


#5

Sorry if this resurrects a zombie thread, but I would guess (being a PHP novice and without having tested it) that the problem lies with the fact that mysqli__query returns FALSE if the query fails (source). This doesn’t cause a problem until you pass it to mysqli__fetch__assoc which is expecting a parameter of type mysqli__result. Some sort of error handling would be needed i production, but for a simple “is it working” it works.