Create database for php

PHP MySQL Create Database

In this tutorial you will learn how to create a database in MySQL using PHP.

Creating MySQL Database Using PHP

Now that you’ve understood how to open a connection to the MySQL database server. In this tutorial you will learn how to execute SQL query to create a database.

Before saving or accessing the data, we need to create a database first. The CREATE DATABASE statement is used to create a new database in MySQL.

Let’s make a SQL query using the CREATE DATABASE statement, after that we will execute this SQL query through passing it to the PHP mysqli_query() function to finally create our database. The following example creates a database named demo.

Example

 // Attempt create database query execution $sql = "CREATE DATABASE demo"; if(mysqli_query($link, $sql)) < echo "Database created successfully"; >else < echo "ERROR: Could not able to execute $sql. " . mysqli_error($link); >// Close connection mysqli_close($link); ?>

connect_error); > // Attempt create database query execution $sql = "CREATE DATABASE demo"; if($mysqli->query($sql) === true) < echo "Database created successfully"; >else< echo "ERROR: Could not able to execute $sql. " . $mysqli->error; > // Close connection $mysqli->close(); ?>

setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); > catch(PDOException $e)< die("ERROR: Could not connect. " . $e->getMessage()); > // Attempt create database query execution try< $sql = "CREATE DATABASE demo"; $pdo->exec($sql); echo "Database created successfully"; > catch(PDOException $e)< die("ERROR: Could not able to execute $sql. " . $e->getMessage()); > // Close connection unset($pdo); ?>

Tip: Setting the PDO::ATTR_ERRMODE attribute to PDO::ERRMODE_EXCEPTION tells PDO to throw exceptions whenever a database error occurs.

Источник

PHP | MySQL ( Creating Database )

What is a database?
Database is a collection of inter-related data which helps in efficient retrieval, insertion and deletion of data from database and organizes the data in the form of tables, views, schemas, reports etc. For Example, university database organizes the data about students, faculty, and admin staff etc. which helps in efficient retrieval, insertion and deletion of data from it.

We know that in MySQL to create a database we need to execute a query. You may refer to this article for the SQL query to create data-bases.

The basic steps to create MySQL database using PHP are:

• Establish a connection to MySQL server from your PHP script as described in this article.
• If the connection is successful, write a SQL query to create a database and store it in a string variable.
• Execute the query.

We have already learnt about establish a connection and creating variables in PHP. We can execute the query from our PHP script in 3 different ways as described below:

1. Using MySQLi Object-oriented procedure: If the MySQL connection is established using Object-oriented procedure then we can use the query() function of mysqli class to execute our query as described in the below syntax. Syntax:

// Creating a connection $conn = new mysqli($servername, $username, $password); // Check connection if ($conn->connect_error) < die("Connection failed: " . $conn->connect_error); > // Creating a database named newDB $sql = "CREATE DATABASE newDB"; if ($conn->query($sql) === TRUE) < echo "Database created successfully with the name newDB"; >else < echo "Error creating database: " . $conn->error; > // closing connection $conn->close(); ?>

Note:Specify the three arguments servername, username and password to the mysqli object whenever creating a database. Output:

Using MySQLi Procedural procedure: If the MySQL connection is established using procedural procedure then we can use the mysqli_query() function of PHP to execute our query as described in the below syntax. Syntax:

// Creating connection $conn = mysqli_connect($servername, $username, $password); // Checking connection if (!$conn) < die("Connection failed: " . mysqli_connect_error()); >// Creating a database named newDB $sql = "CREATE DATABASE newDB"; if (mysqli_query($conn, $sql)) < echo "Database created successfully with the name newDB"; >else < echo "Error creating database: " . mysqli_error($conn); >// closing connection mysqli_close($conn); ?>

Output:

Using PDO procedure: If the MySQL connection is established using PDO procedure then we can execute our query as described in the below syntax. Syntax:

// setting the PDO error mode to exception $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $sql = "CREATE DATABASE newDB"; // using exec() because no results are returned $conn->exec($sql); echo "Database created successfully with the name newDB"; > catch(PDOException $e) < echo $sql . "
" . $e->getMessage(); > $conn = null; ?>

Note:The exception class in PDO is used to handle any problems that may occur in our database queries. If an exception is thrown within the try < >block, the script stops executing and flows directly to the first catch() < >block. Output:

Источник

MySQL Database

A dynamic and complex website is usually driven by a database of some sort. In this lesson, we’ll learn how to work with a database of our own, using one of the most popular database management systems around, MySQL.

Overview of MySQL

MySQL is used so widely with PHP because it is easy-to-use, fast, secure, scalable and extremely powerful. In addition, it runs on a wide range of operating systems and therefore is the ideal database management system to use for applications and websites of all sizes. The logo of MySQL.

MySQL databases store their data into individual tables, each divided up into rows and columns. Columns define what kind of data the rows hold, and each row represents an entry into that table.

Here’s an example of how a table is structured in MySQL:

[email protected] | | 2 | JohnCena | [email protected] | | 3 | Drake | [email protected] | | 4 | Beyonce | [email protected] | | 5 | Nas | [email protected] | +----+------------+----------------------------------+ 

1. id : The user’s identification number.
2. username : The username of the account.
3. email : The user’s email address.

Then we have five rows, each row being a completely separate user.

To keep things simple, we’ll only be working with strings and numbers, but MySQL supports a wide range of data types, include timestamps, bits, and many more.

Connecting to a MySQL Server

To work with a MySQL database, we first must be able to connect to a MySQL server. Thankfully, this is a relatively simple thing to do. The basic syntax for it looks like this:

You simply pass in a string containing the MySQL server name and then the username and password of the account you’re trying to access that server with.

Create a separate file called database.php to hold our database details, and put this in it:

Fill in the variables with your own details and you should be good to go and ready to work with your MySQL database by simply importing this file whenever you want to use the newly created $pdo variable:

Creating a MySQL Database and Table

Unless you already did so, you’ll need to create a database on the database server. This is because a single server can host many different databases. Let’s create a database using PHP.

exec($query); echo('Database created successfully.'); ?> 

The text you see here, CREATE DATABASE IF NOT EXISTS is something called SQL, which stands for Structured Query Language. Simply put, it is how we can describe commands for MySQL to follow and do the stuff we want it to do.

In this case, we are telling MySQL to create a new database if one with that same name does not already exist, and then telling it the name of the database to create, app .

Now app is an empty database on our database server. We need to define a table for our users on it. Here’s how to create a users table with our three columns on our app database:

exec($query); echo('Table created successfully.'); ?> 

Without getting too complicated, that command used the CREATE TABLE to create a table with our desired name, users . Then it added a column named id as an integer that auto-increments (each new entry gets a unique number that is just the increment of the row’s id), followed by some text for the username and some more text for the email. Finally, the query was executed and our table was created successfully!

Deleting a MySQL Database and Table

Before moving on to the fun stuff, it is worth learning how to undo what we just did. In other words, we should learn how to delete a MySQL database and table.

Here’s how to delete, or drop, the MySQL table we just created:

exec($query); echo('Table dropped successfully.'); ?> 

Running that script will drop the database with the name you provided. Now here’s how to delete the entire database, along with every table that might exist on it:

exec($query); echo('Database dropped successfully.'); ?> 

As you can tell, the commands to drop a table and database are basically identical, and therefore you should be very careful when using either command!

Inserting Data into a MySQL Database Table

Assuming we still have both our MySQL database and table, let’s learn how to insert data into them. Inserting data involves specifying the columns you want to insert data into, and then providing the values for each column.

Let’s add a new user to our app’s database:

 $username, 'email' => $email ]; $statement = $pdo->prepare($query); $statement->execute($params); echo('User added successfully.'); ?> 

First we manually defined the values that we wanted to insert. In a real application, this might come from user input. After that, we define the query to run. Since we are inserting data, we use the INSERT INTO SQL statement and pass in the table we want to use, users . Then we define the two columns we want to insert data into, the username and email columns, along with two placeholder variables, :username and :email . The value of these variables are then provided by the following $params array.

Finally we can prepare the statement and then execute it using the desired parameters.

If all went well, your database should now have a new entry at the bottom and look like this:

[email protected] | | 2 | JohnCena | [email protected] | | 3 | Drake | [email protected] | | 4 | Beyonce | [email protected] | | 5 | Nas | [email protected] | | 6 | Biggie | [email protected] | +----+------------+----------------------------------+ 

Because we set the id column to auto-increment, we don’t have to pass in a value for it. MySQL adds the correct value for us.

Getting Data from a MySQL Database Table

Great, now let’s learn how to get data from a MySQL table now that we’ve inserted some. To get some data back, we use the SELECT SQL statement and define the condition for it. Let’s say we want the first user in our table:

execute(); $data = $statement->fetch(PDO::FETCH_ASSOC); print_r($data); ?> 

We’re using the SELECT SQL statement, passing along the exact columns we want from the row, specify the table as FROM users then use a WHERE SQL statement to define that we only want the row where the id matches the value we defined, 1 .

Our code prepares the statement, the binds the parameter, executes it, then returns the data in an array that we put in the $data variable. Finally, we print out that variable to see that contents are exactly what we wanted.

Updating Data on a MySQL Database Table

You can update data on a MySQL database table by using the UPDATE SQL statement.

[email protected]'; $query = 'UPDATE users SET username = :username, email = :email WHERE $params = [ 'id' => $id, 'username' => $username, 'email' => $email ]; $statement = $pdo->prepare($query); $statement->execute($params); echo('User updated successfully.'); ?> 

As usual, the values are provided in a variable for demonstration purposes, but in a more developed application, this might come from user input. We are setting new data on the row whose id is equal to 1 . By the end of this script, our database should look like this:

[email protected] | | 2 | JohnCena | [email protected] | | 3 | Drake | [email protected] | | 4 | Beyonce | [email protected] | | 5 | Nas | [email protected] | | 6 | Biggie | [email protected] | +----+------------+----------------------------------+ 

Deleting Data from MySQL Database Table

When you want to delete data, you’re usually only trying to delete data within a table, not the entire table nor the entire database. For this case, we can use the DELETE FROM SQL statement.

Let’s say we want to delete our last entry in our table, the 6th entry:

 $id ]; $statement = $pdo->prepare($query); $statement->execute($params); echo('User deleted successfully.'); ?> 

By now the syntax should be familiar. We are asking MySQL to delete data from the users table wherever the row’s id column is equal to 6 . That deletes the 6th entry in our table, leaving the table finally looking like this:

[email protected] | | 2 | JohnCena | [email protected] | | 3 | Drake | [email protected] | | 4 | Beyonce | [email protected] | | 5 | Nas | [email protected] | +----+------------+----------------------------------+ 

Conclusion

Database management systems are powerful and flexible tools that allow for dynamic and robust websites and applications. They manage and store our data, following the commands we give them for manipulating and serving that data to us. This was just an introduction to the world of databases, but hopefully this helped you get started with the basics of working with MySQL in PHP!

Resources

Источник