Posted on

PHP MySQL Update

The UPDATE statement is used to modify data in a table.


Update Data In a Database

The UPDATE statement is used to update existing records in a table.

Syntax

UPDATE table_name
SET column1=value, column2=value2,…
WHERE some_column=some_value

Note:Notice the WHERE clause in the UPDATE syntax. The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!

To learn more about SQL, please visit our SQL tutorial.

To get PHP to execute the statement above we must use the mysql_query() function. This function is used to send a query or command to a MySQL connection.

Example

Earlier in the tutorial we created a table named “Persons”. Here is how it looks:

FirstName LastName Age
Peter Griffin 35
Glenn Quagmire 33

The following example updates some data in the “Persons” table:

<?php
$con = mysql_connect(“localhost”,”peter”,”abc123″);
if (!$con)
{
die(‘Could not connect: ‘ . mysql_error());
}

mysql_select_db(“my_db”, $con);

mysql_query(“UPDATE Persons SET Age = ’36’
WHERE FirstName = ‘Peter’ AND LastName = ‘Griffin'”);

mysql_close($con);
?>

After the update, the “Persons” table will look like this:

FirstName LastName Age
Peter Griffin 36
Glenn Quagmire 33
Posted on

PHP MySQL The Where Clause

The WHERE clause is used to filter records.


The WHERE clause

The WHERE clause is used to extract only those records that fulfill a specified criterion.

Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name operator value

To learn more about SQL, please visit our SQL tutorial.

To get PHP to execute the statement above we must use the mysql_query() function. This function is used to send a query or command to a MySQL connection.

Example

The following example selects all rows from the “Persons” table where “FirstName=’Peter’:

<?php
$con = mysql_connect(“localhost”,”peter”,”abc123″);
if (!$con)
{
die(‘Could not connect: ‘ . mysql_error());
}mysql_select_db(“my_db”, $con);

$result = mysql_query(“SELECT * FROM Persons
WHERE FirstName=’Peter'”);

while($row = mysql_fetch_array($result))
{
echo $row[‘FirstName’] . ” ” . $row[‘LastName’];
echo “<br />”;
}
?>

The output of the code above will be:

Peter Griffin
________________________________________________________

PHP MySQL Order By Keyword

The ORDER BY keyword is used to sort the data in a recordset.


The ORDER BY Keyword

The ORDER BY keyword is used to sort the data in a recordset.

The ORDER BY keyword sort the records in ascending order by default.

If you want to sort the records in a descending order, you can use the DESC keyword.

Syntax

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC

To learn more about SQL, please visit our SQL tutorial.

Example

The following example selects all the data stored in the “Persons” table, and sorts the result by the “Age” column:

<?php
$con = mysql_connect(“localhost”,”peter”,”abc123″);
if (!$con)
{
die(‘Could not connect: ‘ . mysql_error());
}

mysql_select_db(“my_db”, $con);

$result = mysql_query(“SELECT * FROM Persons ORDER BY age”);

while($row = mysql_fetch_array($result))
{
echo $row[‘FirstName’];
echo ” ” . $row[‘LastName’];
echo ” ” . $row[‘Age’];
echo “<br />”;
}

mysql_close($con);
?>

The output of the code above will be:

Glenn Quagmire 33
Peter Griffin 35

 


Order by Two Columns

It is also possible to order by more than one column. When ordering by more than one column, the second column is only used if the values in the first column are equal:

SELECT column_name(s)
FROM table_name
ORDER BY column1, column2
Posted on

PHP MySQL Select

The SELECT statement is used to select data from a database.


Select Data From a Database Table

The SELECT statement is used to select data from a database.

Syntax

SELECT column_name(s)
FROM table_name

To learn more about SQL, please visit our SQL tutorial.

To get PHP to execute the statement above we must use the mysql_query() function. This function is used to send a query or command to a MySQL connection.

Example

The following example selects all the data stored in the “Persons” table (The * character selects all the data in the table):

<?php
$con = mysql_connect(“localhost”,”peter”,”abc123″);
if (!$con)
{
die(‘Could not connect: ‘ . mysql_error());
}

mysql_select_db(“my_db”, $con);

$result = mysql_query(“SELECT * FROM Persons”);

while($row = mysql_fetch_array($result))
{
echo $row[‘FirstName’] . ” ” . $row[‘LastName’];
echo “<br />”;
}

mysql_close($con);
?>

The example above stores the data returned by the mysql_query() function in the $result variable.

Next, we use the mysql_fetch_array() function to return the first row from the recordset as an array. Each call to mysql_fetch_array() returns the next row in the recordset. The while loop loops through all the records in the recordset. To print the value of each row, we use the PHP $row variable ($row[‘FirstName’] and $row[‘LastName’]).

The output of the code above will be:

Peter Griffin
Glenn Quagmire

 


Display the Result in an HTML Table

The following example selects the same data as the example above, but will display the data in an HTML table:

<?php
$con = mysql_connect(“localhost”,”peter”,”abc123″);
if (!$con)
{
die(‘Could not connect: ‘ . mysql_error());
}

mysql_select_db(“my_db”, $con);

$result = mysql_query(“SELECT * FROM Persons”);

echo <table border=’1′>
<
tr>
<th>Firstname</th>
<th>Lastname</th>
</tr>”;

while($row = mysql_fetch_array($result))
{
echo “<tr>”;
echo “<td>” . $row[‘FirstName’] . “</td>”;
echo “<td>” . $row[‘LastName’] . “</td>”;
echo “</tr>”;
}
echo “</table>”;

mysql_close($con);
?>

The output of the code above will be:

Firstname Lastname
Glenn Quagmire
Peter Griffin
Posted on

PHP MySQL Insert Into a Table

The INSERT INTO statement is used to insert new records in a table.


Insert Data Into a Database Table

The INSERT INTO statement is used to add new records to a database table.

Syntax

It is possible to write the INSERT INTO statement in two forms.

The first form doesn’t specify the column names where the data will be inserted, only their values:

INSERT INTO table_name
VALUES (value1, value2, value3,…)

The second form specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3,…)
VALUES (value1, value2, value3,…)

To learn more about SQL, please visit our SQL tutorial.

To get PHP to execute the statements above we must use the mysql_query() function. This function is used to send a query or command to a MySQL connection.

Example

In the previous chapter we created a table named “Persons”, with three columns; “Firstname”, “Lastname” and “Age”. We will use the same table in this example. The following example adds two new records to the “Persons” table:

<?php
$con = mysql_connect(“localhost”,”peter”,”abc123″);
if (!$con)
{
die(‘Could not connect: ‘ . mysql_error());
}

mysql_select_db(“my_db”, $con);

mysql_query(“INSERT INTO Persons (FirstName, LastName, Age)
VALUES (‘Peter’, ‘Griffin’, ’35’)”);

mysql_query(“INSERT INTO Persons (FirstName, LastName, Age)
VALUES (‘Glenn’, ‘Quagmire’, ’33’)”);

mysql_close($con);
?>

 


Insert Data From a Form Into a Database

Now we will create an HTML form that can be used to add new records to the “Persons” table.

Here is the HTML form:

<html>
<body>

<form action=”insert.php” method=”post”>
Firstname: <input type=”text” name=”firstname” />
Lastname: <input type=”text” name=”lastname” />
Age: <input type=”text” name=”age” />
<input type=”submit” />
</form>

</body>
</html>

When a user clicks the submit button in the HTML form in the example above, the form data is sent to “insert.php”.

The “insert.php” file connects to a database, and retrieves the values from the form with the PHP $_POST variables.

Then, the mysql_query() function executes the INSERT INTO statement, and a new record will be added to the “Persons” table.

Here is the “insert.php” page:

<?php
$con = mysql_connect(“localhost”,”peter”,”abc123″);
if (!$con)
{
die(‘Could not connect: ‘ . mysql_error());
}

mysql_select_db(“my_db”, $con);

$sql=”INSERT INTO Persons (FirstName, LastName, Age)
VALUES
(‘$_POST[firstname]’,’$_POST[lastname]’,’$_POST[age]’)”;

if (!mysql_query($sql,$con))
{
die(‘Error: ‘ . mysql_error());
}
echo “1 record added”;

mysql_close($con)
?>

Posted on

PHP MySQL Create Database and Tables

A database holds one or multiple tables.


Create a Database

The CREATE DATABASE statement is used to create a database in MySQL.

Syntax

CREATE DATABASE database_name

To learn more about SQL, please visit our SQL tutorial.

To get PHP to execute the statement above we must use the mysql_query() function. This function is used to send a query or command to a MySQL connection.

Example

The following example creates a database called “my_db”:

<?php
$con = mysql_connect(“localhost”,”peter”,”abc123″);
if (!$con)
{
die(‘Could not connect: ‘ . mysql_error());
}

if (mysql_query(“CREATE DATABASE my_db”,$con))
{
echo “Database created”;
}
else
{
echo “Error creating database: ” . mysql_error();
}

mysql_close($con);
?>

 


Create a Table

The CREATE TABLE statement is used to create a table in MySQL.

Syntax

CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
….

)

