Category: DataBase

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

I think you already know that the Unix Timestamp is not Human readable values it is just set of Integer numbers to the user when you show it on the screen.

Because of this reason we need to convert it before present it to the user.

If you do not convert the value while selecting rows from the Database Table then you are doing this programmatically after SELECT.

In the PHP you can use the date() function for converting.

$timestamp = 1476956996;
echo "date time : ".date('d-M-Y H:i:s a',$timestamp);

This gives the following output –

date time : 20-Oct-2016 11:49:56 am

DataBase

The phpMyAdmin allows us the number of ways to export the Table data one of the ways is CSV(Comma Separated Value).

There are two ways to create a CSV file with PHP –

  • By comma separated string, or
  • using fputcsv() method

I am using the fputcsv() method (writes an array to the file line by line) in the demonstration, where I am listing MySQL table data in the Tabular format and a button to export data.

When the export button gets clicked then creating a new CSV file and downloading it.

http://makitweb.com/how-to-export-mysql-table-data-as-csv-file-in-php/

DataBase PHP