Basics of working with phpMyAdmin. Setting up phpMyAdmin and MySQL on local hosting Denver How to populate a table in localhost tools phpmyadmin

Good day to all! Today I will tell you about how to create a database inphpmyadmin. And so, first of all, we start the server and go to phpMyAdmin. If you the server is installed on the local computer, That in Danwer phpMyAdmin is located at http://localhost/tools/phpmyadmin/, and in WAMP and on other servers, phpMyAdmin is available at http://localhost/phpmyadmin/

On real hosting, phpMyadmin is available at a different address. Which one you need to find out from your hosting provider.

In version 5.5 it looks like this:

Next, in the new database name field, enter the name of our database, for example test. In the comparison field, select the database encoding, utf8_general_ci. If we do not specify the encoding, that is, we leave the “comparison” as is, then this encoding is selected by default. After filling out these fields, click on the button create.

A message will appear in the middle of the screen that the test database has been created. It will appear in the list with the created databases. We find it in this list, click on its name and find ourselves inside the created database. Initially it is empty, so let's create a table.

There are two ways to create tables:

First way: In the left column click on create table

And the following window will appear:

Set a name for the table and a title for each field. For example, we want to create a table with site categories. Therefore, we set the table name categories. For this table we only need 2 fields, these are id and category names ( name) According to the rules in any table, the first field always has the name id, that is, an identifier. For this field, be sure to select the INT type and set the length of the value in numbers, for example 2. We select the index PRIMARY, so we give it a primary key and insert a checkbox for A_I (Auto Increment) so that its value is automatically increased by one.

For the category name field, select the VARCHAR type and set the maximum length to 255 characters.

If you want to add one more or more fields, then in the field add, enter the number that corresponds to the number of fields you want to add and click ok.

We don’t need four fields, but only two, so we filled in only two fields and left the rest empty. Only filled fields will be added to the table. After you have filled out all the required fields, move down the page a little lower and click on Save.

That's it, a table with a name categories created. It will appear in the list of tables on the left side of the screen.

Second way:

Immediately after entering the database we see such a block Create table. This block has 2 fields where we can enter the name of the table and the number of columns in the table. Let's create, for example, a table with registered users, let's call this table users. This table will have the following fields: id, login, password, e-mail, f_name, s_name, In the number of columns field, enter 6 and click OK.

The same window will appear as in the first method.

After filling out all the fields, go down a little lower and click on the button save.

All table users created. It was also added to the left list with the created databases.

That's all for today. Now you know, how to create MySQL database and tables in phpmyadmin without difficulties and puzzles. I wish everyone good luck!

Did you like the article?

PhpMyAdmin is a script that is written in PHP and provides the ability to manage MySQL databases. Allows you to run SQL query commands, work with database fields and tables (create, edit, delete, etc.). In PhPMyAdmin you can create new databases, make backups and much more. PhpMyAdmin is installed on almost every hosting and is included in the standard configuration of the local Denver server.

In most cases, when working with Joomla, PhрMyAdmin is used only for creating new databases (most often this is done in Denver) and for copying and restoring (Import/Export). The remaining features of PhрMyAdmin are used by advanced Webmasters and developers.

In this article I want to talk about situations in which you can use PhpMyAdmin.

Creation of a database (mostly in Denver).

To do this, type this path in the address bar of your browser:

http://localhost/tools/phpmyadmin

Following this link will launch phpMyAdmin. (Denver must be installed and running).

In order to create a database, you need to enter the name of the future database in the New database field and click the create button, it is worth noting that the database encoding for Joomla must be in UTF-8 (as in the figure below).

If you also need to create a user for the new database, then you need to click on the privileges button and on the page that opens click the link " Add a new user"(The user has certain privileges that are established when he is created, due to these privileges he can change, delete, create new tables and fields in the database). If ррMyAdmin is on the hosting, then login/authorization occurs using the specified username and password) .

Next, you need to fill in the required fields and click the ok button at the bottom of the screen; an example of filling out the fields and actions is shown in the figure below. If everything was done correctly, you will see a message that a new user has been added.

Note. Creating a user is possible only in Denver; on regular hosting, as a rule, the user has already been created and has all the necessary privileges (username and password are sent to your email), or a new user is created through a separate interface of the hosting provider (with all the necessary privileges), where You enter the username and password yourself and then they are used for authorization in phpMyAdmin (just don’t forget to write it down somewhere).

Creating a database backup using phpMyAdmin

To create a database backup, go to the phpMyAdmin main page and in the left window, select the desired database (for which you need to make a backup) by clicking on it.

In the image below, a database named auto (45), in brackets indicates the number of tables that are in the database.

