How to use SQL Manager in CodeLobster PHP IDE

Probably, every PHP programmer had to create his own framework or library, and it was necessary to work quickly and do everything practically from scratch.

Often we need to test some of our ideas or algorithms and we write code for interacting with databases directly, without using ORM frameworks.

In addition, SQL that is used in various libraries and CMS might not work quite efficiently and slow down the site.

In such cases, you need to open the SQL editor and start optimizing the queries.

We bring to your attention an article in which the SQL-manager built-in in CodeLobster PHP IDE will be considered.

On a simple example, we will learn how to effectively use the SQL editor and how to create and configure database connections.

Creating and configuring a connection to MySQL

Go to the main menu "Tools" -> "MySQL" -> "SQL Manager". In the opened tab on the right information panel we have the opportunity to add a host and database, for this click the "Register Host" button.

Fill in all the required fields in the dialog box that appears, and straight away let’s give the connection an informative name and enter it in the "Connection Alias" field.

Then we must specify the host and port of our MySQL server and the name of the database with which we are planning to work.

It remains to enter the correct username and password and click "OK".

It is done, the host and database are registered and we can begin to examine the functionality of the SQL manager.

Working with tables and data using SQL manager

The main advantage of the built-in CodeLobster SQL manager is the ability to perform the full range of operations necessary for quality work with database.

A classic set of such functions is called CRUD (Create, Read, Update, Delete). You can create and modify tables in the visual mode, add and delete records, as well as read and edit any information.

This, of course, will save a lot of time for the developer at the stage of designing a database for his application.

For our example, we need to create a table in which information about useful books will be stored, and then display this data in a conveniently readable form using a PHP script.

In the program window, right-click on the name of database to which we just connected and select "Create Table" from the context menu. Enter the name of the table – "books" and click "OK".

Now the CodeLobster opens the table editor, in this tab click the "New Field" button.

In the dialog that appears, enter the name of the field "id", it will be our primary key with the INTEGER data type, so check "Not Null" and "Autoincrement" checkboxes. The "Primary Key" field will be selected automatically – this is what we need, click "OK".

The next column of the table will contain the name of the book, for this you can use the variable length string – VARCHAR.

The field is called "title", we set the maximum length for it of 100 characters. Also, adding a book without a name does not make sense, so we set the "Not Null" restriction by enabling the corresponding checkbox.

In addition, check the option "Primary Key", and as a result, MySQL will create an index for this column. Thanks to this, for example, queries with the WHERE title = “…” expression will be executed much faster when you will be working with a table containing a large number of records.

Another thing worth paying attention to is the "Collation" field. Since we have to work with strings in utf8 encoding, we specify the utf8_general_ci collation, since in MySQL this value is used by default for the utf8 character set.

Click "OK" to save the changes.

In the same way, we create three more columns: "author" and "image" of type VARCHAR(100) to store the name of the author of the book and the name of the file with the cover photo, and the "description" column of TEXT type for a brief description of the book.

After the structure is created, we need to press the "Compile" button to execute the commands for creating and saving the table in the database.

In the program the window in which we are working now has one more tab – "Data", here you can view and edit the contents of the current table.

At the moment, it is still empty, so we will continue to work, press "Insert Record" – this is a button with a "+" sign.

To start, you need to double-click on the desired field in the column and enter the data. In this case, the values of strings and numeric fields are entered directly in place, and for text fields a special dialog box is used, that facilitates and simplifies the input of text in the form of several lines.

You do not need to enter the "id" column, since its value will be generated automatically by MySQL engine.

Also, do not forget that the real data insertion will occur only after clicking the "Post Edit" button. So, let’s review the values of fields and, if everything is correct, click the button with "check" sign.

So, pretty quickly, you can pre-populate the database and add as many rows as you need for your application.

Working with SQL editor in CodeLobster

Our IDE uses two-panel windows to edit SQL queries. At the top of the window we can enter a query, and at the bottom we can immediately see its result.

Right-click on the table name and select "New SQL File".

In the opened window of the editor, we begin to enter the query, now you can use intelligent prompts not only on the SQL syntax, but also on the names of the tables.

By pressing Ctrl + Space in the process of composing the query, we also get an auto-complete window on the names of the columns in the current table. Of course, in order to these functions work correctly, a connection to the database must be established.

After we have entered the query, we press the "Execute" button and examine the results at the bottom of the window.

It is possible to save the created file in your project with the extension .sql, as well as the regular file with the source code and use it then at any time.

All the benefits of the SQL editor are also useful if you have to insert queries directly into PHP code.

For our example, we need to run a query to retrieve all the entries from the "books" table. In the image below, you can make sure that CodeLobster does an excellent job with the mixed code.

It is known that in the development process, the programmer is not limited to creating a database and populating tables.

After the database is designed and filled with data, you can transfer it to another host or save it for later use in other projects.

To start the process of exporting or moving the database, you need to right-click on the name of the database or table and select the "Export" item.

The Export Wizard starts, it has many options and therefore is flexible configured. With its help in several steps you can export to one SQL-file the structure or data contained in tables or them all together.

In the case of working with individual tables, their contents can be saved not only in the form of SQL, but also in CSV or TSV format, that is very convenient.

Now any other application can work with the exported records, for example, they can be easily analyzed and edited in Microsoft Word or OpenOffice.

So, let’s sum up

We have discussed in detail the use of the SQL manager in CodeLobster IDE.

By studying the article, you learned how to connect to the MySQL server, how to create tables, add entries and execute queries in the editor window or from PHP scripts.

And, finally, you can not be afraid of losing data, because you will be able to quickly backup any database or table with the help of the Wizard.

So, now you have got all the necessary tools for the full range of works on designing databases for your projects.