Internet Methodologies Journal And News

We can always fetch from one database and rewrite to another. Here is a very simple solution.

  $db1 = mysql_connect("hostname","username","password") // Connect to Server 1
  mysql_select_db("db1″, $db1);
  $res1 = mysql_query("query",$db1);

  $db2 = mysql_connect("hostname","username","password"); // Connect to Server 2
  mysql_select_db("db2″, $db2);
  $res2 = mysql_query("query",$db2);

At this point you can only fetch records from you previous ResultSet, i.e $res1 – But you cannot execute new query in $db1, even if you
supply the link as because the link was overwritten by the new db, So at this point the following script will fail

$res3 = mysql_query("query",$db1); //this will failSo how to solve that?

Please have a look at.

	$db1 = mysql_connect("hostname","username","password");
	mysql_select_db("db1″, $db1);
	$res1 = mysql_query("query",$db1);

	$db2 = mysql_connect("hostname","username","password", true)
	mysql_select_db("db2", $db2);
	$res2 = mysql_query("query",$db2);

So mysql_connect has another optional boolean parameter which indicates whether a link will be created or not.

As we connect to the $db2 with this optional parameter set to ‘true’, so both link will remain live.

Now the following query will execute successfully.

	$res3 = mysql_query("query",$db1);

FLOATs store floating point numbers with 8 place accuracy and take up 4 bytes.

DOUBLEs store floating point numbers with 16 place accuracy and take up 8 bytes.

REAL is a synonym of FLOAT for now.

Persistent connections are links that do not close when the execution of your script ends. When a persistent connection is requested, PHP checks if there’s already an identical persistent connection (that remained open from earlier) – and if it exists, it uses it. If it does not exist, it creates the link. An ‘identical’ connection is a connection that was opened to the same host, with the same username and the same password (where applicable).

1. Download this file and import it into your database. Postcodes.txt

2. Rename Postcode.txt to Postcode.sql

3. Import to your database and enjoy complete list of UK Postcodes.

Here is the sample code:

CREATE TABLE IF NOT EXISTS `postcodes` (
  `id` int(11) NOT NULL auto_increment,
  `postcode` tinytext NOT NULL,
  `grid_n` varchar(15) NOT NULL,
  `grid_e` varchar(15) NOT NULL,
  `latitude` varchar(15) NOT NULL,
  `longitude` varchar(15) NOT NULL,
  PRIMARY KEY  (`id`)
);

INSERT INTO `postcodes` (`id`, `postcode`, `grid_n`, `grid_e`, `latitude`, `longitude`) VALUES
(1, 'AB10', '392900', '804900', '57.135', '-2.117'),
(2, 'AB11', '394500', '805300', '57.138', '-2.092'),
(3, 'AB12', '393300', '801100', '57.101', '-2.111'),
(4, 'AB13', '385600', '801900', '57.108', '-2.237'),
(5, 'AB14', '383600', '801100', '57.101', '-2.27'),
(6, 'AB15', '390000', '805300', '57.138', '-2.164');

<?php
$username = "your_name";
$password = "your password";
$hostname = "localhost";
$dbname = "database name"; 

//Connection to the MySql Database
$dbhandle = mysql_connect($hostname, $username, $password)
 or die("Unable to connect to MySQL");
mysql_select_db("$dbname",$db); 

echo "Connected to MySQL";
?>

When they are using mysql_connect() function, every time it is opening and closing the database connection, depending on the request .

mysql_connect() and mysql_pconnect() both are working for database connection but with small difference. In mysql_pconnect(), ‘p’ stands for persistance connection.

But in case of mysql_pconnect() function,
First, when connecting, the function would try to find a (persistent) connection that’s already open with the same host, username and password. If three is found, an identifier for it will be returned in lieu of opening a new connection.
Second, the connection to the SQL server won’t be closed when the execution of the script ends. In lieu, the connection will remain open for future use (mysql_close() won’t close connection established by mysql_pconnect()).

mysql_pconncet() is useful when you have a lot of traffice on your site. At that time for every request it won’t open a connection but will take it from the pool. This will increase the efficiency of your site. But for general use mysql_connect() is best.

myisamchk is used to get information about your database tables or to check, repair, or optimize them. This command can check or repair MyISAM tables. It can also optimize and analyze these tables. Database design, tuning of databases can be used to improve and optimize performance of MySql.

Indexes should be used to increase the performance of MySQl select query. Index on a column can be specified using col_name(n) syntax. Here, n specifies the first “n” characters that can be used to create an index. Columns that will be used more often in a select query should be indexed. When a new index is created; MYSQl builds a separate block of information that needs to be updated every time there are changes made to the table.

MySQL optimizes the following way:

• Removes unnecessary parenthesis
• Constant expressions used by indexes are evaluated only once
• Early detection of invalid constant expressions
• HAVING is merged with WHERE if GROUPBY is not used.
• The best Join combination is found.
• Before each row is output, those that do not match the HAVING clause are skipped.
• Each table index is queried and the best index is used.

What is the default table in MYSQL and which type of table is generatedby default.

MyISAM is the default storage engine.

If the operating system or filesystem places a limit on the number of files in a directory, MySQL is bound by that constraint.The efficiency of the operating system in handling large numbers of files in a directory can place a practical limit on the number of tables in a database. If the time required to open a file in the directory increases significantly as the number of files increases, database performance can be adversely affected.
The amount of available disk space limits the number of tables.

MySQL 3.22 had a 4GB (4 gigabyte) limit on table size. With the MyISAM storage engine in MySQL 3.23, the maximum table size was increased to
65536 terabytes (2567 – 1 bytes). With this larger allowed table size, the maximum effective table size for MySQL databases is usually determined by operating system constraints on file sizes, not by MySQL internal limits.The InnoDB storage engine maintains InnoDB tables within a tablespace
that can be created from several files. This allows a table to exceed the maximum individual file size. The tablespace can include raw disk
partitions, which allows extremely large tables. The maximum tablespace size is 64TB. The following table lists some examples of operating system file-size limits. This is only a rough guide and is not intended to be definitive. For the most up-to-date information, be sure to check the documentation specific to your operating system.

Operating System File-size LimitLinux 2.2-Intel 32-bit 2GB (LFS: 4GB)

Linux 2.4+ (using ext3 filesystem) 4TB

Solaris 9/10 16TB

NetWare w/NSS filesystem 8TB

Win32 w/ FAT/FAT32 2GB/4GB

Win32 w/ NTFS 2TB (possibly larger)

MacOS X w/ HFS+ 2TB

Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the
file type.

• The ‘.frm’ file stores the table definition.
• The data file has a ‘.MYD’ (MYData) extension.
• The index file has a ‘.MYI’ (MYIndex) extension.