Category: DataBase

Sometimes it requires to quickly create a clone of the MySQL database while working on the project either for the backup purpose, using it on a copy of the project or moving the project.

With phpMyAdmin, this process is easier.

In this tutorial, I am assuming that you already have a cPanel enabled web hosting server.

Create a copy of MySQL database using phpMyAdmin

DataBase How To

Elasticsearch is open-source and NoSQL based distributed search engine which is written in JAVA.

It is designed for high-performance searches on large data.

For data storing it uses indexes which is the collection of different types of document and properties.

Compares to the relational database is very fast.

  • A relational database consists of –
    Databases -> Tables -> Row/Columns
  • But the Elasticsearch consists of –
    Indices -> Types -> Documents with properties

To communicate with it requires REST APIs.

It is supported by various languages – JAVA, PHP, Python, JavaScript, Node.js and many others.

In this tutorial, I am using XAMPP for its installation.

How to install Elasticsearch on Windows System

DataBase How To

Within the project sometimes requires using multiple MySQL databases. It may be the existing database from other project or the new one.

To handle this with PHP requires to create separate connections for each database and use the connection accordingly while manipulating data in MySQL database.

Connect to multiple mysql database with PHP

DataBase PHP

From cPanel you cannot directly create or upload your database using PHPMyAdmin while making your website live. You need to follow some of the steps like – creating a database, user, etc.

The cPanel gives easy to use interface that makes easier to manage the database and make it available in the PHPMyAdmin.

In this tutorial, I am assuming that you already have a cPanel enabled web hosting server.

How to create and manage a MySQL Database in cPanel

DataBase How To

The Codeigniter allows us to perform the database action like – select, insert, update, and delete with the minimal script.

The $this->db->update() method is used for update existing record. It takes table name and an array or object as the parameter.

In the demonstration, I am listing the user’s list with the edit option. Show the update form on edit option click.

Update records in Database Table with CodeIgniter

DataBase PHP

Data storing is a basic requirement while creating an application.

It is possible to store data online but the app needs to be online whenever data processing is required.

For local data storage use SQLite database which is already embedded on the mobile platforms – Android, IOS, Windows, Blackberry, etc.

The Cordova plugin provides support to access SQLite database in the app.

In this tutorial, I am creating an Android app where use SQLite database to save and retrieve records. Deploy the application with PhoneGap Build.

Storing Data Locally in a PhoneGap App with SQLite

DataBase Phonegap

There is always the possibility that the users may not enter the values as we expected and the data is being saved on the Database table. E.g. unwanted whitespace or characters with the value.

You will see the issue when you check for duplicate records or sort the list.

MySQL have some string functions that you can use to eliminate the extra space or characters from the field.

Remove unwanted whitespace from the column – MySQL

DataBase

You can save your uploading images in the database table for later use e.g. display user profile or product image, create the image gallery, etc.

There are two ways of doing this –

  • Save path or name of an image
  • Encode image into base64 format

In this tutorial, I show you both of the methods for storing and retrieving an image from the database table.

Upload and store an image in the database with PHP

DataBase PHP

The data is not displayed in any particular order when you simply fetch it from MySQL table. For displaying data in some meaningful way ORDER BY clause is used.

This sorts your result on the basis of column name specified in the clause within the SELECT query.

It allows us to –

  • Specify ordering in single or multiple columns
  • Define sort results in ASC or DESC format.

How to use order by with multiple columns in MySQL

DataBase

There is the various approach of selecting the last insert id from MySQL table.

  • Select a single row from the table in descending order and store the id.
  • Select Maximum value.
  • The following query gives you next AUTO_INCREMENT value from selected table which you can use to get the last id.
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user'

If your currently AUTO_INCREMENT column last value is 8 and when you execute the above query on the Table this will return 9.

In PHP there is an inbuilt method which simply returns the last insert id according to the previous insert query.

Get last insert id from MySQL Table with PHP

DataBase PHP