After you select the database, a new window will open with its contents. Next, you need to click on the button “ Export" at the top of the window (as shown in the image below).

The “Export” button means copying the database into a text file and saving it on your computer. By clicking this button, a new window will open with export settings settings, all you need to do here is select all the database tables, check the box " Save as file" and press the button ok in some versions of phpMyAdmin this button is called went.

Please note that when exporting a database, you can specify various settings, for example, pack the database into an archive, this is mainly used when the size of the database is very large and takes up tens or even hundreds of megabytes, since exporting such a heavy database is not possible on some servers It will work out due to hosting limitations. To solve this problem, you need to set the format in which the database will be packaged during export and click the ok button.

Restoring a database from a backup using phpMyAdmin

To restore from a database backup, you need to perform the following sequence of actions. First you need to clear the existing database of all tables. To do this, you need to log in to phpMyAdmin, select the required database in the left column that you are going to restore from a backup, and in the window that appears, you need to go to the very bottom of the page and click on the link “ Select all" and select the option " Delete» from the drop-down list.

This will open the following window with a list of all tables to confirm the deletion of tables. You need to click on “ Yes" After this, you can restore the database from a previously made backup copy.
To do this, go to the tab “ Import" and in the window that appears, click on the button " Review» and find the previously made copy of the database on your computer. Next, you need to make sure that the encoding is UTF-8. Then press the button " OK" and wait for the database backup to finish loading.

Other uses

Here are some more examples of using PhpMyAdmin: one day, while testing one component, the database somehow became corrupted, causing the site to stop working completely. During database corruption, if this happens, a message is displayed stating that the database is damaged or there is no connection. To solve this problem, you need to go to PhpMyAdmin, which also displays the corresponding message. Next, you need to check the damaged database table and select the option from the list - "Restore table", after which you will see the following message: The SQL query was executed successfully.

Optimizing tables in the database via phpMyAdmin

There is also a rather useful table optimization feature. This option deletes all already deleted data that is still stored in the tables, thus significantly clearing the table of garbage and reducing its size. To optimize tables, you need to check all the database tables and click on the link from the list "Optimize table". Just be sure to back up your database in case something goes wrong.

The next way to use PhpMyAdmin's capabilities is to run SQL queries against database tables. This moment is extremely convenient, especially when you need to change something of the same type in all the texts on your site at once. For example, if the number of articles on your site is more than 100, then doing this manually is quite time-consuming and labor-intensive. And here one request and everything is ready.

Just always remember and remember to always back up your data before making any changes. Articles in Joomla are contained in a table called " jos_content", therefore, to make changes to all texts, you need to click on the name of this table. In the window located in the center, go to the “Browse” tab.

On the page that appears, click on "Change". In the new window you need to enter the text of the SQL query, which will make changes to all articles. On the right in the PhpMyAdmin window, you can select only those fields in which you want to make changes.

From all of the above, it turns out that for a restful sleep you need to have up-to-date backups of your database and files on your local PC. These same backups can be used when you transfer your site to a new hosting.

Click on the title to open/close a detailed description of the process

We will need to create a new database - click on the tab "Database". In the subsection text field "Create Database" enter phpmyadmin, press the button "Create".

Let's create a new user - click on the tab "Users".

At the moment we have a single user created with the name root. Click on the link "Add user",

in the pop-up window that opens, in the section "Account Information" to text field "Username:" enter "pma". To text field "Host:" enter "localhost". In field "Password:" enter the password (any one that comes to your mind). In field "Confirmation:" Confirm the password entered above.

Scroll the window to the section "Global Privileges", click on the link "Select all".

Click on the button "Add user".

A new user named pma will be created.

Now, you need to import data into the created database. In the left column select phpmyadmin(click on it with the mouse).

In the top menu, click on the tab Import.

In the subsection Imported file: Click the review button.

In the context menu that opens, select the file create_tables.sql which is in the folder examples, located inside the phpMyAdmin root directory (in my case the full path looks like this: c:\Program Files (x86)\Apache Software Foundation\Apache2.2\htdocs\phpmyadmin\examples\create_tables.sql).

At the very bottom of the page we find and press the button Ok. The database has been imported.

Let's make changes to the configuration file.

Open the file in notepad config.inc.php. Looking for a string $cfg["Servers"][$i]["controlhost"] = "";, uncomment it (remove the double slash), enter it in quotes localhost(the line will look like $cfg["Servers"][$i]["controlhost"] = "localhost";).

Find and uncomment the line $cfg["Servers"][$i]["controlpass"] = "pmapass";, in quotes, change the password to the one that was assigned when creating the user pma.

Find and uncomment the following lines:

$cfg["Servers"][$i]["controluser"] = "pma";

$cfg["Servers"][$i]["pmadb"] = "phpmyadmin";

$cfg["Servers"][$i]["bookmarktable"] = "pma_bookmark";

$cfg["Servers"][$i]["relation"] = "pma_relation";

$cfg["Servers"][$i]["table_info"] = "pma_table_info";

$cfg["Servers"][$i]["table_coords"] = "pma_table_coords";

$cfg["Servers"][$i]["pdf_pages"] = "pma_pdf_pages";

$cfg["Servers"][$i]["column_info"] = "pma_column_info";

$cfg["Servers"][$i]["history"] = "pma_history";

$cfg["Servers"][$i]["tracking"] = "pma_tracking";

$cfg["Servers"][$i]["designer_coords"] = "pma_designer_coords";

$cfg["Servers"][$i]["userconfig"] = "pma_userconfig";

$cfg["Servers"][$i]["recent"] = "pma_recent";

$cfg["Servers"][$i]["table_uiprefs"] = "pma_table_uiprefs";

We save the changes to the file, exit and enter again phpMyAdmin. The warning has disappeared.

As an alternative (if you really don’t want to dig into the code) - replace the entire contents of the file config.inc.php to the code below

< $cfg["blowfish_secret"] = "a8b7c6d"; $i = 0; $i++; $cfg["Servers"][$i]["auth_type"] = "cookie"; $cfg["Servers"][$i]["host"] = "localhost"; $cfg["Servers"][$i]["connect_type"] = "tcp"; $cfg["Servers"][$i]["compress"] = false; $cfg["Servers"][$i]["extension"] = "mysqli"; $cfg["Servers"][$i]["AllowNoPassword"] = false; $cfg["Servers"][$i]["controlhost"] = "localhost"; $cfg["Servers"][$i]["controluser"] = "pma"; $cfg["Servers"][$i]["controlpass"] = "пароль для пользователя pma"; $cfg["Servers"][$i]["pmadb"] = "phpmyadmin"; $cfg["Servers"][$i]["bookmarktable"] = "pma_bookmark"; $cfg["Servers"][$i]["relation"] = "pma_relation"; $cfg["Servers"][$i]["table_info"] = "pma_table_info"; $cfg["Servers"][$i]["table_coords"] = "pma_table_coords"; $cfg["Servers"][$i]["pdf_pages"] = "pma_pdf_pages"; $cfg["Servers"][$i]["column_info"] = "pma_column_info"; $cfg["Servers"][$i]["history"] = "pma_history"; $cfg["Servers"][$i]["table_uiprefs"] = "pma_table_uiprefs"; $cfg["Servers"][$i]["tracking"] = "pma_tracking"; $cfg["Servers"][$i]["designer_coords"] = "pma_designer_coords"; $cfg["Servers"][$i]["userconfig"] = "pma_userconfig"; $cfg["Servers"][$i]["recent"] = "pma_recent"; $cfg["UploadDir"] = ""; $cfg["SaveDir"] = ""; ?>

Don't forget to write in the line $cfg["Servers"][$i]["controlpass"] = "password for user pma"; in quotes the password that was set for the user pma and in the line $cfg["blowfish_secret"] = "a8b7c6d"; increase the inscription in quotation marks to at least 10 characters

Each new installation of CMS Joomla requires the creation of a new database. This database will store data such as articles/materials, menus, categories and users. This data is necessary to manage the site on Joomla!

To install Joomla, you must have a working database, a database user, a database user password, and appropriate database user privileges.

This material will look at the most common type of database used when installing Joomla, namely the MySQL database. We will look at two options for creating a database for Joomla. The first method will be to create a database on a local server, and the second will be to create a database on hosting, with an emphasis on using DirectAdmin - the hosting control panel. But other hosting control panels should have similar database creation steps.

Creating a MySQL database on a local server

In order to create a database on a local server, you must first install it. Therefore, we download the local server – Denwer [Download] and install it. Denwer installation instructions .

After successfully installing the local server, you can begin creating a database for installing Joomla! In order to get to the database management interface, you need to start the local Denwer server (if not running) and enter in the browser address bar: http://localhost/tools/phpmyadmin. In the window that opens, you will see the “phpMyAdmin” web interface. Now you can proceed directly to creating the database.

The database and the user for it have been created, now you can begin installing Joomla on the local server.

Creating a MySQL database on hosting

As mentioned above, creating a database on hosting will be carried out using the example of the DirectAdmin control panel. But all stages of creation will be similar to any hosting control panel.