To learn more about SQL, please visit our SQL tutorial.

We must add the CREATE TABLE statement to the mysql_query() function to execute the command.

Example

The following example creates a table named “Persons”, with three columns. The column names will be “FirstName”, “LastName” and “Age”:

<?php
$con = mysql_connect(“localhost”,”peter”,”abc123″);
if (!$con)
{
die(‘Could not connect: ‘ . mysql_error());
}

// Create database
if (mysql_query(“CREATE DATABASE my_db”,$con))
{
echo “Database created”;
}
else
{
echo “Error creating database: ” . mysql_error();
}

// Create table
mysql_select_db(“my_db”, $con);
$sql = “CREATE TABLE Persons
(
FirstName varchar(15),
LastName varchar(15),
Age int
)”;

// Execute query
mysql_query($sql,$con);

mysql_close($con);
?>

Important: A database must be selected before a table can be created. The database is selected with the mysql_select_db() function.

Note: When you create a database field of type varchar, you must specify the maximum length of the field, e.g. varchar(15).

The data type specifies what type of data the column can hold. For a complete reference of all the data types available in MySQL, go to our complete Data Types reference.


Primary Keys and Auto Increment Fields

Each table should have a primary key field.

A primary key is used to uniquely identify the rows in a table. Each primary key value must be unique within the table. Furthermore, the primary key field cannot be null because the database engine requires a value to locate the record.

