Coding Platform

Migrate MS Access database (.mdb) to MySQL (.sql) database using MySQL ODBC Connector

2017-10-21T15:05:00.003+05:30

Hello coders, this blog post provides information about conversion of MS Access Database to MySQL database. First of all, here is a brief overview of the need to migrate .mdb file to .sql file.

- Microsoft Access is used as Database Management System (DBMS) for Windows Desktop Applications. Now, suppose that you have created a similar application on Web and you want to use same database as used in Windows Application.
- You can not use the database created in Microsoft Access on Web Server as it is. You have the following options for using the same database for your Web Application.

Using Microsoft SharePoint – MS Access can be used on SharePoint to build Web Database Applications.
Migrating MS Access database to Web based database, i.e, MySQL, MS SQL Server or others. This post discusses about this method.

If the conversion of .mdb file can be done to .sql file, then the .sql can be imported to any Web based database. There are online tools available for this. But this blog post discusses about migration without using online available tools.
Now, we will explain how to migrate .mdb file (MS Access database) to .sql file.

For this migration, we will export tables from MS Access to MySQL database using MySQL ODBC connector. There is a full documentation on MySQL ODBC connector on MySQL developer website at the following URL:
https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-introduction.html.

MySQL ODBC connector can be downloaded from the following URL:
https://dev.mysql.com/downloads/connector/odbc/5.3.html. Here, you can download it according to your operating system.
After installing MySQL ODBC connector, it needs to be configured to create a Data Source Name (DSN). The documentation to create DSN using ODBC Data Source Administrator on Windows can be found at https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-dsn-windows-5-2.html.
This Data Source Name (DSN) will be used during migration of MS Access tables to MySQL database. During creation of DSN, there is a Connection Parameters Dialog box(as shown below) where Database connection parameters like Database Host, Database User, Database Password and Database Name need to be filled. During migration, this database connection will be used.
The documentation for exporting MS Access database to MySQL database can be found at https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-examples-tools-with-access-export.html.

Brief Overview of Export Process:

Create a new database in MySQL. Its connection details are filled in Connection Parameters Dialog box during creation of DSN (as described above) as tables in MS Access will be exported in this database.
Open MS Access .mdb file to be exported. In the left panel, there are table names and when a table name is clicked, the table data is shown in the right panel. Right click on the table you want to export, and in the menu (as shown below) that appears, choose Export ->ODBC Database.
The Export dialog box appears (as shown below). Enter the desired name for the table after its import into the MySQL server, and click OK.
The Select Data Source dialog box (as shown below) appears. It lists the defined data sources for any ODBC drivers installed on your computer. Click either the File Data Source or Machine Data Source tab, and then double-click the Connector/ODBC DSN to which you want to export your table (DSN created above).
A dialog box appears with a success message if the export is successful. In the dialog box, you can choose to save the export steps for easy repetitions in the future.
If you see the error message (as shown below) instead when you try to export to the Connector/ODBC DSN, it means you did not choose the Database to connect to when you defined in to the DSN. Reconfigure the DSN and specify the Database.

After the tables from MS Access are exported to MySQL database, you can export the MySQL database dump as .sql file which can be imported to other Web based DBMS which you are using.

References:

Sorting a multidimensional associative array by key's value using usort in PHP

2016-06-29T18:45:00.000+05:30

This post explains how a multidimensional associative array in PHP can be sorted by value of one of the associative keys. It can also be described as sorting an array of arrays by the keys of sub-arrays. Let us understand the situation with an example. Suppose there is a PHP array shown in the PHP code below:

<?php
$multi_array = array(array('name' => 'Orange', 'code' => '18plkp', 'value' => 15),
                     array('name' => 'Apple', 'code' => '45jklp', 'value' => 49),
                     array('name' => 'Guava', 'code' => '48pyhp', 'value' => 169)
                    );
var_dump($multi_array);
?>

The output of this code is as follows:

array (size=3)
  0 => 
    array (size=3)
      'name' => string 'Orange' (length=6)
      'code' => string '18plkp' (length=6)
      'value' => int 15
  1 => 
    array (size=3)
      'name' => string 'Apple' (length=5)
      'code' => string '45jklp' (length=6)
      'value' => int 49
  2 => 
    array (size=3)
      'name' => string 'Guava' (length=5)
      'code' => string '48pyhp' (length=6)
      'value' => int 169

This is an array which has 3 elements. These 3 elements are associative arrays. So, if you want to sort the array $multi_array with the value of associative key 'name', then how will you do it in PHP?
Read below for the solution.
So, after sorting the array by key 'name' in ascending order, the array will look like as follows:

