Category: Database

See how to insert date in a MySQL table to efficiently organize your dynamic website contents

September 10, 2010 / Ahmandonk

Learning how to handle MySQL databases in the easiest way possible has been the main subject of our recent few posts here, the last one of which was focused on managing MySQL databases through phpMyAdmin. Since managing a MySQL database and a MySQL table, in particular, is a multi-task process – there are operations that still need to be touched on.

The next important aspect of managing a MySQL is learning how to insert date in MySQL table, since specifying the dates of each content update is crucial for the logical structuring and arrangement of articles, posts and replies in an up-to-date dynamic website.

Reference to the newly updated insert-date-in-MySQL article will give you details about the recommended date statement format and illustrative examples of the basic ways for inserting date in MySQL – via a command line, a script (written in PHP, Perl, Python etc.), or via the PHPMyAdmin interface. You will learn how to use the MySQL built-in function CURDATE() in your import-date query or insert data manually. To further your knowledge of MySQL’s flexibilities you will also see how to set only the year of an event using the YEAR statement or how to store both the date and the time through the DATETIME statement.

Learning how to keep your MySQL databases dated will help you build and sustain a chronologically ordered archive of your rapidly growing dynamic content driven web presence.

What to expect from the newest version of MySQL – MySQL 6.0

September 10, 2010 / Ahmandonk

No modern website focused on the intuitive onsite experience of the visitors can exist without a good database at its backbone. Since the availability of dynamic content on a website has become a criterion for the website’s usability, boosting the amount of visitors attracted to its pages, database solutions are constantly evolving to keep up with the users’ demands.

So is MySQL – the most widely known and used database software on the web. With the users’ requirements getting more and more sophisticated – the software has been undergoing rapid development in the hands of its large supporting community ever since its launch to now approach the release of its latest super efficient new version – MySQL 6.

The latest stable version of MySQL is MySQL 5.1 and currently this is the version offered by most self-respected web hosting providers. However, just like any version of given extensively used software this version of the popular database solution is also about to ‘live its time’, giving way to a more advanced version that is intended to fill in its glitches and offer some brand new revolutionary characteristics.

Having announced the alpha pre-production release of MySQL 6.0, MySQL developers are now in a feverish preparations period, testing the new version and encouraging all users to take their time trying out its new functionalities in an off-production-level system environment.

In our MySQL 6 Preview article – you can check out in detail all new features, updates to existing functionalities and deprecated functionalities intrinsic to MySQL 6.0. Here are some of the best new MySQL 6 features you can expect from this soon-to-be-stable version:

The Falcon Storage Engine – one of the most noteworthy MySQL innovations, designed to work with multi-core CPUs and larger memory architectures. Its basic features include: Multi Version Concurrency Control (MVCC), allowing table updates without locking table rows; optimization for modern multi-threaded environments, enabling faster transactions; on-the-go data compression, decreasing the amount of disk space used; data and data index caching, providing faster access to the stored information, etc.
Backup improvements – another important function of MySQL 6.0 are the Database_Backup and Restore statements aimed at minimizing table locking during data backup and restore operations.
New Unicode Character Sets – the newly added support for more Unicode character sets, like utf16, utf32, and the 4-byte utf8 will extend the range of information stored in the databases.

Along with the brand new features added, MySQL 6.0 also offers some significant optimizations and enhancements of the existing MySQL database features like the updated syntax for the LOCK_TABLES statement and the addition of a LOAD_XML, allowing for the data in an XML file to be inserted directly into a MySQL table. Also, the new MySQL software version is associated with the removal of certain obsolete or security-glitches related features from the MySQL core.

To get deeper into the basics of the upcoming stable version of MySQL 6.0 – take a look at our article, explaining in more detail the new features, enhancements and removed funtionalities. In case you wish to take part in the alpha-testing campaign – go and download your MySQL 6.0 installation from the official website. Mind that you are strongly recommended to back up all your database data when moving from any previous MySQL version to the new one.

PostgreSQL databases – the new-generation back-end solution for contemporary dynamic websites

September 10, 2010 / Ahmandonk

The ‘invasion’ of dynamic content driven websites into the global virtual space has lead to the establishment of new web usability standards. Today websites are competing with each other to ensure an intuitive user experience by means of various new-generation browse-and-click techniques. Although visually different from each other, dynamic websites share one thing in common, they use databases for organizing and updating their contents. And speaking of new-generation solutions – the first database type coming to mind is PostgreSQL.

PostgreSQL is popular as a free alternative to powerful yet expensive corporate applications like MsSQL and Sybase, as they all originate from one and the same platform, the Ingres software developed back in the 1970s. Today embraced by a growing number of users, PostgreSQL allows a great freedom in handling databases through a wide range of in-built features like defined-by-user objects, Multi-Version Concurrency Control (MVCC), database triggers, indexes and various data types.

The user’s policy plays an important role in PgSQL, where the database user is called a ‘superuser’ and has all-round control over the access to the objects in the database. The PostgreSQL create user function of the software supports two methods – creating a user with an easy-to-use web interface (via PostgreSQL clients like pSQL, pgAdmin, or phpPgAdmin) and with the command-line wrapper called createuser, used mainly by programmers and administrators.

Each PgSQL user is assigned a wide range of privileges, like creating PostgreSQL databases and PostgreSQL tables, and managing their settings through the phpPgAdmin interface. Users can create databases in two ways – via the PostgreSQL command line, using the ‘create database’ command, and through the custom graphical interface offered by their hosting provider. Setting up tables is possible through the command line and via phpPgAdmin.

Alongside the regular database manipulation operations PgSQL databases also support some special little tools, like the PostgreSQL Sequence, aimed to alleviate your work with tables and databases. The sequence offers a simple and safe multi-user method for generating unique numeric identifiers in the PostgreSQL database. It is most often used for the creation of artificial primary keys. You can easily create a sequence in PostgreSQL via the command line or via the phpPgAdmin tool.

Managing PostgreSQL databases is also possible out of the operational system environment, through the ODBC software API method. This is done with the help of psqlODBC – the ODBC driver for PostgreSQL. It is now offered independently of the database package and undergoes constant updates and improvements.