MySQL

MySQL is very popular database used for storing data in web sites.

Why Use Database ?

Database make it easy to store and retrieve data.

SQL (Standard Query Language) is used to retrieve data from database. SQL is a common language used by many database programs.

How to connect to MySQL Server ?

To connect to a MySQL server, you need to know IP Address of the MySQL server, Database name, user name and password.

Tables

Before we can store data in a database, we need to create table.

If Database is a folder, tables are like files. Only tables can store real data. In database, we can't save random data, we create multiple tables for type of data.

For example, to store students information, we create students table. A table have muliple fields, in case of students table

* name
* standard
* address
* score
* age

etc..

Out Test Database

You can use following database for next few lessons.

Code:
MySQL Server IP: 127.0.0.1
Database: fwhphp_db
User: fwhphp_user
Password: k5BJRaX6SFbs
As you can see, we have a very complicated password, so it is secure to post it on a public form :)

It is very important we don't show our database login details to public, but in this case, it is not a big issue as we don't have anything important in it and we can delete it any time.

Let's Connect to MySQL Server

We use MySQLi (MySQL Improved) function to connect to MySQL server. This is the new recommended method.

day_13_mysql_connect.php

PHP Code:
<?php

$db_server = '127.0.0.1';
$db_user = '';
$db_password = '';
$db_name = '';

$mysqli = new mysqli($db_server, $db_user, $db_password, $db_name);

if ($mysqli->connect_errno) {
    echo 'Connect failed: ' . $mysqli->connect_error;
    exit();
} else {
    echo '<h1>Success! Welcome to MySQL database server.</h1>';
}
In above code, you need to set value for following variables.

PHP Code:
$db_user = '';
$db_password = '';
$db_name = ''; 
Try with wrong user name, password and db name and see what error message you get. You should be able to read such error message and understand why that error occurred. So simulate error conditions by using wrong values for the variables.

Reading Data From MySQL

To start, i created a table with some sample data.

Name of the table is "chat", it have 3 fields.

* id (numeric field)
* name (text field)
* message (text field)

day_13_mysql_read.php

PHP Code:
<?php

$db_server = '127.0.0.1';
$db_user = 'fwhphp_user';
$db_password = 'k5BJRaX6SFbs';
$db_name = 'fwhphp_db';

$mysqli = new mysqli($db_server, $db_user, $db_password, $db_name);

if ($mysqli->connect_errno) {
    echo 'Connect failed: ' . $mysqli->connect_error;
    exit();
}

$sql = 'SELECT * FROM `chat`';
$result = $mysqli->query($sql);

echo '<p>MySQL have returned ' . $result->num_rows . ' records.</p>';

echo '<br>';
echo '<hr>';

while ($chat = $result->fetch_object()) {
    echo '<b>' .  $chat->name . '</b> says : ' . $chat->message;
    echo '<br>';
    echo '<hr>';
}
Tomorrow we will see how to store data into a table.

EDIT: Due to today PHP downgrade on server48, my previous example code did not work, so i have to use while() loop. While loop allow you to read all records from MySQL, it is somewhat like foreach.