How to create a MYSQL database with WebFaction and PHPMyAdmin

Requirements: WebFaction account, CyberDuck Account.

First go to WebFaction.

Click Databases and Databases

Click add new database

You can name your database, set the type to “MySQL” set your username as your webfaction username, you can give it a password too.

If you are concerned about security as you always should be ;) you should give it a new unique password, the problem is keychains will make that inconvenient because you might have already saved a password for this website. So there are tradeoffs.

Click save when you’re done.

You should see it listed.

You can then click “phpMyAdmin” to go to the phpmyadmin dashboard.

Sign in with the password you just created.

Click “New” to create a table.

Click save when you’re done

For your id, you must set its type to INT and omit the length/values.

Tick the auto-increment box

Just click Go from this popup

If you give it a size, you will get sketchy errors when you try to save your database.

Click save.

You should see your table on the left hand column.

Click the insert tab to add entries!

You shouldn’t give a value to the id row because that is automatically generated. After you have added a couple entries, you can check them out by going to the browse tab.

You can see the code at work by clicking on the SQL tab.

If you don’t know the syntax for basic SQL commands, you can click any of the following buttons and it will autofill boiler plate for the clicked command.

Now you can create a file that will interact with your database. Go to CyberDuck and click New File. Then

Then open your new file in your editor of choice: Right click on your files.

Type ! then enter to autofill all that great HTML boiler plate.

Then copy the php from here.

Let's break this down.

In order to create a connection variable, you create a new mysqli object which has a server-name, username, password, and database name.

The if condition checks if there is any problem with the connection object.

The next line is your sql statement which defines what you are asking of the database. In this case we are selecting the id value, firstname value, and lastname values from the myguests which is the name of the table we are getting the values from in this case.

On the next line we are calling the query method on the $conn object and passing in our SQL instructions.

For successful SELECT, SHOW, DESCRIBE, or EXPLAIN queries it will return a mysqli_result object. For other successful queries it will return TRUE. FALSE on failure

If a mysql_result object is returned, then

while($row = $result->fetch_assoc()) {
echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
}

Will run.

Otherwise

echo "0 results";

will run.

Make sure the values in the first echo statement match the row names and then when you run this .php file through a browser, you should get something like this depending on what values you put in.