To create a database on your hosting, you need to log into your hosting control panel. You should know how to log into your hosting control panel yourself; when you registered your hosting, you should have been sent all the information on how to use it. Otherwise, you can clarify any information by contacting the technical support of your hosting provider.

Once you have logged into your hosting control panel, you can proceed directly to creating a database.

Now you can install Joomla 2.5(or install Joomla 3.1) directly on the hosting and at a certain stage of the installation you will have to enter the data that needed to be recorded (database name, username, user password and host).

Good day. Today I will focus on working with the phpMyAdmin utility. This utility allows us to work with the MySQL database server. To be more precise, phpMyAdmin allows you to:

1. Create a database
2. Create tables in the database
3. Add, delete and edit data in tables
4. Search data
5. Set privileges on a database, table
6. Back up and restore the database
7. And much more

Those. phpMyAdmin allows you to make initial settings for the database and its contents. And now about all this in detail...

Installing phpMyAdmin

phpMyAdmin is a collection of html, css, javascript and php files - it is a small website that manages MySQL. Installing phpMyAdmin involves simply copying files to your web server. I described how phpMyAdmin is installed in the article Installing and configuring apache+php+mysql on Windows. Part 3. After we have installed phpMyAdmin in the address bar of the browser we type the address<Ваш сайт>/phpMyAdmin and we get to the phpMyAdmin authorization window

In the “User” field enter root, and in the “Password” field enter the password that you set for the root user when installing MySQL.

Below them are the names of the databases that are located on the MySQL server. Be careful with the databases here: information_shema, mysql, performance_shema are service databases and if you don’t know what they are responsible for, then it’s better not to touch them.
On the right side or the main one at the top we see a menu

Below this menu we see:

Creating a database using phpMyAdmin

To start creating a database, go to the “Databases” menu

Enter the name of the database in the field and click “Create”

In order to delete a database, you need to select the required database in the database window and click “Delete”

And here we will see another great feature of phpMyAdmin that will appeal to those who are just learning the SQL language: phpMyAdmin shows us the query.
To delete the database, you must confirm the request.

Working with the database via phpMyAdmin

In order to start working with the database, you need to log into it. To do this, you can go to the required database on the left side. Or in the database window, click on the link to the required database

Let's create a table test in our database. To do this, in the window of our database, enter the name of the table in the field, and in the number of columns field, set, for example, 2 and click “Ok”

In the next window we are asked to fill in the column data:

1. Name - column name
2. Type - column type
3. Length - column length
4. Default - the value that will be substituted by default if you specified it
5. Comparison - how the data will be searched
6. Attributes - Column Attributes
7. Null - whether the column can be empty
8. Index - field index
9. A_I - whether this column is auto-incrementing
10. Comment - comment for this column

We also need to specify the column type.
After entering all the necessary data, click “Save”

We see that our table has appeared

Now we can:

1. execute an SQL query - this is done in the “SQL” menu
2. search for data in our database - this is done in the “Search” menu
3. We can make a request using a template, which can be done in the “Request by template” menu
4. Export database data to various formats - this is done in the “Export” menu
5. Import data into the database in the “Import” menu
6. Set privileges on the database: create users for this database and configure their access to data - this is done in the “Privileges” menu
7. We can delete the table. To select the required table and click “Delete”

Working with data

In order to start working with data, we need to go to the table with the data we want to work with. To get to the required table, you need to follow the link with the name of this table.

After which we will see the structure of this table

In order to add data to the table, you need to go to the “Insert” menu and start adding data. In general, we can use the data in the table:

1. Browse.
2. Add.
3. Delete.
4. Change.
5. Copy.
6. Search using various criteria.

In phpMyAdmin we can add users by assigning them certain privileges. We can create a user both for the entire database server and for a separate database. Let's use our test database test as an example to create a user and assign him certain privileges. To do this, let's go to our test database and click privileges in the menu.

In the next window, click “Add user”

In the next window, fill in all the fields:

1. Username (login
2. Host - select access restriction: from any machine, from a local machine, use a host table or use a text field.
3. Password — enter the password for this account (if you generate a password, you do not need to enter anything)
4. Confirmation - repeat password
5. Create a password - when you click on the “Generate” button, phpMyAdmin will automatically generate a password

After filling out all the fields, click “Add user”

If the user is successfully added, you will see the following window

To change privileges, click “Edit privileges” next to the required user.
This concludes my review of the basics of working with phpMyAdmin. If you are interested in studying phpMyAdmin in more detail, then write in the comments and I will definitely write a series of articles on phpMyAdmin. In general, I don’t recommend getting hung up on this utility, and learning how to work with MySQL through the console is much faster, more reliable, and you can do almost whatever you want with the database server.