The following example sets the personID field as the primary key field. The primary key field is often an ID number, and is often used with the AUTO_INCREMENT setting. AUTO_INCREMENT automatically increases the value of the field by 1 each time a new record is added. To ensure that the primary key field cannot be null, we must add the NOT NULL setting to the field.

Example

$sql = “CREATE TABLE Persons
(
personID int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(personID),
FirstName varchar(15),
LastName varchar(15),
Age int
)”;

mysql_query($sql,$con);

Posted on

Connect to a MySQL Database with PHP

The free MySQL database is very often used with PHP.


Create a Connection to a MySQL Database

Before you can access data in a database, you must create a connection to the database.

In PHP, this is done with the mysql_connect() function.

Syntax

mysql_connect(servername,username,password);

 

Parameter Description
servername Optional. Specifies the server to connect to. Default value is “localhost:3306”
username Optional. Specifies the username to log in with. Default value is the name of the user that owns the server process
password Optional. Specifies the password to log in with. Default is “”

Note: There are more available parameters, but the ones listed above are the most important. Visit our full PHP MySQL Reference for more details.

Example

In the following example we store the connection in a variable ($con) for later use in the script. The “die” part will be executed if the connection fails:

<?php
$con = mysql_connect(“localhost”,”peter”,”abc123″);
if (!$con)
{
die(‘Could not connect: ‘ . mysql_error());
}

// some code
?>

 


Closing a Connection

The connection will be closed automatically when the script ends. To close the connection before, use the mysql_close() function:

<?php
$con = mysql_connect(“localhost”,”peter”,”abc123″);
if (!$con)
{
die(‘Could not connect: ‘ . mysql_error());
}

// some code

mysql_close($con);
?>

Posted on 2 Comments

MySQL vs MS SQL Server

Comparing the open source MySQL database server against the proprietary database server “MS SQL Server” from Microsoft.

Performance Comparison

The Transaction Processing Performance Council is an independent organization that specifies the typical transactions (transactions used in inventory control systems, airline reservation systems and banking systems) and some general rules these transactions should satisfy. The TPC produces benchmarks that measure transaction processing and database performance in terms of how many transactions a given system and database can perform per unit of time, e.g., transactions per second or transactions per minute.

As of June 2006, SQL Server 2000 holds the third position in the OLTP Test in price by performance results and Microsoft SQL Server 2005×64 holds the first place. However MySQL does not participate in these tests, so it is difficult to make a head on comparison using TPC benchmarks.

In some benchmarks, MySQL has proven to be faster in its basic table format, MyISAM. MyISAM databases are very compact on disk and place little demand on CPU cycles and memory. MySQL can run on Windows without complaint but performs better on UNIX and UNIX-like systems. Additional performance gains can be had by using MySQL on a 64-bit processor, because MySQL uses an abundance of 64-bit integers internally. Much of the very busy Yahoo! Finance portal and Slashdot use MySQL as a back-end database.

It’s worth noting that both systems will work well within either .NET or J2EE architecture. Similarly, both will benefit from RAID, and both will perform best if the data store is on a hard drive or array that is solely dedicated to that purpose.

Given the lack of a standard benchmark upon which to compare the two databases, it’s not possible to conclusively rule on which database has the upper hand in performance.

Features Comparison

Regardless of other differences, the SQL Server 2000 and MySQL have a widely different feature set.

SQL Server 2000 and MySQL v5.0 limits

Although many of the limits placed by the database are for the purists, some are important for everyone to take note.

