Connecting multiple db using CodeIgniter framework:

In Applications/config folder open the database.php file. You will find the default database connection code. Which is need to to be replaced with your settings. Like database name, hostname, username, password, etc… And ‘$active_group’ variable is set with ‘default’ value. You can replace them all but only if you are sure about them.  The ‘Record set’ code would be like this,

       

       $active_group = 'default';

               $active_record = TRUE;

              

               $db['default']['hostname'] = 'localhost';

               $db['default']['username'] = 'root';

               $db['default']['password'] = '';

               $db['default']['database'] = 'feedback';

               $db['default']['dbdriver'] = 'mysql';

               $db['default']['dbprefix'] = '';

               $db['default']['pconnect'] = TRUE;

               $db['default']['db_debug'] = TRUE;

               $db['default']['cache_on'] = FALSE;

               $db['default']['cachedir'] = '';

               $db['default']['char_set'] = 'utf8';

               $db['default']['dbcollat'] = 'utf8_general_ci';

               $db['default']['swap_pre'] = '';

               $db['default']['autoinit'] = TRUE;

               $db['default']['stricton'] = FALSE;

          If you want to connect to multiple databases at a time, you can connect by Copying Record set and paste below the code and change the multi-dimensional array key value ‘default’ to ‘new_conn’ i.e

             $db['default']  ==>  $db['comments’]

And as well as the database values. Like hostname, dbname, username, pwd, …

Following is the new database connection array.

//================NEW COMMENTS DATABASE ==========

               $db['comments']['hostname'] = 'localhost';

               $db['comments']['username'] = 'root';

               $db['comments']['password'] = '';

               $db['comments']['database'] = 'comments';

               $db['comments']['dbdriver'] = 'mysql';

               $db['comments']['dbprefix'] = '';

               $db['comments']['pconnect'] = TRUE;

               $db['comments']['db_debug'] = TRUE;

               $db['comments']['cache_on'] = FALSE;

               $db['comments']['cachedir'] = '';

               $db['comments']['char_set'] = 'utf8';

               $db['comments']['dbcollat'] = 'utf8_general_ci';

               $db['comments']['swap_pre'] = '';

               $db['comments']['autoinit'] = TRUE;

               $db['comments']['stricton'] = FALSE;

Connection to new database:

By default the CodeIgniter connection statement is

          

          $this->load->database();

This code connects the default database which is set following variables,

        $active_group = 'default';

        $active_record = TRUE;

         For connecting to new database you need to specify the database name along with this default connection statement. And you have to set TRUE for $active_record variable, Following is the code snippet:

$a=$this->load->database('comments',TRUE);

         First database() method arguments is database connection array key value which will be set to ‘$active_group’ I.e comments which we already defined below the default database connection array. And second argument is TRUE/FALSE which will change ‘$active_record’ variable value.

This connection statement returns the connection results which we storing into a variable. So we can query the database with that variable. Following code is example query statements,

$res=$a->insert("comment ",$data);

$a->_error_message();

$a->distinct();

$a->select('name');

$query=$a->get("comment");

…

You may wish to connect to another new database then follow the same steps to achieve.

Connecting to Cloud Files (Rackspace) server

Download and install any of file transfer softwares like Filezilla, CyberDuck,etc. Following connection using FileZilla software.

• Goto File --> Site Manager
• Click the "New Site" button
• Under general tab enter required options
• Your Hostname:(ip address eg.127.0.0.2)
• Select the Protocol option as SFTP - SSH FTP
• Select Logon Type : Normal (You can save password)
• Enter Username and password for server

Then click the Connect button. For valid settings connection will be established.

Data Import / Exporting Cloud databases (Rackspace Databases) using MySQL Workbench(Version 5.2.44)

Following steps uses the MySQL Workbench software.

Download and Install the Workbench in your system.

Then,

1. Open the MySQL Workbench Software

2. Under "Server Administration" panel click the New Server Instance link. "Create New Server Instance Profile" window will open. 

Following step by step options to create the server administration instance.
 Under "Specify Host Machine" option. There are following 3 options 
   1. Localhost
   2. Remote Host
   3. Take Parameters from Existing Database Connection

Remote host connection: You can specify Hostname "Address" textbox under Remote Host option.
  -->  Enter the IP address or Hostname
  -->  And, Database Connection:
Enter the Connection Name, Method and parameters. Connection method should be Standard TCP/IP over SSH. So SSH Hostname, Username, Password  field need to filled. Along with MySQL Hostname, Username and password. MySQL Server Port is generally set as default value 3306. Then click next button. Skip and go to next step reading.

Take Parameters from Existing Database Connection: If you already created the database connection select the connection name option from the dropdown list in "Take Parameters from Existing Database Connection" option. Then Click Next button. Go to to next step.


3. Automatically testing the database connection with with given settings. Like, Opening Database Connection, Getting Server version, and Getting Server OS. Results the success or error message. If successfully tested Click Next button.

4. Under "Management and OS" option select type of remote management you want to use:
     Our cloud server configured with SSH login based management so i go with this option. Under "Operating System Selection" select your cloud server operating system from the "Operating system" drop down-list. Mine is Linux  and MySQL Installation Type is Ubuntu Linux (Vendor Package). Vendor Package is if you unsure of your version of OS. Then click next button.

5. SSH Configuration options need to give SSH Hostname and Username.Then click next button.

6."Testing Host Machine Settings" option shows the connection status with success or error options. If success, then click on next button.

7. Review settings popup will open, click on Continue. popup will close immediately.

8. Under "Create the Instance Profile" option default Server Instance Name will be shown. You can change with other name. Click on Finish button. Instance will be created and listed under Server Adminstration panel box.

• Click on your Database server instance name. Admin Task and Object Browser window will open.
• There are some categories like, MANAGEMENT, CONFIGURATION, SECURITY and DATA EXPORT / RESTORE.
• Under DATA EXPORT / RESTORE category Select the Data Export or Data Import / Restore.

Data Export : Select the schema to export and select the options like, Export to Self-Contained File and choose the location where to save the .sql dump file. Then check the other checkboxes which are your requirements. By clicking on "Start Export" option MySQL workbench will generate SQL File under given directory.

Data Import/Restore : Under "Import from Disk" tab, Select any one of two options 
1) Import from Dump Project Folder: All tables of database will be created as separate files.
2) Or, Import from Self-Contained File: Whole database is created as a individual .sql file.

