MySQL Connect and Create DB

Opening a Connection

To create a database, you have to first connect the MySQL server to the php script. This is done using mysqli_connect() function of php.

Syntax

mysqli_connect("host","username","password","databaseName");

This function contains mainly these parameters-

  • host- This can be a host name or the IP address. For the server you installed with wamp server, host name will be localhost
  • username- This is the username for MySQL server. For our case, it will be root
  • password- This is the password for MySQL server. For our case, there is no password set
  • databaseName- This is the parameter which will be effective if you want to work with a database that has been created before. You can directly connect to the database which name will be given here. If you want to create a new database, omit this parameter

MySQLi Installation

  • For Linux and Windows: The MySQLi extension is automatically installed in most cases, when php5 mysql package is installed
  • For installation details, go to: http://php.net/manual/en/mysqli.installation.php

  • So to connect to the MySQL server, use following code in php file-



    <!DOCTYPE html>

    <html>

    <body>

    <?php

    $connect=mysqli_connect("localhost","root",""); //connecting

    if (mysqli_connect_errno()) //checking for errors

    {

    echo "Failed to connect to MySQL: " . mysqli_connect_error();

    }

    else

    {

    echo "Connected Successfully";

    }

    ?>

    </body>

    </html>

    Output

    Connected Succesfully
  • In above example, we connected to the MySQL server and saved the result in the variable $connect. After that, we checked if there was any error while connecting to MySQL. The output shows that MySQL has been connected successfully

Creating Database


  • A database can be created only when the MySQL server has been connected
  • CREATE DATABSE is the statement which is used to create a new database
  • Syntax

    CREATE DATABASE databaseName
  • This statement must be added to a php function to bring in effect. The function is mysqli_query(), which is used to execute all the queries in MySQL
  • Queries are already discussed in the chapter Introduction to MySQL. For now, remember to add the above statement to this function.

  • This function takes mainly two parameters-
    • link= This is the link to the MySQL server. The result of function mysqli_connect() is used here. In the example given above, this link is $connect
    • query= This is the query that is to be executed. Query is always in the form of string therefore CREATE DATABASE statement must be enclosed within double quotes

Syntax

mysqli_query(mysqli_connect(...),"CREATE DATABASE databaseName");

Example

<!DOCTYPE html>

<html>

<body>

<?php

$connect=mysqli_connect("localhost","root","");

if (mysqli_connect_errno())

{

echo "Failed to connect to MySQL: " . mysqli_connect_error();

}

$query="CREATE DATABASE firstdb";

if(mysqli_query($connect,$query))

{

echo "Database Created";

}

else

{

echo "Error";

}?>

</body>

</html>

Above program creates a database named firstdb as well as checks if the database was successfully created or not

Closing a Connection


  • The connection that you opened using mysqli_connect() function, will be ended automatically when the php script is ended. But sometimes you may need to close it in between the php script
  • mysqli_close() function is used to close a connection. It takes a single parameter which is the link of the connection
mysqli_close($connect);