Some of these built-in limitations may be crucial for database design. Varchar size especially can be problematic, as often stored data such as comments or articles may be forced into BLOBs or TEXT columns. Total row size is also important for this reason.

Feature SQL Server 2000 MySQL v5.0 (MyISAM)
Column name length 128 64
Index name length 128 64
Table name length 128 64
Max indexes per table 250 64 (128 with recompile)
Index length 900 1024
Max index column length 900 255
Columns per index 16 16
Max char size 8000 255
Max varchar size 8000 65532
Max blob size 2147483647 2147483647
Max columns in GROUP BY Limited by number of bytes (8060) 64
Max columns in ORDER BY Limited by number of bytes (8060) 64
Tables per SELECT statement 256 31
Max columns per table 1024 3398
Max table row length 8036 65534
Longest SQL statement 16777216 1048574
Constant string size in SELECT 16777207 1048565

Data Storage

SQL Server with its closed, proprietary storage engine is fundamentally different from MySQL extensible, open storage engine. Its Sybase-derived database engine boasts of an adaptive algorithm that does most of the tuning that earlier needed to done manually. While SQL Server 2000 chooses the strategy of a single engine doing all the work, MySQL supports pluggable storage engines that can be chosen depending upon the facilities needed.

MySQL’s strategy is useful for various types of database use: quick read access to data without the need for transactional overhead is provided by the built in MyISAM engine, whereas InnoDB, a third party database engine owned by Oracle and licensed under the GPL is most often used for transactions and other features at the cost of some read performance. The MEMORY storage engine creates tables with contents that are stored in memory, and is useful for embedded database applications. NDB Cluster

is the storage engine used by MySQL Cluster to implement tables that are partitioned over many computers.

The FEDERATED storage engine stores data in a remote database. In its current release, it works with MySQL only but its future releases will be able to connect to other data sources using other driver or client connection methods.

InnoDB engine has the most advanced database feature set. The disadvantage to MySQL’s pluggable database engine scheme is that care must be given when selecting the engine to use when designing the database before use.

MySQL database engine feature comparison

MyISAM InnoDB MEMORY NDB
Multi-statement transactions, ROLLBACK X X
Foreign key constraints X
Locking level table row table row
BTREE indexes X X X
FULLTEXT indexes X
HASH lookups X X X
Other in-memory tree-based index 4.1.0
GIS, RTREE indexes 4.1.0
Unicode 4.1.0 4.1.2
Merge (union views) X
Compress read-only storage X
Relative disk use low high low
Relative memory use low high low high

Database Features

One of the critical features of any database engine is data integrity. ACID (Atomic, Consistent, Isolated, and Durable) compliance is a qualification that assures data integrity. ACID essentially means that when a transaction is performed within a database, either the whole transaction is successful and the information is written to the database, or nothing is written. Both SQL Server 2000 and MySQL supports ACID-compliant transaction functionality. SQL Server locks are dynamically applied at various levels of granularity, in order to select the least restrictive lock required for the transaction.

InnoDB provides MySQL with a transaction-safe (ACID compliant) storage engine with commit, rollback, and crash recovery capabilities. InnoDB does locking on the row level and also provides an Oracle-style consistent non-locking read in SELECT statements. These features increase multi-user concurrency and performance. In SQL queries you can freely mix InnoDB type tables with other table types of MySQL, even within the same query.

With the new release of MySQL 5.0, it has now incorporated several features that were missing earlier, and thus some of the reasons that caused you to choose SQL Server 2000 are no longer valid, for example, the need of Views, Cursors and Procedures. One thing now lacking is the full support for triggers. Another thing in which MySQL lags behind is XML support, and with the release of Yukon, this is going to look like a big gap in functionality.

Feature SQL Server 2000 MySQL v5.0
ACID Yes Yes
Referential Integrity Yes Yes
Transactions Yes Yes
Temporary Table Yes Yes
Views Indexed views Updateable views
Indexes
R-/R+ tree ? MyISAM only
Hash ? MEMORY only
Expression ? No
Partial ? No
Reverse ? No
Bitmap ? No
Cursor Yes Yes
Trigger Yes Rudimentary
Function Yes Yes
Procedure Yes Yes
External routine Yes Yes
Partitioning
Range Yes NDB only
Hash No No
Composite No No
List No No
XML support Yes No
Unicode Yes Yes

Database Warehousing

The data warehouses or data marts can be used for sophisticated enterprise intelligence systems that process queries required to discover trends and analyze critical factors. These systems are called online analytical processing (OLAP) systems. The data in data warehouses and data marts is organized differently than in traditional transaction processing databases.

