Monthly Archives: September 2015

Add Magento Admin User Using MySQL Script

Posted: September 15, 2015

Categories: News , Magento Tutorial , Wordpress , vTiger CRM , vTiger CRM Customer Portal , Premium Plugin , Git , Laravel , Mysql , Ionic Framework

This script quickly adds a Magento Admin user directly into the database. It is possible to run this script from the command line or by copying and pasting into phpMyAdmin. Just make sure to edit the following fields with your personalized data and import. Most of these fields are trivial, I’m just listing them so you don’t miss anything.

Set the salt portion of your password. You’ll rarely need to change this. If you do, just use two lower case letters of your choice.
Set your password. At least 8 characters in length and at least one digit.
Firstname: Enter admin’s first name.
Lastname: Enter admin’s last name.
Enter email of admin user.
Enter username where ‘myuser’ is set. Notice ‘myuser’ shows up in two places.
Enter Firstname again. This is more symbolic to label the rule.

LOCK TABLES `admin_role` WRITE , `admin_user` WRITE;

SET @SALT = "rp";
SET @PASS = CONCAT(MD5(CONCAT( @SALT , "password") ), CONCAT(":", @SALT ));
SELECT @EXTRA := MAX(extra) FROM admin_user WHERE extra IS NOT NULL;

INSERT INTO `admin_user` (firstname,lastname,email,username,password,created,lognum,reload_acl_flag,is_active,extra,rp_token_created_at) 
VALUES ('Firstname','Lastname','email@example.com','myuser',@PASS,NOW(),0,0,1,@EXTRA,NOW());

INSERT INTO `admin_role` (parent_id,tree_level,sort_order,role_type,user_id,role_name) 
VALUES (1,2,0,'U',(SELECT user_id FROM admin_user WHERE username = 'myuser'),'Firstname');

UNLOCK TABLES;

LOCK TABLES`admin_role`WRITE,`admin_user`WRITE;

SET@SALT="rp";

SET@PASS=CONCAT(MD5(CONCAT(@SALT,"password")),CONCAT(":",@SALT));

SELECT@EXTRA:=MAX(extra)FROMadmin_userWHEREextraISNOT NULL;

INSERTINTO`admin_user`(firstname,lastname,email,username,password,created,lognum,reload_acl_flag,is_active,extra,rp_token_created_at)

VALUES('Firstname','Lastname','email@example.com','myuser',@PASS,NOW(),0,0,1,@EXTRA,NOW());

INSERTINTO`admin_role`(parent_id,tree_level,sort_order,role_type,user_id,role_name)

VALUES(1,2,0,'U',(SELECTuser_idFROMadmin_userWHEREusername='myuser'),'Firstname');

UNLOCK TABLES;

CRUD operation in PhoneGap application using SQLite Database

Posted: September 05, 2015

Categories: News , Magento Tutorial , Wordpress , vTiger CRM , vTiger CRM Customer Portal , Premium Plugin , Git , Laravel , Mysql , Ionic Framework

Introduction:
Few days ago I received a comment from one of the reader of my blog to show an example on CRUD (Create, Read, Update and Delete) with the database. So in this post we will explore the use of CRUD operation using SQLite database in PhoneGap application.

Description:
CRUD can be described as a process, which helps the user to add, view, search and modify the information inside the database.

Here are the basic steps which demonstrates CRUD operation.

Create or add new entries
Read, retrieve, search or view existing entries
Update, modify or edit existing entries
Delete, deactivate or destroy existing entries

Before doing any operation on database let’s initialize the database first, either using HTML5 local database concept or using SQLite plugin.

Let’s add the following javascript to initilze the database.

var databaseName =  "DummyDB";
var databaseVersion = "1.0";
var databaseDisplayName = "DummyDatabase";
var databaseSize =  2 * 1024 * 1024;
var myDB = "";
 
//Accessing with HTML5 local database
myDB = window.openDatabase(databaseName, databaseVersion , databaseDisplayName, databaseSize);

window.openDatabase helps to return the new database object. This method will create a the new SQLite database and also can be use for other data manipulation.

//Using SQLite plugin
myDB = window.sqlitePlugin.openDatabase({name : databaseName});

Above method will create the SQLite datebase with unlimited storage capacity. To integrate the SQLite plugin we can follow this post.

Now lets focus on the basic 4 steps of CRUD operation by using the database object already created by above step.

CREATE: Initializing or creating the table. We normally use two keywords CREATE and INSERT for this operation.

Example:

function CreateBusinessTable() {
            myDB.transaction(function(transaction) {
                transaction.executeSql('CREATE ' +
                           'TABLE IF NOT EXISTS ' +
                           'Business_Table (id integer primary key, business_id integer, business_name text)', [],
                    function(tx, result) {
                        console.log("Business table created successfully.");
                    }, 
                    function(error) {
                          console.log("Error occurred while creating the table.");
                    });
                });
}
function InsertBusinessData(){
        myDB.transaction(function(transaction) {
            // Define insert query
            var executeQuery = "INSERT INTO " +
                                "Business_Table" + 
                                "(business_id, business_name) "+
                                "VALUES(?,?)";
            Helper.log(executeQuery);              
            transaction.executeSql(executeQuery, ['1234', 'Samdoit']
                , function(tx, result) {   // On success
                     console.log('Business data inserted successfully.');
                },
                function(error){     // On error                               
                     console.log('Error occurred while inserting business data.'); 
                });
        });
}

READ: Use to get, search or retrieve entries from a table inside the database.

Example:

function GetBusinessData() {
           myDB.transaction(function(transaction) {
                transaction.executeSql("SELECT business_id, business_name FROM Business_Table                                                   WHERE business_id =?", ['1234'],
                function(tx, result) {
                      var dataLength = result.rows.length;
                       console.log(dataLength);
                      if(dataLength  > 0){
                           var businessName = result.rows.item(0).business_name;
            console.log(businessName);
                       }else{
                             console.log("No business found having this business id.); 
                       }
                  }, 
                  function(error) {
                        console.log("Error occurred while getting the data.);
                  });
          }); 
}

UPDATE: This operation is used to update or modify the entries in the table.

Example:

function UpdateBusinssTable(){
    myDB.transaction(
                    function(transaction) {
                    // Define update query
                    var executeQuery = "UPDATE " +
                                       "Business_Table " +
                                       "SET business_name = ?  WHERE  business_id =?"; 
                    transaction.executeSql(executeQuery, ['Samdoit Infotech', '1234']
                        , function(tx, result) {   // On success
                             console.log('Business updated successfully.');
                        },
                        function(error){     // On error                               
                            console.log('Error occurred while updating the business.'); 
                        });
           });
}

DELETE: This is the last and final method of CRUD operation which is used to delete/drop the entries in table.

Example:

function DeleteBusinessTable(){
            myDB.transaction(
                function(transaction) {
                // Define delete query
                var executeQuery = "DELETE FROM Business_Table";
                transaction.executeSql(executeQuery, []
                    , function(tx, result) {   // On success
                         console.log('All business data deleted successfully.');
                    },
                    function(error){     // On error                               
                         console.log('Error occurred while deleting the business data.'); 
                    });
            });
}
 
function DropBusinessTable(){
            myDB.transaction(
                function(transaction) {
                // Define delete query
                var executeQuery = "DROP TABLE  IF EXISTS Business_Table";
                transaction.executeSql(executeQuery, []
                    , function(tx, result) {   // On success
                         console.log('Table deleted successfully.');
                    },
                    function(error){     // On error                               
                         console.log('Error occurred while droping the table.'); 
                    });
            });
}

Summary:
I hope the above content will give some idea to start the basic CRUD operation with database and will make the life easy.