Home >Backend Development >PHP Tutorial >PHP and MySQL basic tutorial (1)_PHP tutorial
PHP and MySQL basic tutorial (1)_PHP tutorial
- WBOYOriginal
- 2016-07-21 16:07:23835browse
Interaction between HTML, PHP and MySQL
Why use a database?
The World Wide Web (WWW) is more than just a place for information. If you have something, make a website and share it with people all over the world. However, this is not an easy task. As your website grows larger, you may encounter problems like this:
The website contains too many things, making it difficult for visitors to get what they want quickly. This problem is fatal to a website to some extent.
Visitors want to provide you with information, and this information must be saved for later use.
Both the above two problems can be solved through the database!
In the world of WWW, databases are everywhere. As large as Yahoo!, Amazon, eBay, or as small as a simple message board, you can see the use of databases. It can even be said that the database is the foundation of all advanced applications.
Why use PHP and MYSQL
As far as I know, almost all major commercial website databases are based on SQL. Probably the most popular of these is Oracle. It's powerful and, of course, expensive. SQL is not an application, but a language. It is the abbreviation of Structured Query Language (Structured Query Language), which is used to operate and query the database.
In recent years, a number of companies have developed "open code" SQL applications, perhaps the most famous of which is MySQL. It is not only free, but for general small and medium-sized database applications, its performance is not inferior to Oracle.
To run MySQL on a website, you need a scripting language to interact with the database. In the past, Perl was the most popular. But now it seems that PHP is better. Don't ask me what's the difference?? I used to use Perl and it worked fine, but now everyone seems to prefer PHP. There is certainly a reason for its popularity.
Required software
This part of the content has been introduced in a previous article by ChinaByte Network Academy. Readers can refer to the article "Setting up local PHP development for win98". No further details will be given here.
HTML and PHP
Author: Yangmei Compilation Number of clicks on this article: 398
Let’s take a look at how PHP works. Take a look at the following code:
< html>
< body>
< ?php
print "Hello, world. ";
?>
< /body>
< /html>
When this page is requested, it will be browsed "Hello, world" is displayed in the monitor.
As you can see, the PHP script is embedded in the HTML file. It starts with " < ? " and ends with " ?> ". Not only that, we can even embed HTML tags in PHP scripts:
< ?php
print "< html>";
print "< ; body>";
print "Hello, world.";
print "< /body>";
print "< /html>";
?>
The two methods achieve the same goal by different means, and the effect is the same. But in some special cases, it is more convenient to choose one of them.
PHP’s prints statement
Author: Yangmei compiled this article Number of clicks: 398
The simplest interaction between PHP and HTML is achieved through the print statement:
< ?php
print "Hello, world.";
?>
print is the simplest and most used function, used to display some text in the browser window , the echo function is similar to print, but you can use "," to separate multiple contents to be displayed, which is more convenient when displaying mixed string constants and variables.
There is also a printf function, used to format the output of numbers. You can display a number as an integer or in scientific notation.
In these functions, the use of parentheses is different:
echo must not have parentheses
printf must have
print but can None
To display a string or a number is very simple, just follow the variable name or constant after the print statement. However, if you want to display an array, should you also write it like this:
print $myarray;
The output result will be "Array", and PHP tells you that $myarray is an array. This has some use when you're not sure whether a variable is an array, but right now we want to see the contents of the array.
You can use the implode function to convert an array into a string. It contains two parameters, the first is the array variable name, and the second is the delimiter of the array content. When the conversion is complete, the contents of the array are linked by delimiters to form a string:
$implodedarray = implode ($myarray, ", ");
print $implodedarray;
You can also use the array_walk function to display the array. This function performs the same functional operation on each element of the array. For example:
function printelement ($element)
{
print ("$element< p>");
}
array_walk($myarray, "printelement");
How PHP sends data to MySQL
Author: Yangmei compiled this article Number of clicks: 398
You should be familiar with HTML forms, the following paragraph The code is a very simple HTML form:
< html>
< body>
< form action=submitform.php3 method=GET>
Last name: < input type=text name=first_name size=25 maxlength=25>
First name: < input type=text name=last_name size=25 maxlength=25>
< p>
< input type=submit>
< /form>
< /body>
< /html> ;
When you enter data and press the submit button, this form will send the data to submitform.php3. This PHP script then processes the received data. The following is the code of submitform.php3:
< html>
< body>
< ?php
mysql_connect (localhost, username, password);
mysql_select_db (dbname);
mysql_query ("INSERT INTO tablename (first_name, last_name)
VALUES ('$first_name', '$last_name')
");
print ($first_name);
print (" ");
print ($last_name);
print ("< p>");
print ("Thanks for filling out the registration form");
?>
< /body>
< /html>
The "username" and "password" in the third line of the code represent your login respectively. MySQL database account and password. "dbname" in the fifth line represents the name of the MySQL database. "tablename" in line 13 is the name of a table in the database.
When you press submit, you can see the name you entered displayed on a new page. Take another look at the URL bar of the browser. Its content should be like this:
… /submitform.php3?first_name=Fred&last_name=Flintstone
Because we are using the form GET method, so the data is passed to submitform.php3 via the URL. Obviously, the GET method has limitations. When there is a lot of content to be transferred, GET cannot be used and only the POST method can be used.But no matter what method is used, when the data transfer is completed, PHP automatically creates a variable for each field in the form that is the same as their name (name attribute of the form).
PHP variables all start with a dollar sign. In this way, during the processing of the submitform.php3 script, there will be two variables $first_name and $last_name. The content of the variables is what you input. content.
Let’s check whether the name you entered has actually been entered into the database. Start MySQL, enter at the mysql> prompt:
mysql> select * from tablename;
You should be able to get a table with the content you just entered:
+----------------+------------+
| first_name | last_name |
+---- --------+----------------+
| Liu| Rufeng
+---------- --+----------------+
1 rows in set (0.00 sec)
Let’s analyze how submitform.php3 works:
The first two lines of the script are:
mysql_connect (localhost, username, password);
mysql_select_db (dbname);
This Two function calls are used to open the MySQL database. The meaning of the specific parameters has just been mentioned.
The following line executes a SQL statement:
mysql_query ("INSERT INTO tablename (first_name, last_name)
VALUES ('$first_name', '$last_name')
");
The mysql_query function is used to execute a SQL query on the selected database. You can execute any SQL statement in the mysql_query function. The SQL statement to be executed must be enclosed in double quotes as a string, and variables within it must be enclosed in single quotes.
There is one thing to note: MySQL statements must end with a semicolon (;). The same is true for a line of PHP code, but MySQL statements in PHP scripts cannot have semicolons. That is, when you enter a MySQL command at the mysql> prompt, you should add a semicolon:
INSERT INTO tablename (first_name, last_name)
VALUES ('$first_name' , '$last_name');
But if this command appears in a PHP script, the semicolon must be removed. The reason for this is that some statements, such as SELECT and INSERT, work with or without semicolons. But there are some statements, such as UPDATE, which won't work if you add a semicolon. To avoid trouble, just remember this rule.
How to extract data from MySQL in PHP
Now we create another HTML form to perform this task:
< html>
< body> ;
< form action=searchform.php3 method=GET>
Please enter your query content:
< p>
Last name: < input type=text name=first_name size=25 maxlength=25>
< p>
First name: < input type=text name=last_name size=25 maxlength=25>
< p>
< input type=submit>
< /form>
< /body>
< /html>
Similarly, there is a php script to process this form. We create another searchform.php3 file:
< html>
< body>
< ?php
mysql_connect (localhost, username, password);
mysql_select_db (dbname);
if ($first_name == "")
{$first_name = '%';}
if ($last_name == "")
{$last_name = '%';}
$result = mysql_query ("SELECT * FROM tablename
WHERE first_name LIKE '$first_name%'
AND last_name LIKE '$last_name%'
");
if ($row = mysql_fetch_array($result)) {
do {
print $row["first_name"];
print (" ");
print $row["last_name"];
print ("< p>");
} while($row = mysql_fetch_array($result));
} else {print "Sorry, no matching record was found in our database. ";}
?>
< /body>
< /html>
When you enter the content you want to retrieve in the form content, and then press the SUBMIT button, you will enter a new page, which lists all matching search results. Let’s take a look at how this script completes the search task. Several statements are the same as mentioned above. First, establish a database connection, and then select the database and data table. These are necessary for every database application. Then there are several statements like this:
if ($ first_name == "")
{$first_name = '%';}
if ($last_name == "")
{$last_name = '%'; }
These lines are used to check whether each field of the form is empty. Pay attention to the two equal signs, because most of PHP's syntax is derived from the C language, and the usage of the equal signs here is also the same as that of C. : One equal sign is an assignment sign, and two equal signs represent logical equality.It should also be noted that when the condition after IF is true, the subsequent statements to be executed are placed in "{" and "}", and a semicolon must be added after each statement to indicate the end of the statement.
The percent sign % is a wildcard character in SQL language. After understanding a little bit, you should know the meaning of these two lines: if the "FIRST_NAME" field is empty, then all FIRST_NAME will be listed. The next two sentences have the same meaning.
$result = mysql_query ("SELECT * FROM tablename
WHERE first_name LIKE '$first_name%'
AND last_name LIKE '$last_name%'"
");
This line does most of the work of searching. When the mysql_query function completes a query, it returns an integer flag.
The query selects those first_names from all records Records whose columns are the same as the $first_name variable, and whose last_name column and $last_name variable values are also the same, are put into the temporary records set, and the returned integer is used as the mark of this records set.
if ($row = mysql_fetch_array($result)) {
do {
print $row["first_name"];
print (" ");
print $ row["last_name"];
print ("< p>");
} while($row = mysql_fetch_array($result));
} else {print "Sorry, no matching record was found in our database. ";}
This is the last step, which is the display part. The mysql_fetch_array function first extracts the content of the first line of the query result, and then displays it using the PRINT statement. The parameter of this function is what the mysql_query function returns. Integer flag. After mysql_fetch_array is executed successfully, the record set pointer will automatically move down, so that when mysql_fetch_array is executed again, the content of the next row of records will be obtained. The array variable $row is created and used by the mysql_fetch_array function. The result fields of the query are filled in. Each component of the array corresponds to each field of the query result. If a matching record is found, the variable $row will not be empty, and the curly brackets will be executed. Statement:
do {
print $row["first_name"];
print (" ");
print $row["last_name" "];
print ("< p>");
} while($row = mysql_fetch_array($result));
This is a do … while Loop. Different from the while loop, it executes the loop body once, and then checks whether the loop condition is satisfied. Since it is known that the loop body must be executed at least once when the record set is not empty, so it should be executed. What is used is do ... while instead of while loop. The loop body to be executed is in the curly braces:
print $row["fir
PHP How to extract data from MySQL
Author: Yangmei Compilation Number of clicks for this article: 398
Now we create another HTML form to perform this task:
< html>
< body>
< form action=searchform.php3 method=GET>
Please enter your query content:
< p>
Last name: < input type =text name=first_name size=25 maxlength=25>
< p>
Name: < input type=text name=last_name size=25 maxlength=25>
< p>
< input type=submit>
< /form>
< /body>
< / html>
< ?php
mysql_connect (localhost, username, password);
mysql_select_db (dbname);
if ( $first_name == "")
{$first_name = '%';}
if ($last_name == "")
{$last_name = '%' ;}
$result = mysql_query ("SELECT * FROM tablename
WHERE first_name LIKE '$first_name%'
AND last_name LIKE '$last_name%'
");
if ($row = mysql_fetch_array($result)) {
do {
print $row["first_name"];
print (" ");
print $row["last_name"];
print ("< p>");
} while($row = mysql_fetch_array($result));
} else {print "Sorry, no matching record was found in our database. ";}
?>
< /body>
< /html>
When you enter the content you want to retrieve in the form content, and then press the SUBMIT button, you will enter a new page, which lists all matching search results. Let’s take a look at how this script completes the search task. Several statements are the same as mentioned above. First, establish a database connection, and then select the database and data table. These are necessary for every database application.Then there are several statements like this:
if ($first_name == "")
{$first_name = '%';}
if ($last_name == "")
{$last_name = '%';}
These lines are used to check whether each field of the form is empty. Pay attention to the two equal signs, because most of PHP's syntax is derived from the C language, and the usage of the equal sign here is also the same as in C: one equal sign is an assignment sign, and two equal signs represent logical equality. It should also be noted that when the condition after IF is true, the subsequent statements to be executed are placed in "{" and "}", and a semicolon must be added after each statement to indicate the end of the statement.
The percent sign % is a wildcard character in SQL language. After understanding a little bit, you should know the meaning of these two lines: if the "FIRST_NAME" field is empty, then all FIRST_NAME will be listed. The next two sentences have the same meaning.
$result = mysql_query ("SELECT * FROM tablename
WHERE first_name LIKE '$first_name%'
AND last_name LIKE '$last_name%'"
");
This line does most of the work of searching. When the mysql_query function completes a query, it returns an integer flag.
The query selects those first_names from all records Records whose columns are the same as the $first_name variable, and whose last_name column and $last_name variable values are also the same, are put into the temporary records set, and the returned integer is used as the mark of this records set.
if ($row = mysql_fetch_array($result)) {
do {
print $row["first_name"];
print (" ");
print $ row["last_name"];
print ("< p>");
} while($row = mysql_fetch_array($result));
} else {print "Sorry, no matching record was found in our database. ";}
This is the last step, which is the display part. The mysql_fetch_array function first extracts the content of the first line of the query result, and then displays it using the PRINT statement. The parameter of this function is what the mysql_query function returns. Integer flag. After mysql_fetch_array is executed successfully, the record set pointer will automatically move down, so that when mysql_fetch_array is executed again, the content of the next row of records will be obtained. The array variable $row is created and used by the mysql_fetch_array function. The result fields of the query are filled in. Each component of the array corresponds to each field of the query result. If a matching record is found, the variable $row will not be empty, and the curly brackets will be executed. Statement:
do {
print $row["first_name"];
print (" ");
print $row["last_name" "];
print ("< p>");
} while($row = mysql_fetch_array($result));
This is a do … while Loop. Different from the while loop, it executes the loop body once, and then checks whether the loop condition is satisfied. Since it is known that the loop body must be executed at least once when the record set is not empty, so it should be executed. What is used is do ... while instead of while loop. The loop body to be executed is in the curly braces:
print $row["first_name"];
print (" " );
print $row["last_name"];
print ("< p>");
Then check whether the while condition is met again. is called to get the contents of the current record. This process keeps looping. When no next record exists, mysql_fetch_array returns false, the loop ends, and the record set is completely traversed.
mysql_fetch_array($ result), not only can be called with field names, but also can be referenced by subscripts like a normal array. In this way, the above code can also be written like this:
print $. row[0];
print (" ");
print $row[1];
print ("< p>");
We can also use the echo function to write these four statements more compactly:
echo $row[0], " ", $row[1], "< p>";
When no matching record is found, there will be no content in $row, and the else clause of the if statement will be called:
else {print "Sorry, no more No matching records were found in our database. ";}
Check whether the query is working normally
Author: Yangmei compiled this article Number of clicks: 398
Are your SELECT, DELETE or other queries working properly? This is a must. Be clear, and don’t jump to conclusions too easily.
Checking an INSERT query is relatively simple:
$result = mysql_query ("INSERT INTO tablename (first_name, last_name)
VALUES ('$first_name', '$ last_name')
");
if(!$result)
{
echo "< b>INSERT Query failed: < /b> ", mysql_error();
exit;
}
But this check method does not work for SELECT query. At this time , should be done like this:
$selectresult = mysql_query ("SELECT * FROM tablename
WHERE first_name = '$first_name'
AND last_name = '$last_name'
");
if (mysql_num_rows($selectresult) == 1)
{
print "SELECT query successful";
}
elseif (mysql_num_rows($selectresult) == 0)
{
print "SELECT query failed. ";
exit;
}
For DELETE query, it should be like this:
$deleteresult = mysql_query ("DELETE FROM tablename
WHERE first_name = '$first_name'
AND last_name = '$last_name'
");
if (mysql_affected_rows($deleteresult) == 1)
{
print "DELETE query successful";
}
elseif (mysql_affected_rows($deleteresult) != 1)
{
print "DELETE query failed";
exit;
}