While SQL Server 2000 provides a lot of tools for creating and managing data warehouses, MySQL does not have any significant support for it. Data Transformation Services (DTS) provides a set of services used to build a data warehouse or data mart. Analysis Services is an excellent tool for multidimensional analysis of data in SQL Server 2000 databases. Analysis Services supports multidimensional queries against cubes with hundreds of millions of dimensions and you can even control cube security down to the level of cells and members. Data mining allows you to define models containing grouping and predictive rules that can be applied to data in either a relational database or multi-dimensional OLAP cubes. These predictive models are then used to automatically perform sophisticated analysis of the data to find trends.

Application Development

SQL Server 2000 and MySQL both support ODBC and JDBC for network connectivity, as well as native database access methods. These native methods provide access via the network in both plain text methods and, for a higher level of security, SSL-encrypted methods.

Another important part of database interface methods is authentication for the database. MySQL uses a simple method to store all of its authentication information inside a table. When users attempt to access a database, MySQL compares their credentials against this database, verifying from which machines the users can connect and to what resources they have access. SQL Server 2000 also has integrated Windows authentication.

A number of programming methods also provide ways to access these databases. Both SQL Server 2000 and MySQL support access via C/C++ , Java, Perl, Python, and PHP. One thing that differentiates SQL Server 2000 is its XML capabilities. Using SQLXML package with SQL Server 2000, it is possible to query relational data using XQuery and even expose stored procedures as web services.

Installation

Hardware and Software Requirements

Installation of Microsoft SQL Server requires a Windows operating system. So, if you need to run the database on a Linux box, the comparison is clearly in favor of MySQL. MySQL has been ported to all major operating systems and can be ported to any os that has a C++ compiler and a working implementation of POSIX threads. Using GNU autoconf, MySQL happily compiles on various flavors of UNIX, Windows, Linux and Mac OS X.

Hardware cost is not as much of an issue as it once was, with RAM and hard disk space costs being low. But for the hardware conscious, SQL server can look like a hog compared to MySQL. While SQL server requires a minimum of 128 MB of RAM for reasonable performance, MySQL can easily run on a Linux with as little as 32 MB of RAM, this is likely that is the reason that the MySQL installation manual doesn’t bother specifying hardware requirements.

Although it is not too difficult to compile the MySQL source code, a ready installer is always welcome to get you up and running quickly. With the release of MySQL 4.1.5, MySQL AB has introduced an installer for the Windows version of MySQL, combined with a new GUI Configuration Wizard. This combination automatically installs MySQL, creates an option file, starts the server, and secures the default user accounts.

Hardware SQL Server 2000 MySQL 5.0
Operating system Windows XP, Windows 2000 Windows 9x, Me, NT, 2000, XP, and 2003 Linux 2.0+
Mac OSX 

AIX 4.x, 5.x

BSDI 3.0, 3.1 and 4.x

FreeBSD 3.x, 4.x, 5.x

OpenBSD 2.5+

Digital Unix 4.x

HP-UX 10.20, 11.x

NetBSD 1.3/1.4 Intel, 1.3 Alpha

SCO Open Server, UnixWare 7.1

SGI Irix 6.5

Solaris 2.5

Memory Enterprise Edition and Enterprise Evaluation Edition: 64 MB minimum, 128 MB or more recommendedStandard Edition and Developer Edition: 64 MB minimum 

Personal Edition and Desktop Engine (MSDE 2000):

128 MB minimum on Windows XP

64 MB minimum on Windows 2000

32 MB minimum on other Windows

32 MB of RAM
Hard disk space Enterprise, Enterprise Evaluation, Standard, Developer, and Personal Editions require:95 to 270 MB of available hard disk space for the database engine; 250 MB for a typical installation. 

50 MB of available hard disk space for a minimum installation of Analysis Services; 130 MB for a typical installation.

80 MB of available hard disk space for English Query.

Desktop Engine (MSDE 2000): 44 MB minimum

60 to 85 MB depending on the components and operating system; 200 MB recommended for Windows.

Installation

Microsoft products have always been known for the painless and quick installation through intuitive wizards. MySQL, despite being an open source product is no less, and you can expect the same kind of breezy installation. The installer will even create a Windows service for the MySQL database.

Administration and Maintenance

MySQL provides two types of backup, one where the database dump is taken and another where it emits all the SQL statements required to create the database. SQL Server 2000 only provides the first option. When it comes to hot backups, or backing up your database without shutting it down, both database solutions have methods for doing so. MySQL supports incremental backup strategy for minimizing the amount of time taken if an existing database backup is present.

SQL Server 2000 failover clustering provides high availability support. For example, during an operating system failure or a planned upgrade, you can configure one failover cluster to fail over to any other node in the failover cluster configuration. In this way, you minimize system downtime, thus providing high server availability. But for this, you will need to buy the Enterprise version. MySQL also provides clustering through its NDB database engine, but it is slightly difficult to configure.

