Database

MySql | Show only columns as rows which have specific value

Author - Ishpreet Kaur
|
0 Comments
|

Sometimes, we want to show only those columns which have specific value. Let’s assume we have following table :

In the above table, value of 1 in a column represents that corresponding service is provided by organization and 0 represents service is not provided by the organization.

In the above Read more…

SQL Transaction in Cakephp 3

Author - Parminder Kaur
|
0 Comments
|

SQL transaction in Cakephp 3 is a series of statements working in a logical order to handle database errors and ensure data integrity. There are four main methods being used in Sql transaction in Cakephp 3.

1. Initialize the statement using begin() method.

2. Execute the statement which Read more…

Azure | Consumer Web application to crerate copy of SQL Database

Author - Harleen Kaur
|
0 Comments
|

Azure provides various rest apis to perform different actions and operations that are possible in the azure server like to create web applications, databases and many more. User can design their own simplified UI to perform actions, instead of logging into Azure and perform all the steps one by one Read more…

MySql | Display Latest entries of two columns of two rows into single row

Author - Ishpreet Kaur
|
0 Comments
|

Suppose we have two tables : articles and article_comments

CREATE TABLE articles(
`id` INT( 11 ) NOT NULL AUTO_INCREMENT ,
`title` VARCHAR( 200 ) NOT NULL ,
`content` VARCHAR( 500 ) NOT NULL ,
`author_name` VARCHAR( 20 ) DEFAULT 1 NOT NULL ,
`author_email` VARCHAR( 50 ) NOT NULL ,
`created_date` TIMESTAMP NOT NULL DEFAULT ‘0000-00-00 00:00:00 Read more…

MySQL | How to convert row values into column names

Author - Anjana
|
0 Comments
|

Sometimes requirement arises to display a field value as column name in output and adjacent values in the same row below it as its values.

Suppose I have a table “students” with marks of each student in different subjects. Each student may not appear in every subject:

students

We want Read more…

Download CSV of table Records using OUTFILE sql command and PHP

Author - Deepika
|
0 Comments
|

Create and Download CSV of table Records using OUTFILE Sql command and PHP

1. Create temporary CSV file using OUTFILE SQL command: OUTFILE command is used to write your sql command output to the specified location. You can write the output to some files using OUTFILE when required Read more…

Moodle | SQL report example in Configurable Reports plugin

Author - Harleen Kaur
|
0 Comments
|

SQL Report in ‘Configurable Reports’ plugin provides us an easy UI to write SQL Query and draw results immediately without involving any code complications.

Suppose I want to generate my custom grades report based on some specific activities from course – Let’s say these are PreTest 1, Pretest 2 and Read more…

Moodle: Performing search through wildcard queries with ‘Like’ operator

Author - Harleen Kaur
|
0 Comments
|

‘Like’ operator of SQL can be used in the following ways in Moodle:

Using expression $DB->sql_like:
$grade_item_test = $DB->get_records_sql ( ‘SELECT * FROM {grade_items} WHERE
‘.$DB->sql_like(‘idnumber’, ‘:idnum’).’ AND courseid=:cid order by itemmodule’,
array (‘idnum’=>’test’,’cid’=> $courseid) );
Using like operator:
$grade_item_test = $DB-&gt Read more…

Postgres | JSONB data type field in PostgreSQL(9.4) and using it in CakePHP

Author - Kailash
|
0 Comments
|

JSONB (JSON Blob) datatype in Postgres is used to save JSON data. We can search on JSONB type of column after creating an index on it.

For example:

Usage in PostgreSQL:

1. Create table and index
CREATE TABLE tests
(
id integer NOT NULL,
email citext NOT NULL,
address jsonb NOT Read more…

MySQL Access Denied due to wrong column name

Author - Webner
|
0 Comments
|

MySQL access Denied for user while Username and password is correct.

I got this error when I named one of my table columns as “order” which is also a mysql keyword. When tried to rename it, I got the following error on execution.

ERROR 1045 (28000): Access denied Read more…

How to create AWS OpsWorks Stack

Author - Webner

Purpose is to automate most of recurring processes like creating user, installing application (apache, apache-tomcat, mysql-server …etc), and modifying security file to hardening Linux machines in AWS environment.

1. Click AWS OpsWorks option from AWS console panel:

2. Now click on Add Stack to create a new Stack Read more…

Configuring AWS WAF (Web Application Firewall)

Author - Navneet Kashyap
|
0 Comments
|

AWS WAF web application firewall service is built to protect cloud apps from web attacks like DDoS attacks, SQL injections, Cross site scripting.

Below are the steps involved in configure AWS WAF security:

Step.1 Open CloudFormation and click on create new Stack.

Step.2 Select the option (Specify an Read more…

Couple of different ways to rename table name through UI in phpmyadmin

Author - Parminder Kaur
|
0 Comments
|

First method:

1. Click on the database (from list of databases on the phpMyAdmin home screen) in which you want to rename table name.
2. You will see a list of tables on click of the database.
3. Select the table and there is a dropdown below the tables with Read more…

What is PDO? Why PDO should be used in PHP?

Author - Ishpreet Kaur
|
0 Comments
|

PDO stands for PHP Data Objects. PDO is a consistent way to access databases.
Today, mostly websites or desktop based applications store their data in databases. Therefore, PHP has also provided the way to connect with databases.

PHP supports following ways to connect to the database:

1. Mysql Read more…

Timestamp issue while upgrading to MySQL 5.7

Author - Rita
|
0 Comments
|

Problem: In our application, we have created the session token to provide secure access. We were using current system time stamp in our token as one of the key component. But after upgrading MySQL 5.5 to MySQL 5.7, we were getting random results. Most of the Read more…

Postgresql | How to save default timestamp in unix format instead of yyyy-dd-mm hh:mm:ss

Author - Deepika
|
0 Comments
|

In Postgresql we can save the default timestamp with now() method:

CREATE TABLE test
(
id serial NOT NULL,
name character varying(255),
last_modified_time timestamp without time zone DEFAULT now()
)

This will save the date timestamp in this format:

If we want to use the last_modified_time in code we usually convert Read more…

MySQL | Enable/Disable Auto Commit

Author - Pawandeep
|
0 Comments
|

How to enable or disable autocommit of changes in MySQL using MySQL workbench?

Go to Edit->Preferences->SQL Editor->SQL Execution and check your current settings:

Also commit/rollback can be done with the buttons on SQL tab Read more…

AWS | Upgrading mysql version

Author - Navneet Kashyap
|
0 Comments
|

AWS: Upgrading MySQL version

Below are few step to upgrade Mysql version very easily on AWS-RDS:

1. Log on to AWS console.

2. Open AWS RDS from Database section:

3. Click on Instances:

4. Select the Database Instance which you want to upgrade:

5. Right Click on Read more…

Taking Database Backup And Moving that backup from one server to another using PHP

Author - Deepika
|
0 Comments
|

While working on two servers, we can take the backup of database on one server using mysqldump command in php.

Example :
exec(‘mysqldump –host ‘.$dbhost.’ –user ‘.$dbuser.’ –password=’.$dbpass.” “. $dbname.’ > ‘.$dbname.’.sql’);

After creating dump of database, user can move the dump database file from one server to another Read more…

Scaffolding in CakePHP

Author - Ishpreet Kaur

Scaffolding in CakePHP

Application scaffolding in CakePHP is a technique that allows the developer to define and create a basic application that can create, retrieve, update and delete objects. Basically CRUD (create, Read, Update, Delete) data operations are common in most of the applications.

Scaffolding basically provides temporary structure or Read more…

Send your message