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.

SQl: Create table with variables

This entry is part 4 of 20 in the series SQL Tutorial

Let us create another more interesting table artiste to illustrate some common query methods in the next few posts.

We use create table command as in the sql code below.

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

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

In the table artiste, we define a table with 4 columns – id, artiste, album and year.

Variables in the table

VariableDescription
idpositive integer, auto increment
artistevariable-length string of up to 30 characters
albumvariable-length string of up to 50 characters
yearinteger of up to 10 characters

To display the whole table, we can use a very common query statement:

select * from artiste

The * will select all columns, thus displaying the whole table.

Auto_Increment

We set the column id to auto_increment, with default starting value of 1.

When you insert a NULL (recommended) (or 0, or a missing value), into an auto_increment column, the maximum value of that column plus 1 would be inserted. You can also insert a valid value to an auto_increment column, bypassing the auto-increment.