array (size=3)
  0 => 
    array (size=3)
      'name' => string 'Apple' (length=5)
      'code' => string '45jklp' (length=6)
      'value' => int 49
  1 => 
    array (size=3)
      'name' => string 'Guava' (length=5)
      'code' => string '48pyhp' (length=6)
      'value' => int 169
  2 => 
    array (size=3)
      'name' => string 'Orange' (length=6)
      'code' => string '18plkp' (length=6)
      'value' => int 15

For achieving the above output after sorting, we will use a PHP function "usort" as follows:

usort($multi_array, function($a, $b){
    return strnatcmp($a['name'], $b['name']);
});

So, here is the full code and its explanation.

Code:

<?php
$multi_array = array(array('name' => 'Orange', 'code' => '18plkp', 'value' => 15),
                     array('name' => 'Apple', 'code' => '45jklp', 'value' => 49),
                     array('name' => 'Guava', 'code' => '48pyhp', 'value' => 169)
                    );
var_dump($multi_array);
usort($multi_array, function($a, $b){
    return strnatcmp($a['name'], $b['name']);
});
var_dump($multi_array);
?>

Explanation:
1. $multi_array is a multidimensional array which has 3 associative arrays as its elements.
2. The first var_dump($multi_array) dumps the array on the console.
3. Here is the main part where sorting is done. The usort function has the following documentation:
usort — Sort an array by values using a user-defined comparison function
The first argument is the array variable that is to be sorted. The second argument is the user-supplied comparison function. In the comparison function, we have used "strnatcmp" to compare the array elements by key "name". If the sorting needs to be done on another key, then that key can be used instead of "name". The strnatcmp compares alphanumeric strings.
4. The second var_dump($multi_array) outputs the sorted array to the console.

Note: The second argument passed to usort in the above example is an anonymous function which was introduced in PHP 5.3. So, for PHP < 5.3, we have to define a callback function and then pass the function name as the second argument to usort as shown below:

<?php
function cmp($a, $b)

{
    return strnatcmp($a['name'], $b['name']);
}

$multi_array = array(array('name' => 'Orange', 'code' => '18plkp', 'value' => 15),
                     array('name' => 'Apple', 'code' => '45jklp', 'value' => 49),
                     array('name' => 'Guava', 'code' => '48pyhp', 'value' => 169)
                    );
var_dump($multi_array);
usort($multi_array, "cmp");
var_dump($multi_array);
?>

Hope, you will get some useful information from this post.
Thanks.

How to make a Google Map using javascript?

2013-10-31T03:46:00.003+05:30

Today, it is common to use google maps to locate the places. There are mobile applications used in mobiles. On websites, the places are located on google maps. Those google maps are made using Google Maps API provided by Google. Here, we will see how to make a simple google map for a particular location using HTML, javascript and Google Maps API.

Here, the HTML code goes below.

<html>
<head>
<script type="text/javascript" src="http://maps.googleapis.com/maps/api/js?sensor=false"></script>
<script type = "text/javascript">
var infoWindow = new google.maps.InfoWindow;
function load() {
      var map = new google.maps.Map(document.getElementById("googlemap"), {
        center: new google.maps.LatLng(37.40557, -122.07747),
        zoom: 13,
        mapTypeId: "roadmap"
      });
   
   var point = new google.maps.LatLng(37.40557, -122.07747);
          var html = "<b>Google Inc.</b> <br/>Mountain View, CA 94043";
          var marker = new google.maps.Marker({
            map: map,
            position: point
          });
          bindInfoWindow(marker, map, infoWindow, html);
}
function bindInfoWindow(marker, map, infoWindow, html) {
      google.maps.event.addListener(marker, "click", function() {
        infoWindow.setContent(html);
        infoWindow.open(map, marker);
      });
    }
</script>
</head>
<body onload="load()">
<div align="center">
<table border="1" width="80%" height="80%">
<tr>
<td id="googlemap" width="80%" height="80%"></td>
</tr>
</table>
</div>
</body>
</html>

Important Points:

Here, the location shown is the Google Inc. with postal code as 94043 and latitude and longitude as 37.40557 and -122.07747 respectively.
You may customize the map using the tutorials on the developers Google Maps API at https://developers.google.com/maps.

URL data extraction like facebook - Simple script in PHP

2013-09-15T21:01:00.001+05:30

When you enter a url, then the main data like title, logo, etc. of the page to which the url takes you, are shown. This is called URL extraction. The best example you can see is facebook. Go to facebook homepage, enter the url in its status box. As soon as you type the url ,it shows you the extracted contents of the page corresponding to the url typed.

Let's write a simple script in PHP to show URL extraction. Mainly it will be css, javascript and html. We will use only file_get_contents(url) function of PHP.

Here the code goes...............

Put this code in a text file and save as .html.

