Create a table using a query in phpmyadmin. Creating a mysql database in phpmyadmin
Welcome to the new release of the mysql section, where we will discuss with you adding entries to PHPMyAdmin. In just a few days, we have mastered how to create databases, tables, and users, and now it’s time to begin the contents of these very tables - records. To do this, I will ask you to go to the database you created, and in the list of tables, select the one to which you want to add a record. A button will appear in the top menu insert. This is exactly what we need. After you click it, you will see the following view:
Now you have to fill all the fields according to your requirements. In my example, you need to fill in only three fields, the first is the id of the inserted record, the second is the title, and the third is the text. You can even store HTML code in a database; no one forbids this, and many people do this. For field id we will not indicate anything, because, if you remember, we specified an auto increment, thanks to which each entry will have a new value. Therefore, we leave this field empty, and fill the next two with the necessary data in the value column. If you noticed, there is another column called function. You and I will almost always leave it unchanged, and perform all operations through PHP. You can now add an entry by clicking ok. And as a result you will get this result:
And already in this window you can manage records, edit them, or delete them. We won’t go into this anymore, because everything is quite clear there and is written in Russian. I think you can figure this out on your own. This concludes our lesson, because you have already learned add entries to PHPMyAdmin. See you soon!
Hello, first post of 2017! While everyone was finishing their salads, I decided to write a short note about foreign key constraints in the MySQL database.
Why are foreign keys needed in a table?
Foreign keys regulate relationships between tables. Thanks to them, control over the structure of the database is greatly simplified, the application code is reduced and simplified, because we shift most of the responsibility for this from our shoulders to the database itself. Properly configured foreign keys ensure that data integrity increases by reducing redundancy.
In a nutshell, there are many benefits to foreign keys, so it’s useful to know how to use them.
Setting up foreign keys
I will demonstrate the setup in my favorite because it is very convenient, in addition, the admin does not hide the generated request code and you can always see it( remember, copy, criticize).
Example ManyToOne and OneToMany
Two tables: goods(id, name, catalog_id) and catalogs(id, name) . In one directory a bunch of goods (OneToMany), and any number of goods can be in one directory(ManyToOne). The foreign key in this example is the catalog_id field in the goods table.
Creating tables in phpmyadmin
Generated queries
CREATE TABLEmybd.goods (id name VARCHAR(255) NOT NULL , catalog_id INT NOT NULL, PRIMARY KEY ( id), INDEX( catalog_id)) ENGINE = InnoDB; CREATE TABLE mybd.catalogs (id INT NOT NULL AUTO_INCREMENT , name VARCHAR(255) NOT NULL , PRIMARY KEY ( id)) ENGINE = InnoDB; Setting up the foreign key catalog_id
It's time to create a link between products and catalogs. Open the goods table, go to the “structure” tab and look for the “connections” button.
In the “foreign key constraints” section, fill in the lines and select the “ON DELETE” and “ON UPDATE” actions.
Generated request
ALTER TABLEgoods ADD FOREIGN KEY ( catalog_id) REFERENCES mybd.catalogs(id) ON DELETE CASCADE ON UPDATE RESTRICT; ON DELETE and ON UPDATE meanings
CASCADE— Cascade deletion and editing. This setting means that when you delete a catalog, all products from it will also be deleted. When editing, if we change the catalog id, the “catalog_id” field for products will automatically change.
RESTRICT— With this setting, if we try to delete a catalog that contains products, or change its id, the database will give us an error and the deletion will not take place.
SET NULL— From the name it is clear that if a catalog with such an id disappears (is deleted or changes), then the value of the products in the “catalog_id” field will be set to NULL. You need to be careful with this setting because indexes are "NOT NULL" by default.
NO ACTION— We ignore deleting and editing a catalog, and even if there is a non-existent identifier in the “catalog_id” field, we simply ignore it.
ManyToMany example
The case is more complicated; in order not to duplicate records, a separate table of relationships is created, which usually consists of two fields. Let's look at an example: authors(id, name) , books(id, name) , author_book(author_id, book_id). A book can be co-authored, and the author can have multiple books - a great example of a ManyToMany relationship.
Creating a link table
In phpmyadmin
Generated request
CREATE TABLElibrary.author_book (author_id INT NOT NULL, book_id INT NOT NULL , INDEX ( author_id, book_id)) ENGINE = InnoDB; author_id and book_id together are a composite index, all that remains is to add restrictions on each of them in the “links” tab and you’re done!
Conclusion
Don't miss the opportunity to add more responsibilities to the database to make it easier for you to work on the application, and think about its structure rather than controlling table relationships. Good luck with your database design, thanks for your attention!
Last update: 12/22/2017
Typically, databases are used as data storage. PHP allows you to use various database management systems, but the most popular today in conjunction with PHP is MySQL. MySQL is free software that allows you to interact with databases using SQL commands. The process of installing and configuring MySQL has already been discussed.
To make it easier for us to work with MySQL databases, let's install a special set of phpMyAdmin scripts. phpMyAdmin provides an intuitive web interface for managing MySQL databases. Using this tool, it is much easier to work with databases than to manage MySQL through the console.
To install phpMyAdmin, download the archive and unpack it into a folder C:\localhost where are the docs for php. Let's rename the unpacked folder to phpMyAdmin for brevity.
In the unpacked phpMyAdmin directory, create a file config.inc.php with the following content:
And to make sure that everything is configured correctly, in the browser let's go to phpMyAdmin, for example, http://localhost:8080/phpmyadmin:
In the left column you can see all the available databases on the MySQL server. Even if you haven't created any databases yet, the server already has a set of default databases.
The right side of the phpMyAdmin interface contains basic database management tools, as well as various configuration information.
Creating a MySQL Database in phpMyAdmin
To exchange data with the MySQL server (save, change, delete, retrieve data), we naturally need a database. We can create a database from the MySQL console, as well as from the phpMyAdmin visual interface.
Let's open the phpMyAdmin interface. Let's go to the Databases tab. Under the label Create a database Let's enter some name for the new database, for example, compstore and click on the "Create" button.
And after that we will receive a message about the successful creation of a new database, and it will be added to the database lists.
The new database is still empty and contains nothing. Let's add a table to it that will store the data. To do this, click on the name of the database and we will be taken to the “Structure” tab, where we will be offered options for the new table. In the "Name" field, enter the name of the new table. Let the table store data about smartphone models, so let's enter the name "phones", and enter the number 3 as the number of columns:
To create a table, click on the “Forward” button. After this, we will have a set of cells for setting column parameters. Let us indicate the following sequentially for the column names: id, name, company. As a type, we will specify the INT type for the id columns, and the VARCHAR type for the name and company columns. For the name and company columns, in the "Length/Values" field, enter the number 200 - it will indicate the maximum line length in characters. Also, for the id column, indicate PRIMARY in the “Index” field and put a checkmark in the “A_I” (AutoIncrement) field:
So the table will have columns for Unique ID, Phone Name and Manufacturer Name. And then click on the “Save” button below.
After creating the table, we can see the table and its columns in the database column:
This is not the only way to create tables in phpMyAdmin, as here we can manipulate the database using SQL queries. So, select our database in the list of databases and go to the “SQL” tab. It displays a field for entering a command in the SQL query language. Let's enter the following command into it:
CREATE Table phones1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(200) NOT NULL, company VARCHAR(200) NOT NULL)
This is a standard SQL table creation command. After the CREATE Table keywords comes the name of the table being created, and then the column definitions in parentheses, separated by commas.
Each column definition contains the column name, its type, and a number of additional values. For example, the definition of the id column (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) indicates the name of the column, the type is INT, and also that the column must have a value - NOT NULL, that its value will automatically increase by one with the addition of a new object - AUTO_INCREMENT, and that it plays the role of a primary key - PRIMARY KEY.
In general, the table created in this way will be equivalent to the one created earlier. Click the Forward button and a second table, phones1, will be created.
To connect to phpMyAdmin you can use the already created database u1234567_default(Where u1234567— your hosting login), which is automatically created when you order hosting. Access details for this database are given in the article.
Attention Database u1234567_default is created only when ordering tariff plans from “Host-0” and higher. If you ordered the “Host-Lite” tariff plan and then upgraded it to a higher one, this database will not be created. You need to create the database yourself according to the instructions:
I can't login to phpMyAdmin
To log into phpMyAdmin you need to use database user login and password. Do not confuse the database user login and password with the hosting service login and password (u1234567).
If the database password is not suitable, you can change it to a new one using the instructions:
How to create, delete or modify a table in the phpMyAdmin database?
Attention! Editing the database may cause your site to not work correctly. Before making changes, create a backup of your site or contact the developers.
How to add a new table to a database?
In this post we will learn how to create communications between tables in a database MySQL by using phpmyadmin. If for some reason you do not wish to use phpmyadmin, see the SQL queries below.
Why is it convenient to keep connections? in the database itself? After all, this task is usually solved by the application itself?It's all about the restrictions and change actions that can be placed on the connections.
For example, you can prohibit deleting a category if at least one note is associated with it. Or delete all notes if the category is deleted. Or set NULL to the linking field. In any case, with the help of connections, the fault tolerance and reliability of the application increases.
To begin with, the table engine must beInnoDB. Only it supports foreign keys (foreign key). If you have tablesMyISAM, read how to convert them toInnoDB .
In order to link tables by fields, you must first add to index linked fields:
IN phpmyadmin select the table, select the structure mode, select the field for which we will make an external link and click Index.
Note the difference between "Index" and "Unique". A unique index can be used, for example, before the id field, that is, where the values are not repeated.
The same action can be done using SQL-query:
ALTER TABLE `table_name` ADD INDEX (`field_name`) ;
Similarly, we add an index (only in my case, now unique or primary) for the table we are referring to, for the id field. Since the id field is an identifier, we create a primary key for it. A unique key might be needed for other unique fields.
By using SQL-query:
ALTER TABLE `table_name` ADD UNIQUE (`field_name`);
Now all that's left is link tables. To do this, click on the Contacts item below:
Now, for the available fields (and only indexed fields are available), we select the connection with external tables and actions when changing records in the tables:
Through SQL-request:
ALTER TABLE `table_name` ADD FOREIGN KEY (`field_in_table_name_which_need_connect`) REFERENCES `outer_table_to_connect` (`outer_field`) ON DELETE RESTRICT ON UPDATE RESTRICT ;
That's all, the tables are connected via foreign key.