Monthly Archives: September 2015

  1. Add Magento Admin User Using MySQL Script

    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.

    1. 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.
    2. Set your password. At least 8 characters in length and at least one digit.
    3. Firstname: Enter admin’s first name.
    4. Lastname: Enter admin’s last name.
    5. Enter email of admin user.
    6. Enter username where ‘myuser’ is set. Notice ‘myuser’ shows up in two places.
    7. Enter Firstname again. This is more symbolic to label the rule.
    MySQL Script for Adding Magento Admin User
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    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;
    Read more »
  2. CRUD operation in PhoneGap application using SQLite Database

    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.

    1
    2
    3
    4
    5
    6
    7
    8
    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.

    1
    2
    //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:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    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:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    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:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    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:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    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.

    Read more »
Copyright © 2016 Samdoit Infotech. All rights reserved.