Select the "Default Schema to be Imported" from dropdown list.

And then click "Start Import" button to finish importing operation.

Connecting Cloud DB (Rackspace Databases) with MySQL Workbench Software

To manage the cloud databases we need to MySQL Workbench (Following instructions using Version 5.2.44).

About MySQL Workbench in brief:

MySQL Workbench is a unified visual tool for database architects, developers, and DBAs. MySQL Workbench provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, and much more. MySQL Workbench is available on Windows, Linux and Mac OS.

• Download and Install the Workbench in your system,

Connect the databases by creating the new connection.

Connection Setup:

• Open the Software(MySQL Workbench).
• In Workbench central workspace window 3 type of connections
• SQL Development
• Data Modeling
• And Server Administration

To run SQL Queries, SQL scripts, edit data and manage database objects with SQL Development connection.

Creating and manipulating database models, Like, Create and manage models, forward and reverse engineer, compare and synchronize schemas, report with Data Modeling.

Server Administration settings like Import/export database, Configure your database server, setup user accounts, browse status variables and server logs can be achieved through Server Administration tools.

• To make SQL Development operations,
• Click on New Connection,
• "Setup New Connection window" will open, enter any connection name of your choice.
• Select the "Connection Method" as Standard TCP/IP over SSH.
• Set the Parameters under Parameters Tab. Following parameters are changed according to your Cloud db settings.
• SSH Hostname: 127.0.0.1:22 , SSH Username: root, SSH Password: pwd
• MySQL Hostname : localhost/59635be9cca24.rackspaceclouddb.com
• MySQL Server Port : 3306 (Default)
• Database Username: root and Password : pwd
• After proper connection settings SSH connection will be established and SQL Editor window will be opened. And it contains SCHEMAS listed with tree structure of databases and tables.

To execute the ALTER command and to update the table which is not having the primary key

