Connection to MySQL with MySQLi

Open command prompt and go to mysql/bin derictory

mariadb1

Type "show databases;" command to view which databases you have.

mariadb2

In my connection code example, I will use connection to usernames table located in myearcth database.

Type "use myearth;" to switch to myearth database.

To see, which tables exist in the myearth database, type command "SHOW TABLES FROM MYEARTH;"

mariadb3

I have the usernames table. You can create it by "copy and paste" the following create table statement.



 CREATE TABLE `usernames` (
    `userid` int(11) NOT NULL AUTO_INCREMENT,
    `lastname` varchar(20) DEFAULT NULL,
    `firstname` varchar(20) DEFAULT NULL,
    `username` varchar(20) DEFAULT NULL,
    `password` varchar(50) DEFAULT NULL,
    `email` varchar(30) DEFAULT NULL,
    `role` varchar(10) DEFAULT NULL,
    `city` varchar(20) DEFAULT NULL,
    `country` varchar(20) DEFAULT NULL,
    `bio` text,
    `website` varchar(100) DEFAULT NULL,
    `active` tinyint(4) DEFAULT NULL,
    PRIMARY KEY (`userid`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

After the table is created, you may use a command "show columns from usernames;" to see all its columns

Create the connect.php file with the following code:

<?php

$link=mysqli_connect("localhost","root","sergey_s", "myearth");

if (mysqli_connect_errno())
{
$msg="Failed to connect to MySQL: " . mysqli_connect_error();
}

Later, you may include it in any php file that access data in the database.

To insert records in the usernames table, use the following code.

insert_record.php

<?php

include('connect.php');

$lastname="Silver";
$firstname="John";
$username="johnsl";
$password="5f4dcc3b5aa765d61d8327deb882cf99"; // md5 encrypted word "password"
$email="johnsl@gmail.com";
$role="user";
$city="Treasure Island";
$country="None";
$bio="I am a pirat!";

$ipsql="insert into usernames(
userid,
lastname,
firstname,
username,
password,
email,
role,
city,
country,
bio,
active)
values
(0,'".$lastname."','".$firstname."','".$username."','".$password."','".$email."','".$role."','".$city."','".$country."','
".$bio."', 1)";

    if(!mysqli_query($link,$ipsql))
	{
	$msg=mysqli_error($link);
	}
	else
	{
	$msg="record inserted successfully!";
        }
    echo $msg;

?>

Run insert_record.php file and check in the command prompt that the record is inserted.

select your database and type: "select * from usernames". The record is displayed. If it is not inserted, then, probably, you made a typo.

Create a display_record.php file to dislay the inserted record.


<?php

include('connect.php');

$sql="select lastname,firstname,username,email,role from usernames where email='".$email."'";

    if(!$uresult=mysqli_query($link,$sql))
	{
	 $msg=mysqli_error($link);
	 }
	 else
	 {

	 $urow = mysqli_fetch_array($uresult, MYSQLI_NUM);

	 $lastname=$urow[0];
	 $firstname=$urow[1];
	 $username=$urow[2];
	 $email=$urow[3];
	 $role=$urow[4];

     echo $lastname." | " . $firstname . " | " . $username . " | " . $role . "<br>";

     }
?>

For updating the record, create the "update_record.php" file.

update_record.php


 <?php

include('connect.php');

$role="admin";
$username="sniderj";

$sql="UPDATE usernames SET role='".$role."' where username='".$username."'";

//echo "sql=".$sql."<br>";

    if(!mysqli_query($link, $sql))
    {
   	$msg=mysqli_error($link);
    	}
    	else
           {
   	$msg="Record is updated successfully!";
    }
    echo $msg;
?>

For delete the record, create the "delete_record.php" file.

delete_record.php


 <?php

include('connect.php');

$userid=2;

$sql="delete from usernames where userid=".$userid;
//echo "sql=".$sql."<br>";

        if(!mysqli_query($link, $sql))
        {
        $msg=mysqli_error($link);
	}
	else
       {
	$msg="Record is deleted successfully!";
	}
	echo $msg;
?≫

You can create a function to insert, update or delete a record. This function will take a query as an argument that we pass by value and message argument that is passed by reference. The function return 1 in case of success and 0 in case of failure.


function execute($sql, &$msg)
{

        if(!mysqli_query($link, $sql))
     {
	 $msg=mysqli_error($link);
     return 0;
	 }
	 else
          {
    $msg="Record is deleted successfully!";
    return 1;
	}

}

You may display not only data, but also the table column names and other metadata.


<?php

include('connect.php');

$email="myemail@gmail.com";

$sql="select lastname,firstname,username,email,role from usernames where email='".$email."'";

if(!$result=mysqli_query($link,$sql))
{
$msg=mysqli_error($link);
}
else
{

    while ($fieldinfo=mysqli_fetch_field($result))
   {
   print("Database: ". $fieldinfo->db . " | " );
   print("Table: ". $fieldinfo->table . " | " );
   print("Name: ". $fieldinfo->name . " | " );
   print("charset #: ". $fieldinfo->charsetnr. " | ");
   print("Length: ". $fieldinfo->length. " | ");
   print("Max_Length: ". $fieldinfo->max_length. " | ");
   print("Type: ". $fieldinfo->type . " | ");
   echo "<br>";
   }

}

mysqli_free_result($result);
?>

OUTPUT: