An MySQL database is an essential element in numerous websites. Although you can easily make and edit the databases on the backend of your website being able to do this using web code is extremely effective. But how do you do it?
PHP is an extremely powerful server-side scripting language that can perform every MySQL database-related operation that you require. Learn how to make use of it to create edit and manage your database from the convenience of your web site.
What do you need to be able to use the MySQL Database With PHP?
Before you begin creating an PHP script, you’ll need an environment that you can run it on. EasyPHP gives you an online web server locally with access to phpMyAdmin and directory management tools — everything you need to test and evaluating your personal PHP code. It is also possible to make an active PHP website to use for this project so long as you have phpMyAdmin installed.
Visit visit the EasyPHP web site for downloads of the Devserver installation. After downloading, open this executable, and go through the steps to install it. It is possible to launch EasyPHP from here however, wait for an hour or so to complete installing.
The EasyPHP icon will appear in the taskbar tray. click it right to click on Servers and choose Restart/Start All Servers to make sure you have your webserver local to you as well as database are running. Make sure you right-click on the icon in your taskbar and choose open Dashboard.
Add Your Project Directory to EasyPHP
When EasyPHP is installed then you’re ready to create a new file on your PC for the PHP files. This file can be added to any directory you want however, you must be aware of the location in order to connect it to EasyPHP.
Click on in the directories section, then select Add Directories and then click Add. It will bring you to an additional screen, where you can enter the Working directory’s name (this could be any name you want to) and the path to the directory that you are working in. The path is the location of the file in your personal computer. In our case, it is simply C:EasyPHP.
You can now create PHP files within the working directory of your system and access them via the EasyPHP dashboard, which is located under section Working Directory section.
Create a Database with MySQL Connect
In the moment at this point, you don’t have a databases to play with at least not yet. Making a database using PHP is simple, however you’ll need to connect to your internet server locally before you begin. Begin by adding variables for the server’s name, username for the server, and password for the server.
This variable refers to the server’s IP address, however, you can also make use of localhost using EasyPHP and some other locally-hosted MySQL database. For default, the username of EasyPHP is root, and there isn’t a password set. Live deployments should always be protected by an account password. Following this is done, you can utilize PHP to connect to the database by using these variables.
Create a mysqli database instance, providing it with your servername, username and password. This allows your script to have the capability to connect to your server’s database. Check to see whether there are any connections errors.
Now is the time to execute the SQL query. It’s similar to running queries through tools such as phpMyAdmin but using PHP to transmit the query. Begin by creating an CREATE DATABASE query to create the database.
First, the code for query. It is a CREATE DATABASE query is all that’s needed is the name of the database, for example “MUOdatabase”. Examine the results of your query and ensure that the database has been created. The last line of code shuts down the connection to ensure that your MySQL database secure..
Insert this code into an PHP script file within your Working Directory you created earlier before, and then open the script file via your EasyPHP Dashboard to run the code you wrote. There will be a blank page, with either a success warning or error.
If you get a successful message, visit phpMyAdmin via the EasyPHP dashboard to verify that the database has been set up.
Design an MySQL Database Table With PHP
The database isn’t yet enough to hold information; you require tables to store the data in. As with creating the database from scratch in order to do this, you must join your database in order to achieve this, but you must also include the name of the database in an attribute. Without this you will find that your SQL queries will not point to any place.
This CREATE TABLE SQL query is more complex than the majority of. The table you create must have an identifier (we named ours MUOtable) and you’ll have to define the columns it will contain.
First, the column has an automatic incrementing value, which gives each record an unique IQ that starts at 0 and increasing with every new record. In addition there are three columns of varchar that accept strings of numbers as well as letters (animal color, animal and country) as well as a time stamp column to track the last time a record was updated.
Like you did when you made your database, verify whether the query worked or was unsuccessful. Also, you should end the connection when you have completed the procedure as described above.
Include this code in an PHP script file within Your Working Directory, save it and launch it using your EasyPHP dashboard to execute the query. After you’ve completed this you’ll notice the new table being added to your database through phpMyAdmin.
From this point, you’ll only have to modify the code inside the SQL Section for Query remarks for every query you’ll run.
Create Data for the MySQL Table Using PHP
Now is the time to add data on your data table! The INERT INTO query is easy to use; all you need is the table’s name and the columns you’d want to include data to along with the actual data. This example includes Elephant to the column for animals, Grey to the color column in addition to England into the country column. After you’ve run the script several times, using various data types the table will begin to develop.
Edit and delete data inside the MySQL Table using PHP
Based on the information within the table, there’s an error. Elephants aren’t found in England So the table must be updated with the correct information. A UPDATE query would be the most efficient method of doing this.
This SQL DELETE query is very similar. It deletes records in a table based on the ID you specify by way of example:
Choose Data in a MySQL Table Using PHP
The SELECT query enables you to extract data from your table and then use it within your program. It must know the table you wish to connect in order to utilize a while loop in order to display the data in HTML.
Working with MySQL Databases as well as PHP
PHP as well as MySQL databases offer web designers an array of powerful tools however security must always be an issue. Maintaining your PHP scripts safe isn’t an easy task, and it’s worthwhile to study server-side security before deploying the code on an active website.
