Jsp, Tomcat, J2Ee, Hibernate Programming, Http Web Server Blog

GETTING STARTED WITH A DATABASE Each row contains an individual record of related data. The significance of primary keys is explained in the next section. The intersection of a row and a column, where the data is stored, is called a field. So, for instance, the caption field for the third record in Figure 11-1 contains the value The Golden Pavilion in Kyoto and the primary key for that record is 3. The terms field and column are often used interchangeably, particularly by phpMyAdmin. A field holds one piece of information for a single record, whereas a column contains the same field for all records. How primary keys work Although Figure 11-1 shows image_id as a consecutive sequence from 1 to 8, they re not row numbers. Figure 11-2 shows the same table with the captions sorted in alphabetical order. The field highlighted in Figure 11-1 has moved to the seventh row, but it still has the same image_id and filename. Figure 11-2. Even when the table is sorted in a different order, each record can be identified by its primary key. Although the primary key is rarely displayed, it identifies the record and all the data stored in it. Once you know the primary key of a record, you can update it, delete it, or use it to display data in a separate page. Don t worry about how you find the primary key. You ll see in the next chapter that it s easily done using Structured Query Language (SQL), the standard means of communicating with all major databases. The important thing to remember is that you should assign a primary key to every record. A primary key doesn t need to be a number, but it must be unique. Social security, staff ID, or product numbers make good primary keys. They may consist of a mixture of numbers, letters, and other characters, but are always dif ferent. MySQL will generate a primary key for you automatically. Once a primary key has been assigned, it should never repeat, never be changed. 287
Note: In case you are looking for affordable and reliable webhost to host and run your j2ee application check Vision J2ee Web Hosting services.

PHP SOLUTIONS: DYNAMIC WEB DESIGN MADE EASY What this chapter covers: Creating MySQL user accounts Creating a new database Defining a database table Choosing the right column type Using MySQL, MySQLI, and PDO to query a database When I first started working with databases, one of the greatest frustrations was that all the books and online tutorials I consulted assumed that you already knew the basics of database design and construction, or if you didn t that you planned to use Microsoft Access. MySQL is very different from Access, which is intended for use in small office environments. MySQL is not only fast and multiplatform; it s capable of handling a high number of simultaneous connections without any perceptible loss of performance. The differences between MySQL and Access also affect the way that you construct and interact with the database. After describing the basics of a database, I ll show you how to set up MySQL user accounts, create your first database, and connect to it with PHP. I ll also show you how to choose the correct data type to store each piece of information. How a database stores information MySQL is a relational database system. All the data is stored in tables, very much in the same way as in a spreadsheet, with information organized into rows and columns. Figure 11-1 shows the database table that you will build later in this chapter, as displayed in phpMyAdmin. Figure 11-1. Information in a database table is stored in rows and columns, just like in a spreadsheet. Each column has a name (image_id, filename, and caption) indicating what it stores. The rows aren t labeled, but the first column (image_id) contains a unique identifier known as a primary key, which can be used to identify the data associated with a particular row.
Note: In case you are looking for affordable and reliable webhost to host and run your j2ee application check Vision J2ee Web Hosting services.

11 GETTING STARTED WITH A DATABASE
We would like to recommend you tested and proved virtual web hosting services, which you will surely find to be of great quality.

SETTING UP MYSQL AND PHPMYADMIN 4. The Export section on the left of the screen lists all the tables in your database. Click Select All and leave the radio buttons on the default SQL. 5. If the database has never been transferred to the other server before, the only option that you need to set on the right side of the screen is the drop-down menu labeled SQL export compatibility. The setting depends on the version of MySQL on the other server (only the first two numbers, such as 3.23, 4.0, 4.1, or 5.0, are important): If the other server is running the same version of MySQL, choose NONE. If transferring between MySQL 4.1 and MySQL 5.0 (in either direction), choose NONE. If the other server is running MySQL 3.23, choose MYSQL323. If the other server is running MySQL 4.0, choose MYSQL40. 6. If the database has already been transferred on a previous occasion, select Add DROP TABLE in the Structure section. The existing contents of each table are dropped and are replaced with the data in the backup file. 7. Put a check mark in the box alongside Save as file at the bottom of the screen. The default setting in File name template is __DB__, which automatically gives the backup file the same name as your database. So, in this case, it will become phpsolutions.sql. If you add anything after the final double underscore, phpMyAdmin will add this to the name. For instance, you might want to indicate the date of the backup, so you could add 20070228 for a backup made on February 28, 2007. The file would then be named phpsolutions20070228.sql. Loading data from a backup file 1. Upload the SQL file to your remote server. (This isn t necessary if you are transferring data to a new installation of MySQL on your local computer.) 2. If a database of the same name doesn t already exist on the target server, create the database, but don t create any tables. 3. Launch the version of phpMyAdmin that is used by the target server and select the database that you plan to transfer the data to. Click the Import tab in the main frame. (On versions of phpMyAdmin earlier than 2.7.0, click the SQL tab instead.) 4. Use the Browse button to locate the SQL file and click Go. That s it! Looking ahead . . . Now that you have MySQL and phpMyAdmin installed, you re no doubt straining at the leash to get to work with your first database. Before you can do so, you need to set up at least one user account in MySQL and learn a little about how a database table is structured. That s what the next chapter is all about, but it won t be page after page of dull theory. By the end of the chapter, you will have built a simple database ready to start using with PHP and display dynamic data in your web pages. 283
If you are searching for cheap webhost for your web application, please visit MySQL5 Web Hosting services.

