Database connection in PHP with MySQLi examples

Use the following PHP block to connect to your database with mysqli

function db_connect($host,$user,$pass,$db) {

   $mysqli = new mysqli($host, $user, $pass, $db);

   $mysqli->set_charset("utf8");

   if($mysqli->connect_error) 
     die('Connect Error (' . mysqli_connect_errno() . ') '. mysqli_connect_error());

   return $mysqli;
}

$mysqli = db_connect('localhost','username','password','database');

Replace localhost, username, password and database with your own details.

Select rows in a table

Here’s an example of how to select the id, firstname and lastname from a table called members.

$sql = "SELECT id, firstname, lastname FROM members";
$result = $mysqli->query($sql);

if ($result->num_rows > 0) {
    //output data of each row
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"];
    }
} else {
    echo "0 results";
}
$mysqli->close();

Insert rows into a table

In this example we are inserting a record into a table called members.

$sql = "INSERT INTO members (firstname, lastname, email)
VALUES ('Joe', 'Smith', 'joe@example.org')";

if ($mysqli->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "
" . $mysqli->error;
}

$mysqli->close();

Update rows in a table

In this example we are changing the name Paul in every record to John in a table called members.

$sql = "UPDATE members SET name = 'John' WHERE name = 'Paul'";

if ($mysqli->query($sql) === TRUE) {
    echo "Record updated successfully";
} else {
    echo "Error: " . $sql . "
" . $mysqli->error;
}

$mysqli->close();

Delete rows from a table

In this example we are deleting every record with the firstname of John in the members table.

$sql = "DELETE FROM members WHERE firstname = 'John'";

if ($mysqli->query($sql) === TRUE) {
    echo "Record deleted successfully";
} else {
    echo "Error: " . $sql . "
" . $mysqli->error;
}

$mysqli->close();