MySQL supports one-way replication. One server acts as the master, while one or more other servers act as slaves. The master server writes updates to its binary log files and the slave servers periodically connect to get updates. SQL Server 2000 offers far more facilities for replication. It offers three choices – Snapshot, Transactional and Merge. While the transactional facility is similar to the MySQL master slave replication, Snapshot is useful where data has changed a lot and so the entire snapshot is sent to the slave. Using Merge replication, two SQL servers can send updates to each other and are a perfect choice for geographically separated SQL servers.

Both products have perfectly acceptable default security mechanisms, as long as you follow the manufacturer’s directions and keep up with your security patches. Both products operate over well-known default IP ports, and, unfortunately, those ports draw intruders like flies to honey. Fortunately, both SQL Server and MySQL allow you to change that port should the default become too popular a destination for your taste.

Stability is one area where MySQL, in its MyISAM configuration, falls a little short. With MyISAM, a UPS is absolutely mandatory because MyISAM assumes uninterrupted operation. If it is shut down unexpectedly, the result could be corruption of the entire data. SQL Server, on the other hand, is far more resistant to data corruption. SQL Server’s data goes through multiple checkpoints and SQL Server remembers where it was in the process even if it happens to be shut down without warning.

SQL Server 2000 MySQL v5.0
Hot backups Yes Yes
Replication Snapshot, Transactional, Merge One-way only
Clustering Yes Yes
Security features High High
Locking and concurrency support Fully automated Row-locking (InnoDB)
Stability High High(unless MyISAM)
GUI Administration Tools Yes Yes (download)

Price

When it comes to licensing costs, MySQL definitely has an edge as it is free and open source software licensed under the GPL. With SQL Server, the most popular way to get a free developer license is to purchase a license for Microsoft Office Developer or Microsoft Visual Studio, both of which give you a free “development use only” SQL Server license.

SQL Server 2000 is currently available under two licensing options:

  • Processor license
  • Server/per-seat client access license (CAL)

The processor license requires a single license for each CPU in the computer running SQL Server 2000 and includes unlimited client access. You can buy this license when you do not know the number of the clients (for example, if your users will connect to SQL Server 2000 through the internet). This license usually is cheaper than Server/Per-Seat CAL when there are many users connected to SQL Server databases.

The Server/per-seat client access license (CAL) requires a license for the server and the licenses for each client device. You can use this licensing option when the customers do not need access beyond the firewall and the number of clients is low (for example, 10-20 users for SQL Server 2000 Standard Edition or 30-40 users for SQL Server 2000 Enterprise Edition).

Lately, Microsoft Database Engine (MSDE) which is actually a scaled down version of SQL Server has been made available as a free download. It is a very attractive choice if you need an embedded database for your windows applications, but is not recommended for use as a server in production environments.

Licensing Options SQL Server 2000 Standard Edition SQL Server 2000 Enterprise Edition
Processor $4,999 per processor $19,999 per processor
Server/Per-Seat CAL with 5 CAL – $1,489with 10 CAL – $2,249 with 25 CAL – $11,099

The MySQL version 5.0 is Dual Licensed. Users can choose to use the MySQL software as an Open Source/Free Software product under the terms of the GNU General Public License or can purchase a standard commercial license from MySQL AB.

The GPL license grants the user a right to use the database at no cost with the condition that any changes made to the source code of the database would have to be made available under GPL. Since most of the applications simply use the database, it is usually a zero-cost alternative.

The MySQL v5.0 Server commercial license is per database server (single installed MySQL binary). The price comparisons below were based on the MySQL Licensing Prices from MySQL AB.

Number of licenses Price per copy (EUR) Price per copy (USD)
1..9 440 495
10..49 315 360
50..99 255 290
100..249 195 220
250..499 155 175

Overall, MySQL has the edge in price as it is free, and performance can’t be conclusively shown to be any worse than MS SQL—it is also used extensively for high profile web applications such as various Yahoo properties and Google’s Adwords. Certain advanced features are not bundled with MySQL, but for basic simple development without licensing cost, MySQL can be seen to have the edge.

MS SQL 2000 and now 2005, are geared towards Microsoft style development on Windows Servers. Installation and configuration may be easier in MS SQL, with advanced database features present by default. MS SQL also implements features not found in any MySQL database engine, and is not overly high in license costs compared to some high end databases. For certain mid-level uses on Windows Servers, MS SQL probably wins out.

Both databases have a wide traction in the market, but being an open source project, MySQL community is somewhat more open and free, and being completely free, MySQL likely has a broader base of support.

Posted on 1 Comment

Exporting data from MS Access to MySQL

Migrating data from MS Access 2003 to MySQL

