PHP and MySQL Basics Tutorial (i)

Source: Internet
Author: User
Tags empty sql mysql variables php and php and mysql first row variable
mysql| Basic Tutorials HTML and PHP, MySQL interaction

Why should I use a database?
World Wide Web (WWW) is more than just a place to provide information. If you have something, make a website, you can share it with people all over the world. But this is not a very easy thing to do. As the Web site becomes larger, you may experience problems like this:

The site contains so many things that visitors are not able to get what they want quickly. The problem is fatal to a website in a way.
The visitor wants to provide you with information, and the information must be saved for later use.
The above two problems, can be solved through the database!

In the world of WWW, databases are everywhere. As big as Yahoo!, Amazon, EBay, small to a simple message board, you can see the database in the arena. It can even be said that the database is the basis of all advanced applications.

Why use PHP and MYSQL
As I know, almost all of the major commercial web databases are based on SQL. The most popular of these may be Oracle. It's powerful, of course, and it's expensive. SQL is not an application, but a language, shorthand for Structured query Language (Structured Query language) to manipulate and query the database.

In recent years, some companies have developed "open code" SQL applications, the best-known of which may be MySQL. It's not just free, but it does not perform as well as Oracle for the average medium and small database applications.

To run MySQL on a Web site, you need a scripting language to interact with the database. In the past, Perl was the most popular. But now it looks like PHP is a little better. Don't ask me what's the difference between them? I used to use Perl and it worked very well, but now it seems like everyone likes to use PHP. Its popularity certainly has its reason.

The software you need
This part of the content, Chinabyte Network College earlier in the article has been introduced. Readers can refer to the article "setting up local PHP development for Win98". This is no longer detailed here.

HTML and PHP

Let's take a look at how PHP works. Take a look at the following code:

< html>

< body>

< PHP

Print "Hello, world."

?>

</body>


When this page is requested, it will display "Hello, World" in the browser.

As you can see, the PHP script is embedded in the HTML file. It with "<"? "Start, End with"?> ". More than that, we can even embed HTML tags in PHP scripts:

< PHP

Print "< html>";

Print "< body>";

Print "Hello, world."

Print "</body>";

Print "
?>

Two methods are the same, the effect is the same. But in some special cases, it's more convenient to choose one of them.
PHP's Prints statement

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 to display some text in a browser window, the Echo function is similar to print, but you can use the "," number to separate multiple items to display, which is more convenient when mixing string constants and variable display.

There is also a printf function to format the output of the number. You can use a number as an integer or a scientific notation to show it.

In these functions, the use of parentheses is different:

echo must not be with parentheses
printf must have
Print is optional
To display a string or a number is simple, just keep the variable name or constant behind the print statement. However, if you want to display an array, is it also written like this:

Print $myarray;

Its output will be "array", and PHP tells you $myarray is an array. This is useful when you are unsure whether a variable is an array, but 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 for the contents of the array. When the conversion is complete, the contents of the array are connected by delimiters to form a string:

$implodedarray = Implode ($myarray, ",");

Print $implodedarray;

You can also use the Array_walk function to implement the display of arrays. This function performs the same function operation on each of the contents of the array. For example:

function Printelement ($element)

{

Print ("$element < p>");

}

Array_walk ($myarray, "printelement");
How PHP sends data to MySQL

You should know more about HTML forms, and the following code is a very simple HTML form:

< html>

< body>

< form action=submitform.php3 Method=get>

Surname: < input type=text name=first_name size=25 maxlength=25>

Name: < input type=text name=last_name size=25 maxlength=25>

< p>

< input type=submit>

</form>

</body>


When you enter the data and press the Submit button, the form will send the data to the SUBMITFORM.PHP3. This PHP script is then processed to receive the data, the following is the SUBMITFORM.PHP3 code:

< 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 ("Thank you for filling in the registration form");

?>

</body>


The "username" and "password" in the third line of the code represent the account and password you logged into the MySQL database. The "dbname" in line fifth indicates the name of the MySQL database. The "tablename" in line 13th is the name of a data table in the database.

When you press submit, you can see that the name you entered is displayed on a new page. Take a look at the URL bar of the browser and it should be something like this:

.../submitform.php3?first_name=fred&last_name=flintstone

Because we use the form get method, the data is routed to submitform.php3 through the URL. Obviously, the Get method is limited, and when there is a lot of content to be passed, you can't use GET, only the POST method. But no matter what the method, when the data transfer is complete, PHP automatically creates a variable that is the same as their name (the form's Name property) for each field in the form.

PHP variables have all started with a dollar sign, so that in the process of SUBMITFORM.PHP3 script processing, there will be $first _name and $last _name These two variables, the contents of the variable is what you entered.

Let's check to see if the name you typed is actually entered into the database. To start MySQL, at the mysql> prompt, enter:

Mysql> SELECT * FROM TableName;

You should be able to get a table with the content that you just typed:

+------------+------------+

| first_name | last_name |

+------------+------------+

| Willow | such as wind

+------------+------------+

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);

These two function calls are used to open the MySQL database, the meaning of the specific parameters has just been said.

The following line executes an SQL statement:

mysql_query ("INSERT into TableName" (First_Name, Last_Name)

VALUES (' $first _name ', ' $last _name ')

");

The mysql_query function is used to execute an SQL query on the selected database. You can execute any SQL statement in the mysql_query function. The executed SQL statement must be enclosed in double quotes as a string, in which the variables are enclosed in single quotes.

There is one thing to note: MySQL's statement to use a semicolon (;) End, one line of PHP code is the same, but in the PHP script in the MySQL statement can not have a semicolon. In other words, when you enter the 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 the command appears in a PHP script, it's going to take away that semicolon. This is done because some statements, such as SELECT and INSERT, can work without semicolons. But there are a few statements, such as UPDATE, with a semicolon. In order to avoid trouble, it is good to remember this rule.

How PHP extracts data from MySQL

Now we create another HTML form to perform this task:

< html>

< body>

< form action=searchform.php3 Method=get>

Please enter your query content:

< p>

Surname: < 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>


Also, there is a PHP script to process this form, and we'll build a 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 records were found in our database. ";}

?>

</body>


When you enter the content you want to retrieve in the form, and then press the SUBMIT button, you enter a new page that lists all the matching search results. Let's take a look at how this script actually completes the search task.

The preceding statements, as mentioned above, first establish a database connection and then select the database and datasheet that are required for each database application. Then there are a few of these statements:

if ($first _name = "")

{$first _name = '% ';}

if ($last _name = "")

{$last _name = '% ';}

These lines check to see if each field in the form is empty. Note that the two equals sign, because the syntax of PHP is mostly derived from the C language, where the equal sign is used in the same way as C: An equal sign is an assignment number, and two equal signs represent the logic equals. It should also be noted that when the condition is true after if, the statement to be executed is placed in "{" and "}", and each statement is followed by a semicolon to indicate the end of the statement.

The percent semicolon is a wildcard character in the SQL language, and once you understand it, you should know what the two lines mean: If the "first_name" field is empty, then all the first_name will be listed. The two following sentences also mean the same thing.

$result = mysql_query ("SELECT * FROM TableName")

WHERE first_name like ' $first _name% '

and last_name like ' $last _name% ' "

");

This line completes most of the search work. When the mysql_query function completes a query, it returns an integer flag.

The query selects those first_name columns and $first _name variables from all records, and the last_name and $last _name variable values are placed in the staging Recordset, and the returned integer is used as a flag for the recordset.

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 records were found in our database. ";}

This is the last step, is the display part. The Mysql_fetch_array function first extracts the contents of the first row of the query result, which is displayed in the PRINT statement. The argument for this function is the integer flag returned by the mysql_query function. Once the Mysql_fetch_array is successfully executed, the recordset pointer is automatically moved down so that when the mysql_fetch_array is executed again, the contents of the next line of records are obtained.

The array variable $row created by the Mysql_fetch_array function and populated with the result fields of the query, each of which corresponds to each field of the query result.

If a match is found and the variable $row is not empty, the statement in curly braces is executed:

do {

Print $row ["first_name"];

Print ("");

Print $row ["last_name"];

Print ("< p>");

while ($row = Mysql_fetch_array ($result));

This is a do ... while loop. Unlike a while loop, it executes the loop body first, and then checks to see if the loop condition is satisfied. Since it is already known that the recordset is not empty, it must be done at least once, so you should use a do ... while instead of a while loop. In the curly braces is the loop body to execute:

Print $row ["Fir
How PHP extracts data from MySQL

Now we create another HTML form to perform this task:

< html>

< body>

< form action=searchform.php3 Method=get>

Please enter your query content:

< p>

Surname: < 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>


Also, there is a PHP script to process this form, and we'll build a 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 records were found in our database. ";}

?>

</body>


When you enter the content you want to retrieve in the form, and then press the SUBMIT button, you enter a new page that lists all the matching search results. Let's take a look at how this script actually completes the search task.

The preceding statements, as mentioned above, first establish a database connection and then select the database and datasheet that are required for each database application. Then there are a few of these statements:

if ($first _name = "")

{$first _name = '% ';}

if ($last _name = "")

{$last _name = '% ';}

These lines check to see if each field in the form is empty. Note that the two equals sign, because the syntax of PHP is mostly derived from the C language, where the equal sign is used in the same way as C: An equal sign is an assignment number, and two equal signs represent the logic equals. It should also be noted that when the condition is true after if, the statement to be executed is placed in "{" and "}", and each statement is followed by a semicolon to indicate the end of the statement.

The percent semicolon is a wildcard character in the SQL language, and once you understand it, you should know what the two lines mean: If the "first_name" field is empty, then all the first_name will be listed. The two following sentences also mean the same thing.

$result = mysql_query ("SELECT * FROM TableName")

WHERE first_name like ' $first _name% '

and last_name like ' $last _name% ' "

");

This line completes most of the search work. When the mysql_query function completes a query, it returns an integer flag.

The query selects those first_name columns and $first _name variables from all records, and the last_name and $last _name variable values are placed in the staging Recordset, and the returned integer is used as a flag for the recordset.

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 records were found in our database. ";}

This is the last step, is the display part. The Mysql_fetch_array function first extracts the contents of the first row of the query result, which is displayed in the PRINT statement. The argument for this function is the integer flag returned by the mysql_query function. Once the Mysql_fetch_array is successfully executed, the recordset pointer is automatically moved down so that when the mysql_fetch_array is executed again, the contents of the next line of records are obtained.

The array variable $row created by the Mysql_fetch_array function and populated with the result fields of the query, each of which corresponds to each field of the query result.

If a match is found and the variable $row is not empty, the statement in curly braces is executed:

do {

Print $row ["first_name"];

Print ("");

Print $row ["last_name"];

Print ("< p>");

while ($row = Mysql_fetch_array ($result));

This is a do ... while loop. Unlike a while loop, it executes the loop body first, and then checks to see if the loop condition is satisfied. Since it is already known that the recordset is not empty, it must be done at least once, so you should use a do ... while instead of a while loop. In the curly braces is the loop body to execute:

Print $row ["first_name"];

Print ("");

Print $row ["last_name"];

Print ("< p>");

Then you check to see if the while condition is satisfied. The Mysql_fetch_array function is called again and comes to the contents of the current record. This process loops, and when no next record exists, Mysql_fetch_array returns false, the loop ends, and the recordset is fully traversed.

The array returned by Mysql_fetch_array ($result) can be invoked not only with the field name, but also as a normal array, using subscripts to refer to the various components of the 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 in a compact way:

echo $row [0], "", $row [1], "< p>";

When no matching record is found, there is nothing in the $row, and then the ELSE clause of the IF statement is invoked:

else {print "Sorry, no records were found in our database. ";}
Check that the query is working correctly

Do your SELECT, DELETE, or other queries work correctly? This is a must to understand, and, do not easily jump to conclusions.

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 failure:</b>", mysql_error ();

Exit

}

But this method of checking does not work for a SELECT query, and should be done this way:

$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 succeeded. ";

}

ElseIf (mysql_num_rows ($selectresult) = 0)

{

The print select query failed. ";

Exit

}

And for the DELETE query, this should be the case:

$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 succeeded";

}

ElseIf (Mysql_affected_rows ($deleteresult)!= 1)

{

Print "DELETE query failed";

Exit

}




Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.