How to Create a Simple SQL Database

SQL is a server-side scripting language used to query relational databases such as MySQL database. Relational databases as the name referrers are ways in which data is stored in formatted clusters such as databases and their corresponding SQL server tables in ways and means which creates relationships between tables of similar or even different databases. The language is used to manipulate data stored on the server by creating and deleting tables, creating and dropping databases, inserting and updating data and, creating databases. All of this can be achieved programmatically through various server-side languages such as Python and PHP but in this example, we shall be using PHP to demonstrate how to build a SQL server database.

To begin, follow these steps:

Install a server to run PHP

• Download XAMP (there are other alternatives that function almost similarly) it is free to use.
• Launch the installer and follow the installation guide.
• Next would be the XAMP modules, for this example, we shall only need Apache, MySQL, and phpMyAdmin but the default is also fine.
• Here the installer will ask for an appropriate installation directory, this is very important to note as the directory will contain the htdocs where server-side scripts will be hosted. It is also important to select a drive that as ample free space as the location tends to fill up very fast and migrating files would be a rather tedious task.
• Finish and run the control panel from the windows launcher to proceed to step two.

Install notepad++

They are other text editors like Dreamweaver but let us use notepad++ for now. Notepad can be downloaded and used for free.

• Once XAMP is installed make sure that both Apache and MySQL modules have been successful. This can be confirmed by typing http://localhost/phpmyadmin into the browser it should load something similar to this.
• With the case of PHP, all files created must be stored and run inside the htdocs folder of the XAMP installation directory, so navigate to XAMP/htdocs and create a new folder where we shall keep all our test code, let us call it a query.

Open notepad++ and write a db.php file

This file will be responsible for creating a connection to our MySQL database. Write the following and save the file as db.php inside our query folder which we created.

// Php opener tag.

<?php

//During the installation of XAMP it prompted for a username and password to create a user with privileges over the server if left unchanged the values are root and empty space ” “respectively. The server name should be left as a localhost.

$servername = "localhost";
$username = "username";
$password = "password";

// Code to create a connection
$conn = mysqli_connect($servername, $username, $password);
// Code to confirm connection
if (!$conn) {

// Check the connection and display any error that might be preventing the PHP code from creating a successful connection.
die("Connection failed: " . mysqli_connect_error());
}
// Php closing tag
?>

After creating the db.php we can create another file to execute and made a database using SQL. The following code does just that.

<?php

// Php code to include the created db.php and hence inherit connection created.

include("db.php");

// SQL code to create a database
$sql = "CREATE DATABASE myDB";

// Code to execute SQL query
if (mysqli_query($conn, $sql)) {
 echo "Database created successfully";
} else {
echo "Error creating database: " . mysqli_error($conn);
}
/
mysqli_close($conn);

?>

The above code can be used to create a SQL database called myDB just save the file as index.php, inside the query folder we created in the XAMP htdocs folder, and run it by going to the browser and going to localhost (localhost represents the htdocs XAMP htdocs folder and hence can be used to access and navigate to files and other folders inside it) then query (by default the server will always execute a file with the name index inside the folder it navigates inside and as such it is not a prerequisite to prefix the folder name with the index.php file require.) The complete URL should look like http://localhost/query or http://localhost/query/index.php both would give a similar result.

The above code can be modified to use a variable as an argument in building a database using SQL with the complete code looking like:

<?php

// Php code to include the created db.php and hence inherit connection created.

include("db.php");

.$mdatabase="hellowworld";

// Sql code to create database
$sql = "CREATE DATABASE".$mdatabase;

// Code to execute Sql query
if (mysqli_query($conn, $sql)) {
echo "Database created successfully";
} else {
echo "Error creating database: " . mysqli_error($conn);
}

mysqli_close($conn);

?>

Another implementation would involve creating a PHP function to execute a query to create a database using overloaded data. Such an implementation would like:

<?php

// Php code to include the created db.php and hence inherit connection created.

include("db.php");

function create($conn, $datab)

{

.$mdatabase=$datab

// SQL code to create a database
$sql = "CREATE DATABASE".$mdatabase;

// Code to execute SQL query
if (mysqli_query($conn, $sql)) {
echo "Database created successfully";
} else {
echo "Error creating database: ” . mysqli_error($conn);
}
}
mysqli_close($conn);

?>

This way someone could easily call create (// The connection $conn,// The name of the database to be created “mydb”);

It is important to notice that SQL is language on its own independent from the server-side language running it. This means that a SQL query to create a database implemented in PHP would be similar to one executed by python or C# using MySQL connector to connect to the server. It would also be good to state that all the sample scripts would create empty databases which would have to be populated with tables and later columns and rows for it to be fully functional, also a GUI to modify and monitor the databases, and their corresponding data, can be accessed from http://localhost/phpmyadmin

   

About the Author

ByteScout Team of Writers ByteScout has a team of professional writers proficient in different technical topics. We select the best writers to cover interesting and trending topics for our readers. We love developers and we hope our articles help you learn about programming and programmers.