PHP MySQL: Introduction

This entry is part 1 of 17 in the series PHP & MySQL

Now that we have learnt some basic PHP and gone through CRUD functions in SQL, we will now move to learning how to retrieve and store data into database using PHP.

It’s been said that PHP works very well with MySQL.

That is mainly because of the many functions available in PHP for manupulating MySQL database.

A learning of PHP & MySQL is a learning of those PHP functions.

In PHP 5.3, a new mysqli class was introduced allowing persistent connection support.  This is to reduce overhead created by multiple connections.

We will use this new mysqli class in this tutorial.

PHP MySQL: Making a connection to MySQL OOP Style

This entry is part 2 of 17 in the series PHP & MySQL

First, start the WAMP server so that Apache and MySQL servers are started.

If you have removed the allartist database in MySQL, you can create it again using phpMyAdmin.

phpmyadmin_createdb

<?php
$mysqli = new mysqli("localhost", "root", "", "allartist");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL";
}
echo $mysqli->host_info . "<br>";

$mysqli = new mysqli("127.0.0.1", "root", "", "allartist", 3306);
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL";
}

echo $mysqli->host_info . "<br>";
?>

Since we have installed WAMP on the local PC, we can use localhost or 127.0.0.1.  Notice we have passed the connection to a variable $mysqli.

With this variable $mysqli, we can print out the host info using $mysqli->host_info.

When we run the above script, the output is as below.  It is basically how the script accesses the MySQL database allartist through TCP/IP protocol.

localhost via TCP/IP
127.0.0.1 via TCP/IP

phpmyadmin_createdb2

Object Oriented Style (OOP)

In OOP style, we have used new to create an object.  In OOP, a variable is treated as an object and so we can assign the object to $mysql.

In PHP, the -> is a referencing method. Other languages use a dot for this operator.

PHP couldn’t as PHP is using dot for string concat, hence the -> instead.

PHP MySQL: Making connection to DB non OOP procecural style

This entry is part 3 of 17 in the series PHP & MySQL

The earlier example was done using object oriented style of coding.

We can use the procedural method as shown below.

In Non OOP procedural method, we use mysql_connect.

<?php
$link = mysqli_connect("localhost", "root", "", "allartist");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
} else {
    printf("Connection to DB successful");
}
?>

It is recommended to use the newer OOP way of coding.

PHP MySQL: Create Database

This entry is part 4 of 17 in the series PHP & MySQL

If allartist database is not created yet, we can create it using PHP.

<?php

$mysqli = new mysqli("127.0.0.1", "root", "");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL";
}

$sql = "create database allartist";
if (mysqli_query($mysqli, $sql)) {
    echo "Database allartist created successfully";
} else {
    echo "Error creating database";
}

?>

Notice how we execute the script below by passing it into mysqli_query().

create database allartist

The database connection $mysqli also needs to be passed into mysqli_query().

Its format is:

mixed mysqli_query (db_connection , query string, [int $resultmode])

If the database allartist is not available, we should get a created successfully message.  If we run the code below to create the same database, we should get an error 1007 since the DB allartist has already been created.

<?php

$mysqli = new mysqli("127.0.0.1", "root", "");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL";
}

$sql = "create database allartist";
if (mysqli_query($mysqli, $sql)) {
    echo "Database allartist created successfully";
} else {
    echo "Error creating database with error no: " . $mysqli->errno;
}

?>

PHP MySQL: Create Table

This entry is part 5 of 17 in the series PHP & MySQL

In this sqlfiddle, we have created a table and inserted some data into the table.

CREATE TABLE artist
	(
 id int auto_increment primary key, 
 artiste varchar(30), 
 album varchar(50),
 year int(10)
 
 );

INSERT INTO artist
(artiste, album, year)
VALUES
('Rod Stewart', 'Maggie May', 1973),
('New Order', 'Substance', 1987),
('Nirvana', 'Smell Like Teen Spirit', 1992);
Run the SQL

Let’s create this similar table using PHP.  Using PHP, a table is created by mysqli_query.

There are 2 parameters in mysqli_query.  The database connection string $mysqli and the create table string $sql.

<?php

$mysqli = new mysqli("localhost", "root", "", "allartist");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL";
}

$sql = "CREATE TABLE artist (id int auto_increment primary key, artiste varchar(30), album varchar(50), year int(10))";

if (mysqli_query($mysqli, $sql)) {
    echo "Table artist created successfully";
} else {
    echo "Error creating table with error no: " . $mysqli->errno;
}

?>

If the WAMP server in your PC is started, you can try to run the above script.  If an error number is generated, you can google the relevant error message.

You can now login to phpMyAdmin and see the table artist similar to the screenshot below.

artist_table