PHP SOLUTIONS: DYNAMIC WEB DESIGN MADE EASY Creating a backup If you have just installed MySQL for the first time, bookmark this section for when you need to upload files to your remote server or upgrade MySQL. If you re on a Mac, you must always back up your data before upgrading MySQL. Once the new version has been installed, you can transfer your data to the new server. Windows users need follow this procedure only when upgrading from one series to another, such as 5.0 to 5.1. 1. Launch phpMyAdmin and select the database that you want to back up from the drop-down menu in the navigation frame. 2. When the database details have loaded into the main frame, select Export from the tabs along the top of the screen, as shown here: 3. The rather fearsome looking screen shown in Figure 10-3 opens. In spite of all the options, you need to concern yourself with only a few. Figure 10-3. phpMyAdmin offers a wide range of choices when exporting data from MySQL.
If you are searching for cheap webhost for your web application, please visit MySQL5 Web Hosting services.

SETTING UP MYSQL AND PHPMYADMIN Figure 10-2. phpMyAdmin is a very user-friendly and stable graphical interface to MySQL. Logging out of phpMyAdmin If you opted to password protect phpMyAdmin, the Log out link is at the bottom left of the front page, just beneath Import (as shown in the screenshot). When you click the link, you are immediately prompted for your username and password. Click Cancel, and you will be presented with a screen informing you that you supplied the wrong username/password in other words, you have been logged out. Odd, but that s the way it works. You cannot log back in to phpMyAdmin from the wrong username/password screen. You must enter the original URL into the browser address bar first. 10 Backup and data transfer MySQL doesn t store your database in a single file that you can simply upload to your web- site. Even if you find the right files (on Windows, they re located in C:Program FilesMySQLMySQL Server 5.0data), you re likely to damage them unless the MySQL server is turned off. Anyway, most hosting companies won t permit you to upload the raw files because it would also involve shutting down their server, causing a great deal of inconvenience for everyone. Nevertheless, moving a database from one server to another is very easy. All it involves is creating a backup dump of the data and loading it into the other database with phpMyAdmin. The dump is a text file that contains all the necessary Structured Query Language (SQL) commands to populate an individual table or even an entire database elsewhere. phpMyAdmin can create backups of your entire MySQL server, individual databases, selected tables, or individual tables. To make things simple, the following instructions show you how to back up only a single database. 281
If you are searching for cheap webhost for your web application, please visit MySQL5 Web Hosting services.

PHP SOLUTIONS: DYNAMIC WEB DESIGN MADE EASY Configuring phpMyAdmin manually Although setup.php automates the creation of config.inc.php, it duplicates some default settings. If you strip out the unnecessary commands, you may find it quicker to create the file manually. 1. If you don’t need to password protect access to phpMyAdmin, type the following code into a blank document: Use your own MySQL root password in place of mysqlRootPassword. If you need password protection for phpMyAdmin, use the following code instead: 2. Save the file as config.inc.php in the main phpMyAdmin folder. Erm . . . that s it. Launching phpMyAdmin phpMyAdmin is a browser-based application, so you launch it by entering http://localhost/ phpMyAdmin/ in the address bar (on a Mac, use http://localhost/~username/phpMyAdmin/ if you put phpMyAdmin in your Sites folder). If you stored your root password in config.inc.php, phpMyAdmin should load right away, as shown in Figure 10-2. If you chose to password protect phpMyAdmin, enter root as the username and whatever you specified as the MySQL root password when prompted. If you get a message saying that the server is not responding or that the socket is not correctly configured, make sure that the MySQL server is running. The phpMyAdmin front page has a drop-down menu labeled Theme/Style. Currently, there are two options: Original and Darkblue/orange. Figure 10-2 shows the default Original style. However, all subsequent screenshots are taken using the Darkblue/orange style, because the tabbed interface fits better on the printed page. Choose whichever style you prefer.
If you are searching for cheap webhost for your web application, please visit MySQL5 Web Hosting services.