1. Follow the steps to remove the "Safe Update" option in MySQL Workbench of Version 5.2.44.

• Open the Software(MySQL Workbench).
• Go to Edit --> Preferences.
• "Workbench Preferences" window will open. Select the "SQL Queries" Tab.
• Under "General" section, Uncheck the "Safe Updates". Forbid UPDATEs and DELETEs with no key in WHERE clause or no LIMIT clause. ... option.
• Close the software and open. So settings are apply to the next query execution.

2. Or Directly adding the following statement to beginning of the query execution.

           SET SQL_SAFE_UPDATES=0;

This is common error face by the beginners of MySQL Users / Workbench users while Truncating, Altering or updating the tables.

MySql Error : Create command denied to user in MySql or Rackspace / Cloud db

Error :
Create command denied to user in MySql or Rackspace /  Cloud db

Reasons:

• Not selected the database to use.
• Invalid database name specified.

Solutions:

1. Give the USE query to select the database before queries statements:

USE db_name;

CREATE, INSERT, UPDATE, DELETE statements.

2. Or Specify the database name with the table name. Like,

CREATE TABLE IF NOT EXISTS  `db_name`.`ci_sessions` ( ..... );

PHP Frameworks - Support

Rapid application development with PHP frameworks.

Following list of PHP Frameworks:

1. CodeIgniter 
2. CakePHP
3. Yii
4. Zend
5. Symfony

etc.

Please Refer the following link:

http://phpframeworks.com/

PHP - Cropping images based on lower dimensions (Width / Height)

Following code crop images baased on lower dimention (Width / Height)

Following is the funtion with image url,

     public function croppThis($target_url) {

$this->jpegImgCrop($target_url);

}

$target_url - is Name of image.

Crop image bases on less width / Height:

public function jpegImgCrop($target_url) {//support

$image = imagecreatefromjpeg($target_url);
$filename = $target_url;
  $width = imagesx($image);
  $height = imagesy($image);
  $image_type = imagetypes($image); //IMG_GIF | IMG_JPG | IMG_PNG | IMG_WBMP

  if($width==$height) {

    $thumb_width = $width;
    $thumb_height = $height;

} elseif($width<$height) {

  $thumb_width = $width;
  $thumb_height = $width;

} elseif($width>$height) {

$thumb_width = $height;
$thumb_height = $height;

} else {
  $thumb_width = 150;
  $thumb_height = 150;
}

  $original_aspect = $width / $height;
$thumb_aspect = $thumb_width / $thumb_height;

if ( $original_aspect >= $thumb_aspect ) {

 // If image is wider than thumbnail (in aspect ratio sense)
    $new_height = $thumb_height;
    $new_width = $width / ($height / $thumb_height);

  }
else {
  // If the thumbnail is wider than the image
  $new_width = $thumb_width;
    $new_height = $height / ($width / $thumb_width);
}

$thumb = imagecreatetruecolor( $thumb_width, $thumb_height );

// Resize and crop
imagecopyresampled($thumb,
  $image,
  0 - ($new_width - $thumb_width) / 2, // Center the image horizontally
  0 - ($new_height - $thumb_height) / 2, // Center the image vertically
  0, 0,
  $new_width, $new_height,
  $width, $height);
 
imagejpeg($thumb, $filename, 80);

}

Update new values for existing MySql fields without Overwriting

While updating the existing MySql table field value you may wish to preserve existing field value or my prefer to update without overwriting with new value. You may think of writing PHP code for getting existing value and append and store it to database with new value.

But there is a MySql concat() function. So you can append the your new value dynamically, function having following  pattern:

concat("field_name","new_value");

E.g.

