Monday, October 15, 2012

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)
______________________________



Wednesday, September 19, 2012

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);
                }
});


Thursday, August 30, 2012

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.



Friday, August 24, 2012

How to filter & escape data from Injection attacks in PHP!

Ask any security expert! He will say you should always filter POST and GET data by escaping them before insertion into the database. In that way your scripts can be safe from SQL injection attacks.
Many php programmers are so lazy and just directly insert the POST data without filtering it like


mysql_query("INSERT into `users` (`name`,`email`) VALUES ('$_POST[name]','$_POST[email]')");

which is truly a bad example of not checking input data. Detect and protect important data from fraudulent access by having data security software.

A very good way to clean user input is using mysql_real_escape_function() which is a good way to protect from SQL injection attacks. You can use the function like this.



<?$name = mysql_real_escape_string($_POST['name']); ?>
This way you have to filter each and every POST variable. Imagine you have a form having hundreds of POST variable and how do you filter such data??

I was after a few lines of code where the server would automatically escape/filter POST data before inserting into database. It turns out that mysql_add_slashes() does the job but it causes more problems than anything and it is not advisable to use this function and it has been discontinued since PHP 6.0
Below is the nice little function that would filter/clean all user input and offers protection from
1. MySQL Injection attacks by escaping data.
2. Protection from XSS attacks through script tags.










function filter($data) {
    $data = trim(htmlentities(strip_tags($data)));
    if (get_magic_quotes_gpc())
        $data = stripslashes($data);
    $data = mysql_real_escape_string($data);
    return $data;
}
and to finally filter all POST variables in the form submitted, you have to loop through the array

foreach($_POST as $key => $value) {
    $mydata[$key] = filter($value);
}

and then finally you can use in the filtered array in your mysql statements.










mysql_query("INSERT into `users` (`name`,`email`) VALUES 
('$mydata[name]','$mydata[email]')");

all POST or GET variables in one go! Run the above code and see how this filters the user input data submitted from a form.

Error occured during my work

UPDATE command denied to user ''@'localhost' for table 'users'

Reason is the dabtaase connection not done properly or database not connected from php to execute the mysql queries on the given table.