SETTING UP MYSQL AND PHPMYADMIN other hand, you see the following warning, it means that you have not set up the config folder correctly, and should go back to step 1. 5. Click the Add button in the Servers section. This loads a form with most of the necessary information already filled in. Check the following settings: Server hostname: localhost Server port: Leave blank unless your web server is running on a nonstandard port, such as 8080 Server socket: Leave blank Connection type: tcp PHP extension to use: mysqli 6. The default setting for Authentication type is config. If you don t need to password protect access to phpMyAdmin, check that User for config auth is set to root, and enter your MySQL root password in the next field, Password for config auth. If you want to restrict access to phpMyAdmin by prompting users for a password, change Authentication type to http, and delete root from the User for config auth field. 7. Scroll down to the Actions field and click Add. As shown here, there are two Add buttons close to each other. Click the one circled in the screenshot. 8. The next screen will probably warn you that you didn t set up a phpMyAdmin database, so won t be able to use all the phpMyAdmin features. This is not important. You can set one up later if you decide to use the advanced features of phpMyAdmin. 9. Scroll down to the Configuration section near the bottom of the page and click Save. 10. Open the config folder in Explorer or Finder. You should see a new file called config.inc.php. Move it to the main phpMyAdmin folder. The official instructions tell you to delete the config folder, but this isn t necessary in a local testing environment. 279
In case you need affordable webhost to host your website, our recommendation is ecommerce web host services.

PHP SOLUTIONS: DYNAMIC WEB DESIGN MADE EASY Configuring phpMyAdmin with setup.php 1. Create a new subfolder called config within the phpMyAdmin folder. Windows users skip to step 3. Mac users continue with step 2. 2. On Mac OS X, use Finder to locate the config folder that you have just created. Ctrl-click and select Get Info. In Ownership & Permissions, expand Details, and click the lock icon so that you can make changes to the settings. Change the setting for Others to Read & Write. Close the config Info panel. 3. Open a browser, and type the following into the address bar: http://localhost/phpmyadmin/scripts/setup.php If you created the phpMyAdmin folder inside your Sites folder on a Mac, use the following address, replacing username with your Mac username: http://localhost/~username/phpmyadmin/scripts/setup.php 4. You should see the page shown in Figure 10-1. Figure 10-1. A built-in script automates the configuration of phpMyAdmin. Ignore any warning about the connection not being secure. This is intended for server administrators installing phpMyAdmin on a live Internet server. If, on the
You want to have a cheap webhost for your apache application, then check apache web hosting services.

SETTING UP MYSQL AND PHPMYADMIN Using MySQL with a graphical interface Although you can do everything using MySQL monitor, it s a lot easier to use a graphic interface. There are several to choose from, both commercial and free. Among the free offerings are two from MySQL itself: MySQL Administrator and MySQL Query Browser (www.mysql.com/products/tools). Two other popular graphical front ends for MySQL are the commercial product, Navicat (www.navicat.com), and SQLyog (www.webyog.com), which is available in both commercial and free versions. However, the most popular graphical interface for MySQL is phpMyAdmin (www.phpmyadmin. net). It s a PHP-based administrative system for MySQL that has been around since 1998, and it constantly evolves to keep pace with MySQL developments. It works on Windows, Mac OS X, and Linux and currently supports all versions of MySQL from 3.23.32 to 5.0. What s more, many hosting companies provide it as the standard interface to MySQL. Because phpMyAdmin has a very intuitive interface, I suggest that you try it first. If you work with databases on a regular basis, you may want to explore the other graphical interfaces later. However, since phpMyAdmin is free, you have nothing to lose and you may find it does everything you want. Setting up phpMyAdmin on Windows and Mac Since phpMyAdmin is PHP-based, all that s needed to install it is download the files, unzip them to a website in your local testing environment, and create a simple configuration file. Downloading and installing phpMyAdmin 1. Go to www.phpmyadmin.net and download the latest stable version. The files can be downloaded in three types of compressed file: BZIP2, GZIP, and ZIP. Choose whichever format you have the decompression software for. 2. Unzip the downloaded file. It will extract the contents to a folder called phpMyAdmin-x.x.x, where x represents the version number. 3. Highlight the folder icon and cut it to your clipboard. On Windows, paste it inside the folder designated as your web server root (C:htdocs, if you followed my example). If you re on a Mac and want phpMyAdmin to be available to all users, put the folder in Macintosh HD:Library:WebServer:Documents, rather than in your own Sites folder. 4. Rename the folder you have just moved to this: phpMyAdmin. 5. Like Apache and PHP, phpMyAdmin uses a text file to store all the configuration details. Since version 2.7.0, you no longer edit the phpMyAdmin configuration file, but store your personal details in a new file, which should be named config.inc.php. There are two ways of doing this: using a built-in script called setup.php or manually. I prefer the manual method, but instructions for both methods follow. 277
Note: If you are looking for cheap and reliable webhost to host and run your mysql application check mysql web server services.