mysql_query("UPDATE `table_name` SET `field_name`=concat(`field_name`, ", new_value") WHERE `id_is`='3450';

Result:

______________________________
id_is    ||  field_name
______________________________

22       ||  (value+new_value)
______________________________





Display Mysql records in reverse order with descending order.

Category: MySql, PHP, CodeIgniter and Core PHP

Description:  

                       While working with social media website I came across the scenario of displaying comments in reverse order. Such as, display the most recent comments at the end.

Example:

 

 Development view: 

                     MySql displays records either in ascending or descending order. Therefore, results would display,

• the most recent record in bigining or
• the oldest record in biginning.

The list will be in ascending or descending order as represented the following,

But our requirement is to display records in reverse order.  such as,

For Core PHP users:

    $out='';

    $sql = mysql_query('SELECT `id` FROM `tbl_name` ORDER BY `id` DESC LIMIT 0,3');
    
    if(mysql_num_rows($sql)!=0) {
    
            //REVERSE THE RECORD LIST
    
            while($out=mysql_fetch_array($sql))  {
                            
                      $arr_list = (array) $sql;  //convert object arrays into arrays
    
                      // consider only 'result_array' element in an array :
                      $result_array=($arr_list['result_array']); 
    
                      $reved_rows_arry = array_reverse($result_array);  //Reverse the array elements
                    
            }
    
           //DISPLAY THE COMMENTS IN REVERSE
    
           foreach($reved_rows_arry as $row) {// Display the resulting array
    
                      $comment_text = mysql_query('SELECT * FROM `tbl_name` WHERE `id`='.$row['id']);
    
                       $out=mysql_fetch_row($comment_text);
    
                                   
    
           }
    
    }
    else {
    $out .="No records foind!";
    }
    
    echo $out;

And, following code involves PHP - MySql in CodeIgniter framework:

 

CommentsContoller.php

public function disp_comments() {

                $this->load->model("commentsmodel");

                $this->commentsmodel->dispcomments();

}

This is the controller file with disp_comments() controller, which loads the Comments model and calls the dispcomments() method of the model.

CommentsModel.php

public function dispcomments() {

                $outout='';

                $comment = $this->db->query('SELECT `content_id` FROM `tbl_comment` ORDER BY `comment_id` DESC LIMIT 0,3');      

if($comment->num_rows()!=0) {

                                foreach($comment->result_array() as $out)  {

                                                //convert object arrays into arrays

                                                $comment_1 = (array) $comment;

//consider only 'result_array’ element  in an array

                                                $comment_array=($comment_1['result_array']);

                                                //Reverse the array elements

$comment_rev = array_reverse($comment_array);

                                }

                                foreach($comment_rev as $row) { // Display the resulting array

                                                $comment_text = $this->db->query('SELECT * FROM `tbl_comment_content` WHERE `content_id`='.$row['content_id']);

                                                $text=$comment_text->row();

                                                $outout.='<!-- (Commentor Image) (Comment ) -->';

                                }

} else $outout.="No records foind!";

echo $outout;

}

CommentsViewPage.php

$.ajax({

type: "POST",

url: "<?php echo site_url().'/addpostcomment/disp_comments'; ?>",

                success: function(return_data,textStatus){

                                $(".comments_output").html(return_data);

                }

});

System Upgradation from 32bit to 64 bit

System Upgradation from 32bit to 64 bit:

My MD was facing the problem of slow system execution. So he discussed with us and we all planned and thought to upgrade the system.

Following things we considered:

1. Upgrade the system RAM.
2. Change 32bit System to 64bit.
3. Change the OS.
4. Install good antivirus.
5. Scan existing storage drives.

 Upgrade the system RAM

           Existing system was having the RAM capacity of 2GB so we added 2GB more. Basically RAM increases the execution speed and loading time.

Change 32bit System to 64bit

There are two benefits by increase the bit capacity:

1. More bits means that data can be processed in larger chunks which also means more accurately.
2. More bits means our system can point to or address a larger number of locations in physical memory.

 32-bit systems were once desired because they could address (point to) 4 Gigabytes (GB) of memory in one go. Some modern applications require more than 4 GB of memory to complete their tasks so 64-bit systems are now becoming more attractive because they can potentially address up to 4 billion times that many locations.

Changing the OS

             Our existing operating system was Microsoft Windows XP professional v2002 with service pack-3. We decided the new Microsoft Windows 10

Install good antivirus

              We have installed Kaspersky antivirus to all systems for security. Our aim is to filter virus what ever data goes in and goes out of system.

Scan existing storage drives

           While taking this decision we taken backup of all important data to our external hard disk or separate partition. Those data might be having virus affected files. So first we have to scan thoroughly with installed antivirus.