Build a database-driven website using PHP and MySQL 3_PHP Tutorial

Summary In this chapter, we will focus on learning how to use Structured Query Language (SQL) to work with the MySQL database. (2002-08-29 14:11:10) ----------------------------------------------- ------------------------------------------------ By Wing, Source: Linuxaid Second Chapter: Getting Started with MySQL Welcome back to this tutorial! In the previous chapter, we learned to install and configure PHP and MySQL. In this chapter, we will focus on learning how to use Structured Query Language (SQL) to work with the MySQL database. As briefly explained in the previous chapter of Introduction to Databases, PHP is a server-side scripting language. Through this language, you can add instructions to your Web page so that your Web serving software (may be Apache, Personal Web Server or any other software ) will be executed before sending these pages to the requesting browser. In that simple example, I showed how to insert the current date into the web page that accepts the request every time. It’s all pretty clear, but adding a database to the mix would really interest us. A database server (in our case, MySQL) is a program that stores large amounts of information in a format through which you can easily access the data using a scripting language like PHP. For example, you can use PHP to get a list of jokes in a database and display them to your Web site. In this example, the jokes are completely stored in the database. This has two benefits. First, you no longer need to write an HTML file for each of your jokes, you just need to write a PHP file to pull any joke from the database and display it; second, to add jokes to your Web site, just is to add jokes to the database. PHP code can automatically display new jokes when they are included in the list. Let’s use this example to see how data is stored in the database. A database contains one or several data tables (tables), each data table contains a list of things. For our joke database, we may initially need a data table named "jokes", which contains a list of jokes. Each data table in the database contains one or several data columns (columns) or data fields (fields). Going back to our example, our "jokes" data table might have two columns: the text of the joke and the date the joke was added to the database. Each joke stored in the data table is called a row. To understand all the terms mentioned here, you can look at the following picture: In addition to the two data columns of the joke text ("JokeText") and the date of addition ("JokeDate"), I also added a column called "ID" data column. The purpose of this data column is to assign a unique number to each joke so we can easily look it up and differentiate between the jokes. Let’s learn that there are three data columns and two rows in the above data table. Each row contains a joke's ID, its text, and the date it was added. After mastering these basic terms, we will start using MySQL. Logging in to MySQL The standard interface for a SQL database is to connect to the MySQL service software (installed in Chapter 1) and enter commands at the same time. To connect to the server, we need to use the MySQL client program. If you installed the MySQL service software yourself, whether you installed it under Windows or some version of Unix, you should have installed the client program in the same location as the service program. In Linux, this program is called mysql, and its location is the /usr/local/mysql/bin directory by default. Under Windows, this program is called mysql.exe, and its location is the C: mysql in directory by default. is a MySQL server you installed yourself (for example, you are working on your web host provider's MySQL server), then there are two ways to connect to the MySQL server. The first method is to use telnet to log in to your web host's server. on it and run mysql there. The second method is to download and install the MySQL client program from http://www.mysql.com/ (free for both Windows and Linux). Both methods work well, and your web host may support one or both (you'll need to ask). Whichever method you choose, no matter which system you are using, you should eventually execute the MySQL client program on a command line to connect to your MySQL server. You need to enter the following command: mysql -h -u -p Change into the hostname or IP address of the computer your MySQL server is running on. If you are running the client program on the same computer as the service, you can omit -h and use -h localhost directly. Should be your MySQL username. If you installed the MySQL server yourself, this should be root. If you are using your web hosting provider's MySQL service, this should be the MySQL user they specify for you. The parameter tells the program to prompt you for your password, which will be displayed immediately after you enter the command above. If you installed MySQL yourself, your password is the root password you selected in Chapter 1. If you are using your web hosting provider's MySQL service, this should be the MySQL password they gave you.After everything is entered, the MySQL client program will connect to the MySQL server and return you a MySQL command line: mysql> The ySQL server is actually connected to several databases (this allows a Web host to set up the same database for several users) a MySQL server). So your next step should be to choose a working database. First, get the list of databases on the current server. Type the following command (don’t forget the semicolon!) and hit Enter. mysql> SHOW DATABASES; L will show you a list of databases on the server. If this is a newly installed server (that is, you installed it yourself in Chapter 1). The list will look like this: +----------+ | Database | +----------+ | mysql | | test | +---------- -+ 2 rows in set (0.11 sec) The L server uses the first database called mysql to manage users, their passwords and permissions. We won't be concerned with this database for now; we will discuss it in a later chapter. The second one called test is a data module. You can delete this database, we won't use it in our tutorial (we will create some databases ourselves). Deleting something is called "dropping" in MySQL. To delete the test database, the correct command should be: mysql> DROP DATABASE test; After entering this command and hitting Enter, MySQL will delete the database and Return Query OK. Note that you will not be prompted with information such as "Are you sure?" So you must be very careful when entering commands in MySQL. As we've seen here, you can completely delete a database - including all the information in it - with just one command! Before going to the next step, let us first take a look at the MySQL command line connection. As we have already noticed, all commands in MySQL must end with a semicolon (;). If you forget this semicolon, MySQL will think that you have not finished typing your command and will let you continue typing on the next line: mysql> SHOW -> DATABASES; While waiting for you to enter the rest of the command, The prompt will change from mysql> to ->. This is useful for long commands, where you can enter your command over several lines. If you find that your command is wrong, you can completely cancel the current command (Translator's Note: refers to the command that has not been executed) and start from the beginning. To complete this job, you only need to enter c and press Enter: mysql> DROP DATABASEcmysql> L will completely ignore the command you just entered and return to the prompt to wait for your next command. When you want to exit the MySQL client program, you only need to enter quit or exit (these two commands are exactly the same). This is the only command that can be executed without ending in a semicolon. mysql> quitBye What is SQL? The commands we use to tell MySQL what to do in the process are actually part of a specification called Structured Query Language (SQL). Commands in SQL are also called queries (we will use these two terms interchangeably in this tutorial). A standard language for interacting with most databases, so even if you no longer use MySQL and switch to Microsoft SQL Server in the future, you will find that most of the commands are the same. You must understand the difference between SQL and MySQL. MySQL is the database service software you are using. SQL is the language you use to interact with your database. Set up a database Your web hosting provider has assigned you a database to work with. Please wait patiently for a moment, and we will continue to discuss the following issues with you later. If you are working on a MySQL server you installed yourself. You can easily create a database by executing the following command: mysql> CREATE DATABASE jokes; The name of the library is jokes, to be consistent with the examples we are working on. In fact, you can give your database any name you like. However, if you are working on a Web hosting provider's MySQL server, they may have already set up a database for you, and you will not be able to choose the name of the database. Now that we have a database, we need to tell MySQL that we want to use this database. The following command should not be difficult to remember: mysql> USE jokes; to start using your database. This database will be empty before you add a data table to it. Our first step should be to create a data table to hold our jokes. Create a data table The SQL commands encountered are very simple, but because data tables are relatively flexible, the commands to create them are much more complicated. The basic format for creating a data table is as follows: mysql> CREATE TABLE ( -> , -> , -> ... -> ); Now return Go to our example "Jokes" table. This table has three data columns: ID (a number), JokeText (the text of the joke), and JokeDate (the date it was added).The command to create this table should be like this: mysql> CREATE TABLE Jokes ( -> ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> JokeText TEXT, -> JokeDate DATE NOT NULL -> ); It seems complicated, right? Let's break it down: The first line is relatively simple; it says we want to create a new data table called Jokes. The second line explains that we need a data column called ID, and the type of this column should be an integer (INT). This line also defines some other information about this data column. First, this row is not allowed to be empty (NOT NULL). Second, if you do not specify a value for a column, MySQL will choose to use a value greater than the current maximum value (AUTO_INCREMENT). Finally, this data column is also the unique identifier of this data table, so all values ​​in this data column should be unique (PRIMARY KEY). The third line is very simple; it shows that we need a data column called JokeText, and the type of this column should be a text (TEXT). The fourth line defines our last column. The column name is JokeDate. The type of this column is date (DATE). This column cannot be empty (NOT NULL). Please note that when we enter SQL commands, the case is completely free, but in a MySQL service running under a Unix-based system, because we must be consistent with the directories and files in the MySQL data directory, when encountering database names and tables When naming, we must be case-sensitive. Otherwise, MySQL is completely case-insensitive, with one exception: table names, column names, and other names that appear multiple times in the same command must be spelled exactly the same

http://www.bkjia.com/PHPjc/532244.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/532244.htmlTechArticle Summary In this chapter, we will focus on learning how to use Structured Query Language (SQL) to work in MySQL database . (2002-08-29 14:11:10) ----------------------------------------------- -----...