Most businesses use a software mix in their IT arsenal that makes business sense to them. Because of this, often they have to migrate a part, or whole of their data from one software program to another. In this article by Dr. Jay Krishnaswamy, the built-in method of exporting tables in Microsoft Access is explored to take a table in Microsoft over to MySQL, the open source database product that changed hands recently. This article steps you through the process with a number of screen shots to guide you along the way.

Introduction

It is assumed that you have a working copy of MySQL which you can use to work with this article. The MySQL version used in this article came with the XAMPP download. XAMPP is an easy to install (and use) Apache distribution containing MySQL, PHP, and Perl. The distribution used in this article is XAMPP for Windows. You can find documentation here. Here is a screen shot of the XAMPP control panel where you can turn the services on and off and carry out other administrative tasks.

Exporting data from MS Access 2003 to MySQL

You need to follow the steps indicated here:

  1. Create a database in MySQL to which you will export a table from Microsoft Access 2003
  2. Create a ODBC DSN that helps you connecting Microsoft Access to MySQL
  3. Export the table or tables
  4. Verify the exported items

Creating a database in MySQL

You can create a database in MySQL by using the command ‘Create Database’ in MySQL or using a suitable graphic user interface such as MySQL workbench. You will have to refer to documentation that works with your version of MySQL. Herein the following version was used. The next listing shows how a database named TestMove was created in MySQL starting from the bin folder of the MySQL program folder. Follow the commands and the response from the computer. The Listing 1 and the folders are appropriate for my computer and you may find it in your installation directory. The databases you will be seeing will be different from what you see here except for those created by the installation.

Listing 1: Login and create a database

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:Documents and SettingsJayaram Krishnaswamy>cd

C:>cd xamppmysqlbin

C:xamppmysqlbin>mysql -h localhost -u root -p
Enter password: *********
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.1.30-community MySQL Community Server (GPL)

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cdcol |
| expacc |
| mengerie |
| mydb |
| mysql |
| phpmyadmin |
| test |
| testdemo |
| webauth |
+--------------------+
10 rows in set (0.23 sec)

mysql> create database TestMove;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cdcol |
| expacc |
| mengerie |
| mydb |
| mysql |
| phpmyadmin |
| test |
| testdemo |
| testmove |
| webauth |
+--------------------+
11 rows in set (0.00 sec)

mysql>

The login detail that works error free is shown. The preference for host name is localhost v/s either the Machine Name (in this case Hodentek2) or the IP address. The first ‘Show Databases’ command does not display the TestMove we created which you can see in response to the 2nd Show Databases command. In windows the commands are not case sensitive.

Creating an ODBC DSN to connect to MySQL

When you install from XAMPP you will also be installing an ODBC driver for MySQL for the version of MySQL included in the bundle. In the MySQL version used for this article the version is MySQL ODBC5.1 and the file name is MyODBC5.dll.

Click Start | Control Panel | Administrative Tools | Data Sources (ODBC) and open the ODBC Data Source Administrator window as shown. The default tab is User DSN. Change to System DSN as shown here.

Exporting data from MS Access 2003 to MySQL

Click the Add… button to open the Create New Data Source window as shown.

Exporting data from MS Access 2003 to MySQL

Scroll down and choose MySQL ODBC 5.1 Driver as the driver and click Finish. The MySQL Connector/ODBC Data Source Configuration window shows up.

Exporting data from MS Access 2003 to MySQL

You will have to provide a Data Source Name (DSN) and a description. The server is the localhost. You must have your User Name/Password information to proceed further. The database is the name of the database you created earlier (TestMove) and this should show up in the drop-down list if the rest of the information is correct. Accept the default port. If all information is correct the Test button gets enabled.

Exporting data from MS Access 2003 to MySQL

Click and test the connection using the Test button. You should get a response as shown.

Exporting data from MS Access 2003 to MySQL

Click the OK button on the Test Result window. Click OK on the MySQL Connector/ODBC Data Source Configuration window. There are a number of other flags that you can set up using the ‘Details’ button. The defaults are acceptable for this article.

You have successfully created a System DSN ‘AccMySQL‘ as shown in the next window. Click OK.

Exporting data from MS Access 2003 to MySQL

Verify the contents of TestMove

The TestMove is a new database created in MySQL and as such it is empty as you verify in the following listing.

Listing 2: Database TestMove is empty

mysql> use testmove;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql>

Export the Employees table from MS Access to TestMove

Open the Northwind.mdb (or any other mdb file) file as shown in the next figure.

Exporting data from MS Access 2003 to MySQL

Highlight the Employees table and make a right click to bring up a drop-down menu as shown.

Exporting data from MS Access 2003 to MySQL

Click on Export to open the Export Table ‘Employees’ To… window. Click on the Save as type drop-down to reveal the various file types that you can export to as shown.

