Category: DataBase

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

Pagination is a technique to break and display the large list of content in the smaller parts. This reduces your page load time and it is user-friendly.

There are multiple types of pagination available some are –

In this tutorial, I show How you can add numeric pagination to your web page.

How to make PHP numeric pagination

DataBase PHP

In this tutorial, I show How you can concatenate multiple columns in MySQL.

You can simply do this programmatically by separately select fields from MySQL Table and store their values in the single variable after concat their values.

But you can make the above process little simpler by concatenating the values while selecting rows from DataBase Table.

Let’s take a simple example –

You have two columns – firstname, lastname within your DataBase Table you want to show both the columns values in a single string form. In case you can MySQL functions to combine the values of the columns.

There are two functions for doing this –

  • CONCAT
  • CONCAT_WS

Both functions work similar but have little difference.

How to concatenate multiple columns in MySQL

DataBase