<?xml version="1.0" encoding="iso-8859-1"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
<title>URL Extraction like facebook</title>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function()
{
 $("#linkbox").keyup(function()
 {
 var content=$(this).val();
 var regex = /(\b(https?|ftp|file):\/\/[-A-Z0-9+&@#\/%?=~_|!:,.;]*[-A-Z0-9+&@#\/%=~_|])/ig;
 var link= content.match(regex);
 if(link!=null && link.length>0)
 {
  $("#databox").slideDown('show');
  $("#databox").html("<img src='link_image.gif'>");
  $.get("getdata.php?link="+link,function(data)
  {
   if(data != null & data.length > 0)
   {
    var title=(/<title>(.*?)<\/title>/m).exec(data)[1];
    var logo=(/src='(.*?)'/m).exec(data)[1];
    if(logo && title)
    $("#databox").html("<img src='"+logo+"' class='img'/><div><b>"+title+"</b><br/>"+link);
   }
  });
 }
 return false;
 });
});
</script>
<style>
#linkbox
{
width:500px; height:100px;
border:solid 2px #bbbbbb;
font-size:14px;
margin-bottom:6px;
}
.img
{
float:left; width:150px; max-height: 100px; margin-right:10px;
text-align:center;
}
#databox
{
border:solid 2px #bbbbbb; width: 500px; min-height:100px; padding:15px;
display:none;
}
</style>
</head>
<body>
<div style="margin:50px; padding:10px; width:460px">
<div style="height:25px">
<div style="float:left"><b>Enter url</b><br />e.g., http://thecodinglessons.blogspot.com</div></div><textarea id="linkbox"></textarea><div id="databox"></div></div>
</body>
</html>

link_image.gif - take any image which shows loading picture and put its name in place of link_image.gif.
getdata.php - it is the PHP file which extracts the site data. Its code goes here.......

<?php
if($_GET['link'])
{
 echo file_get_contents($_GET['link']);
}
?>

The title and logo extraction is done in javascript code.

Joomla 3 installation issue on WAMP server version 2.4

2013-09-15T07:12:00.004+05:30

A few days back, I installed WAMP 2.4 on Windows 8 (64 bit). Then I started installing Joomla 3 (latest version at that time) on this operating system. Now, the installation got stuck at the database creation. At first look, it looked like that there might be some incompatibility between Joomla 3 and Windows 8. But the actual problem was with WAMP 2.4 as I searched for the solutions.

In fact, the problem was with PHP version 5.4 which comes by default in WAMP 2.4.

So, at first look, the solution might be like this:

Download the 2.2 version of Wamp Server 32 bit or 64 bit depending upon your windows.
Install it. Then install Joomla 3 , if will become a piece of cake for you now.

If you don't want to compromise on PHP version, then there is another solution available.
Just try this out.

Go to wamp\www\joomla folder\installation\sql\mysql and open joomla.sql file.
Find the term "ENGINE=InnoDB" and replace all with "ENGINE=MyIsam".

The reason that this trick works is that MyIsam is more supported and compatible with Joomla.

Hope you will like the post. Thanks for reading it.

URL Rewriting A Brief Introduction

2013-09-10T03:10:00.002+05:30

Hello everyone,
these days you see many urls on different sites shown as some shortened random text. We may call them shortened urls which are made to function correctly by using URL Rewriting on the server on which the target website is hosted. This is just one example where URL rewriting is used. Many other usages are also there. Let's have a brief introduction of this technique below.

URL Rewriting A Brief Summary

First of all, you should know its definition.
URL rewriting is the process of altering (often automatically by means of a program written for that purpose) the parameters in a URL (Uniform Resource Locator). It allows you to completely separate the URL from the resource.
Suppose the website is at http://www.abc.com/a/b/index.html. You want to hide the path a/b/, i.e., you want the url to be http://www.abc.com/, then this can be achieved through this technique.

How to Rewrite URLs

Implementing URL rewriting on a website depends on the Web server.

For example, Apache usually comes with the URL rewriting module, mod_rewrite, already installed.
Windows IIS requires installation of ISAPI Rewrite module.
Mostly it is done by editing .htaccess files on the server. There are some rewrite rules to make the things work. Well you can find the complete guide to rules to edit .htaccess and Rewrite Engine rules on the web. There are many sites available for this.

Thanks for reading the post, hope you may have got some insight into URL Rewriting.

TinyMCE issue with IE9

2013-09-08T15:51:00.000+05:30

This is just a simple post about one of the problems I had while using tinyMCE editor in Internet Explorer.
The issue was actually in the versions above IE8 only, i.e. IE9 and IE10. May be there some compatibility issues between tinyMCE editor and Internet Explorer.
Suppose there is a form having a textarea with tinyMCE controls added to it and a submit button. The form uses post method. When you submit this form with a value in the textarea, then that value is not posted to the action of the form(in IE9, IE10; for other browsers, it works fine). That means, the value of the textarea with tinyMCE controls is not transferred through HTTP POST method in versions above IE8.
So, unless the issuse is fixed in tinyMCE editor or Internet Explorer, we can force the Internet Explorer to behave as its older version.
The simplest thing is to just include this line in your html document(where you are using textarea with tinyMCE controls) after the starting of the head tag.

<head>
<meta http-equiv="X-UA-Compatible" content="IE=EmulateIE7" />

That's it. Well, there are many solutions available for this issue but I think it is the simplest one.

Simple Pagination Script in PHP

2013-09-08T02:35:00.001+05:30

Let's do a bit of coding in PHP with mysql as the DBMS. Here, I will present the simplest of code to make a page which shows a fixed number of records on a page with the next and previous links to access the next or previous records.I think you are confused what I am saying.

Let's take an example.
Suppose there is a database named "profile"(Let's say in MYSQL). It has a table named "tbl_comment" which has two fields cid and ctext. Now there is a php file named comment.php which will show the list of comments from the tbl_comment table. As the number of comments in the table increase, we need a system to show them systematically on the screen so that the end-user has not to scroll much to read the comments. There comes the pagination script handy.

In the code below, I will not present the page numbers. Only next and previous links will be shown. Let's assume that only each only 5 comments be shown.

Here is the PHP Pagination script for achieving this thing.

<?php
$dbc = mysql_connect("localhost", "root", "root") or die(mysql_error()); 
mysql_select_db("profile") or die(mysql_error());
$start = 0;
$limit = 6;
if(isset($_GET['start']))
{
 $start = @mysql_real_escape_string(trim($_GET['start']));
}
if(is_numeric($start) === false)
{
 $start = 0;
}
$query = "select * from tbl_comment limit ".$start.", ".$limit;
$result = @mysql_query($query);
?>
<div style="width: 20%;">
<?php
if($start > ($limit - 2))
{
 echo '<a href="comment.php?start='.($start - ($limit - 1)).'">&lt;&lt;previous</a>&nbsp;&nbsp;&nbsp;&nbsp;';
}
if(@mysql_num_rows($result) > ($limit - 1))
{
 echo '<a href="comment.php?start='.($start + ($limit - 1)).'" style="float: right;">next&gt;&gt;</a>';
}
echo "</div>";         
$i = 0;
while($row = @mysql_fetch_array($result))
{
 if($i == 5)
 {
  break;
 }
 $i ++;
 echo "<div>".$row['cid'].") ".$row['ctext']."</div>";           
}
?>

Important Points:

This script takes the start GET parameter, selects the records from the database starting from the value of the start GET parameter to the limit of 6. Then it checks for the next and previous links show hide logic and shows 5 comments.
Substitute your parameters for the database connection in the first two lines.
You can change $limit variable to the limit you want.
Replace comment.php in the hrefs of <a> tags to the php file name in which you will place this code.

PHP Redirection - Anchor link problem in Internet explorer

2013-09-05T03:27:00.001+05:30

Well, this was one of the latest issue encountered by me while redirecting to a php page in a php file using php header() function in Internet Explorer.
Problem Description:
header("Location: abc.php?a=b#area"); where area is an anchor link in the html generated by php file abc.php like this
<a name="area" id="area"></a>
The above header function should take to the page abc.php with the current view of the screen on "area" link.
It did so in all major browsers except in Internet Explorer.

So, the solution for the problem was needed to make it browser compatible. After searching and trying different solutions, I adopted the following solution.
Problem Solution:
Here, I will not use the # symbol in the redirection link as it is not a good practice as described in a document on wikepedia----"Clients are not supposed to send URI-fragments to servers when they retrieve a document, and without help from a local application fragments do not participate in HTTP redirections".

The new header function is: header("Location: abc.php?a=b&link=area");
In abc.php, just check if $_GET['link'] is set to area. If it is, then just use this javascript (use jquery function to scroll it to the area link; don't forget to include jquery.min.js).
<?php
if(isset($_GET['link']) && $_GET['link'] == "area")
{
echo '<script type="text/javascript">
$(document).ready(function(){
$(document).scrollTop( $("#area").offset().top );
}); </script>';
}
?>

Thanks for reading this post. Hope you may have got the solution if you have this type of problem.

INTRODUCTION TO CODING PLATFORM BLOG

2013-08-22T02:35:00.000+05:30

Hello coders,

Welcome to the coding platform blog.
Well, this blog will enhance your coding experience. For beginners, it will help develop expertise towards coding languages and think about better solutions to computing problems. It will help in taking expertise to next higher level for expert coders. Here, my experience with computer programming, some coding problems and their solutions, and the new emerging technologies in web development and application development will be shared. Hope everyone will enjoy while learning from this blog.

Thanks.