Exporting data from MS Access 2003 to MySQL

Click on ODBC Sources the Export window pops-up as shown.

Exporting data from MS Access 2003 to MySQL

Click OK. This opens the Select Data Source window. Click on the Machine Data Source tab to show the various available DSN’s. You will notice the AccMySQL created earlier.

Exporting data from MS Access 2003 to MySQL

Choose AccMySql and click OK. The program returns you to MS Access’s Northwind database without giving you an indication about the success/failure of the export. If there is an error it however gives a error message.

Verifying and accessing exported table

Now go back to the DOS screen. I assume you have not closed down the mysql > prompt. Enter commands as shown in the listing.

mysql> use testmove
Database changed
mysql> show tables;
+--------------------+
| Tables_in_testmove |
+--------------------+
| employees |
+--------------------+
1 row in set (0.00 sec)

mysql>

Now you can see that the Employees table has entered the TestMove database in MySql. Probe a little further by entering a select statement to see how much of the data has come in.

mysql> use testmove
Database changed
mysql> show tables;
+--------------------+
| Tables_in_testmove |
+--------------------+
| employees |
+--------------------+
1 row in set (0.00 sec)

mysql> Select * from Employees;
+------------+-----------+-----------+------------------------
--------------+---------------------+-------------------------
--------------------------------------------------------------
----------------------------------------+-----------+
| EmployeeID | LastName | FirstName | Title
 | TitleOfCourtesy | BirthD
ate | HireDate | Address
 | City | Region |
 PostalCode | Country | HomePhone | Extension | Photo
 | Notes | ReportsTo |
+------------+-----------+-----------+------------------------
--------------+---------------------+-------------------------
--------------------------------------------------------------
----------------------------------------+-----------+
| 1 | Davolio | Nancy | Sales Representative
 | Ms. | 1968-1
2-08 00:00:00 | 1992-05-01 00:00:00 | 507 - 20th Ave. E.
Apt. 2A | Seattle | WA | 98122 | USA | (206)
555-9857 | 5467 | EmpID
1.bmp | Education includes a BA in psychology from Colorado
State University. She also co
mpleted "The Art of the Cold Call." Nancy is a member of
Toastmasters International.| 2 |

| 2 | Fuller | Andrew | Vice President, Sales
 | Dr. | 1952-0
2-19 00:00:00 | 1992-08-14 00:00:00 | 908 W. Capital Way
 | Tacoma | WA |
 98401 | USA | (206) 555-9482 | 3457 |
EmpID2.bmp | Andrew received his BTS
commercial and a Ph.D. in international marketing from the University
of Dallas. He is fluent in French and Italian and reads German.
He joined the company as a sales representative, was promoted to
sales manager and was then named vice president of sales.
Andrew is a member of the Sales Management Roundtable,
the Seattle Chamber of Commerce, and the Pacific Rim Importers
Association. | NULL |

| 3 | Buchanan | Steven | Sales Manager
 | Mr. | 1955-0
3-04 00:00:00 | 1993-10-17 00:00:00 | 14 Garrett Hill
 | London | NULL |
 SW1 8JR | UK | (71) 555-4848 | 3453 |
EmpID5.bmp | Steven Buchanan graduate
d from St. Andrews University, Scotland, with a BSC degree.
Upon joining the company as a sales representative, he spent
6 months in an orientation program at the Seattle office a
nd then returned to his permanent post in London, where he was
promoted to sales manager. Mr. Buchanan has completed the
courses "Successful Telemarketing" and "International Sale
s Management." He is fluent in French. | 2 |

| 4 | Suyama | Michael | Sales Representative
 | Mr. | 1963-0
7-02 00:00:00 | 1993-10-17 00:00:00 | Coventry House
Miner Rd. | London | NULL | EC2 7JR | UK |
(71) 555-7773 | 428 | E
mpID6.bmp | Michael is a graduate of Sussex University (MA,
economics) and the University of California at Los Angeles
(MBA, marketing). He has also taken the courses "Multi-Cultural
 Selling" and "Time Management for the Sales Professional."
 He is fluent in Japanese and can read and write French,
Portuguese, and Spanish.| 3 |

+------------+-----------+-----------+------------------------
--------------+---------------------+-------------------------
--------------------------------------------------------------
----------------------------------------+-----------+
4 rows in set (0.00 sec)

mysql>

The output is not particularly pretty but you can see that the data has come in. The next figure shows the same query run in MySQL Workbench a graphic tool used in querying and modeling MySql databases.

Exporting data from MS Access 2003 to MySQL

Summary

The article described the procedure to create an ODBC DSN for connecting to a MySQL Database. The article also described the process of copying data in an MS Access database to MySQL.