Using mysql in php. The basics of working with MySQL in PHP. Connecting to MySQL Database

MySQL is a type of relational database. MySQL is a server that various users can connect to.

When you connect to the Internet, you enter your username and password, as well as the name of the server to which you are connecting? The same system is used when working with MySQL.

Another point: what is a relational database? Relational means table-based. Microsoft's famous spreadsheet editor Excel is actually a relational database editor.

Connecting to MySQL Server

PHP uses the mysqli_connect () function to connect to a MySQL server. This function takes three arguments: server name, username and password.

The mysqli_connect () function returns the connection identifier, it is saved in a variable and later used to work with databases.

MySQL server connection code:

$ link = mysqli_connect ("localhost", "root", "");

In this case, I work for local computer on Denwere, so the hostname is localhost, the username is root, and there is no password.

You also need to close the connection after you finish working with MySQL. The mysqli_close () function is used to close the connection. Extending the example:

$ link = mysqli_connect ("localhost", "root", ""); if (! $ link) die ("Error"); mysqli_close ($ link);

Here we checked the connection identifier for truth, if something is wrong with our connection, then the program will not be executed, the die () function will stop its execution and display an error message in the browser.

Connection errors

The following functions are used to check the connection:

• mysqli_connect_errno () - Returns the error code of the last connection attempt. Returns zero if there are no errors.
• mysqli_connect_error () - Returns a description of the last error connecting to the MySQL server.

define ("HOST", "localhost"); define ("DB_USER", "root"); define ("DB_PASSWORD", ""); define ("DB", "tester"); $ link = mysqli_connect (HOST, DB_USER, DB_PASSWORD, DB); / * check connection * / if (mysqli_connect_errno ()) (printf ("Failed to connect:% s \ n", mysqli_connect_error ()); exit ();) else (printf ("Failed to connect:% s \ n", mysqli_get_host_info ($ link));)

Mysqli_get_host_info () returns a string containing the type of connection used.

Also, note that using the define command I saved all the connection parameters in constants. When you write large projects, and there will be many files connected to the MySQL server, it is convenient to store the connection parameters in a separate file and insert it using the include or require function.

Database selection

There can be multiple databases on a MySQL server. First of all, we need to choose the base we need for work. PHP has one more parameter for this in the mysqli_connect () function - the name of the database.

I created it on my computer via phpMyAdmin named tester. We connect to it:

$ link = mysqli_connect ("localhost", "root", "", "tester"); if (! $ link) die ("Error"); mysql_close ($ link);

So, we have chosen a database to work with. But as we know, a relational database consists of tables, and our database does not yet have tables. The database is created empty, no tables. Tables must be added to it separately. Let's add a table to it using PHP.

Create a table

In the name of MySQL databases, the SQL part stands for Structured Query Language, which translates as structured query language. On SQL language we will write queries and from the PHP program send them to the MySQL server.

To create a table, we just need to specify the CREATE TABLE command. Let's create a table named users in the columns of which the logins (the login column) and passwords (the password column) of the users will be stored.

$ query = "CREATE TABLE users (login VARCHAR (20), password VARCHAR (20))";

In this code, we have assigned the $ query variable a string of text that represents an SQL query. We create a table named users that contains two columns login and password, both of which have a VARCHAR (20) data type. We will talk about data types later, for now I will just note that VARCHAR (20) is a string with a maximum length of 20 characters.

To send our query to the MySQL server we use the PHP function mysqli_query (). This function returns a positive number if the operation was successful and false if an error occurred (the syntax of the request is incorrect or the program does not have permission to execute the request).

$ link = mysqli_connect ("localhost", "root", "", "tester"); if (! $ link) die ("Error"); $ query = "CREATE TABLE users (login VARCHAR (20), password VARCHAR (20))"; mysqli_query ($ query); mysqli_close ($ link);

The SQL query does not need to be written to a variable, it can be written directly as an argument to the mysql_query () function. It's just that the code looks more readable.

This script has one drawback - it doesn't output anything to the browser. Let's add a message:

$ link = mysqli_connect ("localhost", "root", "", "tester"); if (! $ link) die ("Error"); $ query = "CREATE TABLE users (login VARCHAR (20), password VARCHAR (20))"; if (mysqli_query ($ query)) echo "The table has been created."; else echo "The table was not created."; mysqli_close ($ link);

If we re-run this script for execution, we will see a message in the browser: "The table was not created." The fact is that the table was created at the first start, and it is impossible to recreate the table with the same name. We are faced with an error situation, so it's time to talk about error handling when working with MySQL.

Error processing

When debugging a program, we may need precise information about the error. When an error occurs in MySQL, the database server sets the error number and a string describing the error. PHP has special functions to access this data.

• mysqli_errno () - Returns the error number.
• mysqli_error () - Returns a string describing the error.

Now, let's add the mysql_error () function to our script:

$ link = mysql_connect ("localhost", "root", "", "tester"); if (! $ link) die ("Error"); $ query = "CREATE TABLE users (login VARCHAR (20), password VARCHAR (20))"; if (mysqli_query ($ query)) echo "The table has been created."; else echo "Table not created:" .mysqli_error (); mysqli_close ($ link);

Now our script will return the line to the browser: "The table is not created: Table" users "already exists".

Dropping a table

So, now we have a table we don't need. It's time to learn how to delete tables from the database.

To drop a table, use the DROP TABLE command followed by the table name.

$ link = mysqli_connect ("localhost", "root", "", "tester"); if (! $ link) die ("Error"); $ query = "DROP TABLE users"; if (! mysqli_query ($ query)) echo "Error while deleting table:" .mysqli_error (); else echo "The table has been deleted."; mysqli_close ($ link);

Outcomes

So, we have mastered the basics of MySQL. What we have learned to do:

• Connect to MySQL database using mysqli_connect () function.
• Close the connection to the MySQL server using the mysqli_close () function.
• Send SQL query s to the MySQL server using the mysqli_query () function.
• We learned the SQL query for creating a table: create table.
• We learned the SQL query to drop a table: drop table.
• We learned how to handle errors using the mysqli_errno () and mysqli_error () functions.

Then we'll take a closer look at MySQL data types.

We read the following lesson:

I named my base "phptest". After clicking on the "Create" button, you should be automatically transferred to the database you created.

Create a table in the database

There is nothing complicated in this procedure either. Let's create a table in which we will store the title of our articles and the text itself:

As you can see in the number of fields I put the number 3, why? After all, we need to create two fields, one for the title and the text. The point is that the table in the database must have one additional field. For what? This field is an alternate an identification number, (that is, we sort of count our rows in the table) so we get a unique number for each row of the table. This is useful for finding the required string in the database. This field is usually called id and is exposed to it AUTO_INCREMENT(I will show below where it is exhibited). AUTO_INCREMENT allows you to assign a unique number to each line, thus you will not find a record with the same number in the database table!

after filling in the name and the number of fields, press "OK" we get to the page for creating fields. I confess to you, I don't really understand what I am doing when creating fields, but this does not prevent me from working with them.Fill in like this:

And fill in the two fields we need:

Why only two? Because the first id field will be filled in automatically by AUTO_INCREMENT. Click "OK". Now in the "Overview" tab you can see the line we have created in the table:

Well, we learned how to create tables and rows through the phpmyadmin panel, this is already progress By the way, I want to note that the interface of this panel is intuitive, you can easily poke buttons there and figure out what's what

It's time to learn how to create delete and update rows in the created tables using php. In order for you to better perceive what I am telling you, we will write a small admin panel of the site, and on the way, I’ll tell you what and how.

Outputting records from the database

Let's start with this, because when creating a project, you need to visually see the process, and without output it is very difficult to do (I will work with the test.php file, do not be surprised when you see links to this file in the code). How will we withdraw? Well, first we need to connect to the database, after the do while loop (Learn PHP - Loops) we will pull the records from the database. Let's get started

• Database connection

How is the connection made? Attention to the syntax:

mysql_select_db (DB name, mysql_connect (Server, DB username, DB user password));

Database name, server user and password are created by you or provided by your hoster. For example local server, you create some data yourself, or use the already created ones. This is how the connections to the database I created will look like:

// CONNECTING TO THE DATABASE (DB)

The server, user and password were created by default (in this case, we can say that almost all the data is provided to us by the hoster). From my data, I only indicated the name of the database. By the way, this is php code, and therefore must be in special brackets ()

• Data output

We need to display data from the page table. Attention to the syntax of the output:

$ result = mysql_query ("SELECT To extract FROM table name in the database");

The first line allows us to specify which columns we need to pull out, and from which database.

The second line puts everything it finds into a variable ...

ATTENTION There are a couple of points that I would like to clarify. Let's solve several problems:

• We need to pull out all the fields from the table.

How do I pull all fields from the page table? like this:

$ result = mysql_query ("SELECT * FROM page");
$ myrow = mysql_fetch_array ($ result);

You may have noticed that I put an asterisk (*) after the SELECT. An asterisk means that I need to extract all fields from the table

• We only need to pull out one field from the table

How to pull only the text field from the table? like this:

$ result = mysql_query ("SELECT text FROM page");
$ myrow = mysql_fetch_array ($ result);

In order to pull out not all the fields, but only some, it is necessary to list the required fields after SELECT separated by commas.

• We need to pull fields not from all tables, but only from one line

How do I get all the fields out of the first row? We know that the first line has an id equal to one, let's use this knowledge:

After SELECT, I explained which fields need to be extracted, separated by commas, and then added a new line WHERE (which means "where") id is 1. Thus, I pull out the fields I need from the line where id is one

Well, let's start creating the output of our articles? We will only display headers, let's get started:

$ myrow = mysql_fetch_array ($ result);

do
{
echo "". $ myrow. "
";
}

What have we done? We pulled out two fields from the table, id and title. Next, we started the do while loop (Learning PHP - Loops) and formed links, using the data that were taken out of the database. The data is stored in the $ myrow variable, this variable is an array, the keys to the array are the names of our fields in the database. Here's what happened:

There is an output of headers, now let's organize the output of complete messages when you click on a link with a header. In order to do this in one file, add the if () () conditions:

// CONNECTING TO THE DATABASE (DB)
$ nameDB = "phptest"; // Database name
$ nameSERVER = "localhost"; // Server
$ nameUSER = "root"; // Database username
$ passUSER = ""; // Database user password
mysql_select_db ($ nameDB, mysql_connect ($ nameSERVER, $ nameUSER, $ passUSER));
// CONNECTING TO THE DATABASE (DB)

// DISPLAY HEADERS
if (! isset ($ _ GET ["id"]))
{
$ result = mysql_query ("SELECT id, title FROM page");
$ myrow = mysql_fetch_array ($ result);

do
{
echo "". $ myrow. "
";
}
while ($ myrow = mysql_fetch_array ($ result));
}
// DISPLAY HEADERS

// DISPLAY THE FULL TEXT
if (isset ($ _ GET ["id"]))
{

$ myrow = mysql_fetch_array ($ result);

echo $ myrow;
}
// DISPLAY THE FULL TEXT
?>

I added two conditions. We will see the list with headers only if the global variable $ _GET ["id"] does not exist. We will see the full text only if this variable exists. In this case, we will display only one record we need from the database. Here's what we got:

Now I think it's time to learn how to add new rows to the table.

Adding data to the database

Let's start adding by creating a form, here is a piece of code that needs to be added to the end of our file:

// FORM ADD RECORDS
if (isset ($ _ GET ["add"]))
{
echo "

";
}
// FORM ADD RECORDS

This form will appear only if there will be a global variable $ _GET ["add"], so where-thread at the bottom you need to insert a link to add a new article. The best way to do this is in the code for displaying headers, you can also edit the condition for displaying our headers like this:

// DISPLAY HEADERS
if (! isset ($ _ GET ["id"]) AND! isset ($ _ GET ["add"]))
{
$ result = mysql_query ("SELECT id, title FROM page");
$ myrow = mysql_fetch_array ($ result);

do
{
echo "". $ myrow. "
";
}
while ($ myrow = mysql_fetch_array ($ result));

echo "


Add post ";
}
// DISPLAY HEADERS

I edited the condition in such a way that the list of headers does not appear when the form is displayed, this is what happened:

$ result = mysql_query ("INSERT INTO database table name (DB field 1, DB field 2) VALUES (" data 1 "," data 2 ")");

Now we will write a handler for our mini admin panel and you will understand everything. Here is a piece of code that I posted right after connecting to the database:

// ADD RECORDS

{

header ("location: test.php");
exit;
}
// ADD RECORDS

The condition is the global variable $ _POST, that is, if we fill out the form, click on the "Add post" button, then our condition will be triggered. The title field will contain data from the $ _POST global variable, and the text field - $ _POST. Next, the header ("location: test.php") line will work. It allows you to redirect the user to another page, in this case test.php will be this page. And the line exit; interrupts the execution of other scripts. Here's what happened:

Editing data in the database

In order to edit a specific record in our database, we naturally need to determine which record needs to be edited ... I propose to display the edit button in the full text output code, let's edit it:

// DISPLAY THE FULL TEXT
if (isset ($ _ GET ["id"]))
{
$ result = mysql_query ("SELECT text FROM page WHERE id =" $ _ GET "");
$ myrow = mysql_fetch_array ($ result);

echo $ myrow;
echo "


Edit post ";
}
// DISPLAY THE FULL TEXT

if (isset ($ _ GET ["edd"]))
{

$ myrow = mysql_fetch_array ($ result);

Echo "

". $ myrow."

";
}
// FORM EDIT RECORDS

I added this piece of code after the add records form. This form is almost similar to the form for adding posts. As you can see, I have reworked the name attributes and added a value attribute. In this attribute, I put the data that I brought out from the database. There is also an invisible field here. It is needed in order to send the record identifier to the handler file.

Now, so that when this form is displayed on the screen there is no list of headers, we will correct the condition:

// DISPLAY HEADERS

{
$ result = mysql_query ("SELECT id, title FROM page");
$ myrow = mysql_fetch_array ($ result);

do
{
echo "". $ myrow. "
";
}
while ($ myrow = mysql_fetch_array ($ result));

echo "


Add post ";
}
// DISPLAY HEADERS

It's time to find out how the records in the database are edited. Here's the syntax:

Now let's write the handler. Here is a piece of code that I added right after the adding records handler:

// EDIT RECORDS

{

header ("location: test.php");
exit;
}
// EDIT RECORDS

By condition, it can be seen that the handler will be launched only if we clicked on the "edit post" button. Next comes the database update. In the title field we will put the value of the global variable $ _POST and in the text field - $ _POST. We will edit the line whose id is equal to the global variable $ _POST. Then we redirect the user back to the list of headers:

Deleting records from the database

Well, the last thing left for us to study is deletion .. It is simpler than everyone else, so pay attention to the syntax:

In order to delete some records, you need to put a link somewhere in the thread with which the user can delete some record ... let's place this link in the output of the full text of the post:

// DISPLAY THE FULL TEXT
if (isset ($ _ GET ["id"]))
{
$ result = mysql_query ("SELECT text FROM page WHERE id =" $ _ GET "");
$ myrow = mysql_fetch_array ($ result);

echo $ myrow;
echo "


Edit post ";
echo "
Delete post ";
}
// DISPLAY THE FULL TEXT

Result of work

Here is the complete code for today's post:

// CONNECTING TO THE DATABASE (DB)
$ nameDB = "phptest"; // Database name
$ nameSERVER = "localhost"; // Server
$ nameUSER = "root"; // Database username
$ passUSER = ""; // Database user password
mysql_select_db ($ nameDB, mysql_connect ($ nameSERVER, $ nameUSER, $ passUSER));
// CONNECTING TO THE DATABASE (DB)

// ADD RECORDS
if (isset ($ _ POST ["title_post"]) AND isset ($ _ POST ["text_post"]))
{
$ result = mysql_query ("INSERT INTO page (title, text) VALUES (" $ _POST "," $ _ POST ")");
header ("location: test.php");
exit;
}
// ADD RECORDS

// EDIT RECORDS
if (isset ($ _ POST ["title_post_edd"]) AND isset ($ _ POST ["text_post_edd"]))
{
$ result = mysql_query ("UPDATE page SET title =" (! LANG: $ _ POST", text="$_POST" WHERE id="$_POST"");!}
header ("location: test.php");
exit;
}
// EDIT RECORDS

// DELETE RECORDS
if (isset ($ _ GET ["del"]))
{
$ result = mysql_query ("DELETE FROM page WHERE id =" $ _ GET "");
header ("location: test.php");
exit;
}
// DELETE RECORDS

// DISPLAY HEADERS
if (! isset ($ _ GET ["id"]) AND! isset ($ _ GET ["add"]) AND! isset ($ _ GET ["edd"]))
{
$ result = mysql_query ("SELECT id, title FROM page");
$ myrow = mysql_fetch_array ($ result);

do
{
echo "". $ myrow. "
";
}
while ($ myrow = mysql_fetch_array ($ result));

echo "


Add post ";
}
// DISPLAY HEADERS

// DISPLAY THE FULL TEXT
if (isset ($ _ GET ["id"]))
{
$ result = mysql_query ("SELECT text FROM page WHERE id =" $ _ GET "");
$ myrow = mysql_fetch_array ($ result);

echo $ myrow;
echo "


Edit post ";
echo "
Delete post ";
}
// DISPLAY THE FULL TEXT

// FORM ADD RECORDS
if (isset ($ _ GET ["add"]))
{
echo "

";
}
// FORM ADD RECORDS

// FORM EDIT RECORDS
if (isset ($ _ GET ["edd"]))
{
$ result = mysql_query ("SELECT * FROM page WHERE id =" $ _ GET "");
$ myrow = mysql_fetch_array ($ result);

Echo "

". $ myrow."

";
}
// FORM EDIT RECORDS
?>

Conclusion

The material turned out to be quite complicated. But! You may have noticed that I only used the information that I gave earlier. This means that with this knowledge, you can create the simplest projects in php! If you have mastered all this, then you can proudly declare that you are a beginner programmer! Well, now you can start to create your first cms. If you have any questions, and most likely there are, ask, I will help you as much as I can! Good luck, I have everything for today!

P.S .: What is a mdf file? How to open it? How do I open it? These and many questions can be answered at voprosi4ek.ru

We learned how to connect to the MySQL server, choose a database to work with, learned the PHP function of sending queries to the MySQL server, learned two simple queries (creating and deleting a table), and learned how to close the connection.

We will now delve deeper into MySQL queries. So let's get started!

Creating a table - CREATE TABLE

Now we have an empty database, there are no tables in it. So let's create a table first. We already know how to do this from the first part.

Here is the script code that will create the plate we need:

$ link = mysqli_connect ("localhost", "root", "", "tester"); if (! $ link) die ("Error"); $ query = "CREATE TABLE users (login VARCHAR (20), password VARCHAR (20))"; if (mysqli_query ($ link, $ query)) echo "The table has been created."; else echo "Table not created:" .mysqli_error (); mysqli_close ($ link);

There are only two fields in our table: login and password. For now, we don't need it anymore, let's not complicate the process.

So, the table has been created.

Adding rows (records) to a table - INSERT

You can add a new row to a table using the insert SQL command. Here's an example:

$ link = mysqli_connect ("localhost", "root", "", "tester"); if (! $ link) die ("Error"); $ query = "INSERT INTO users (login, password) VALUE (" zeus "," pass123 ")"; if (mysqli_query ($ link, $ query)) echo "User added."; else echo "No user added:". mysqli_error (); mysqli_close ($ link);

The SQL query consists of the INSERT INTO command, the database name users, then the field names in parentheses, then the word VALUE, followed by the added values ​​in parentheses. Values ​​are enclosed in quotes.

The syntax for the request looks like this:

INSERT INTO table_name (column1, column2) VALUE ("x1", "x2")

The quotes in the second brackets are required.

Variables can be in place of values. Here's an example:

$ link = mysqli_connect ("localhost", "root", "", "tester"); if (! $ link) die ("Error"); $ login = "zeus"; $ password = "pass123"; $ query = "INSERT INTO users (login, password) VALUE (" $ login "," $ password ")"; if (mysqli_query ($ link, $ query)) echo "User added."; else echo "No user added:". mysqli_error (); mysqli_close ($ link);

Of course, this example makes little sense. It may be useful for beginners to hear that this is how logins and passwords that users provide during registration are recorded in the database. This data is stored in variables, then, after checking, is written to the database.

Exists quick way inserting multiple rows with one INSERT query:

INSERT INTO users (login, password) VALUE ("bob", "eee333"), ("Rooki", "12345"), ("magy", "olol88e8")

As you can see, the enumerated data are simply separated by commas.

So, using the INSERT command, we learned how to add records to a table. Move on.

Table View: SELECT Command

We now have a users table that has rows. The previous script can be run multiple times, and each time it will add a row to the table. Now we may not know how many rows we have in the table. And I want to know what we have written in it.

To retrieve data from a table, use the SQL SELECT command. The * sign means that we are requesting all data, then after the word FROM we write the name of the table from which we want to receive data.

Let's query all the data from the users table:

$ link = mysqli_connect ("localhost", "root", "", "tester"); if (! $ link) die ("Error"); $ query = "SELECT * FROM users"; $ result = mysqli_query ($ link, $ query); if (! $ result) echo "An error occured:". mysqli_error (); else echo "Data received"; mysqli_close ($ link);

The mysqli_query () function returned the identifier of the query result to us - we put it in a variable and in the future we will work with it using other PHP functions.

The number of records in the request

Let's figure out how many rows are in our query? I've run the script for adding a record to the table myself, I don’t remember how many times and now I don’t know how many rows there are in my table.

The mysqli_num_rows () function is used to determine the number of rows in the query result. This function is passed the identifier of the query result, and it will return the number of records.

$ link = mysqli_connect ("localhost", "root", "", "tester"); if (! $ link) die ("Error"); $ query = "SELECT * FROM users"; $ result = mysqli_query ($ link, $ query); if (! $ result) echo "An error occured:". mysqli_error (); else echo "Data received"; $ count = mysqli_num_rows ($ result); echo "Total rows in the table: $ count."; mysqli_close ($ link);

If we need to find out the number of records in a table, then the given method is not the most suitable one. Here we found out the number of records found in the query, but the number of records in the table is searched differently.

Number of records in table SELECT COUNT (*)

To find out the number of records in a table, you can use the SELECT COUNT (*) FROM table_name command.

$ link = mysqli_connect ("localhost", "root", ""); if (! $ link) die ("Error"); mysqli_select_db ("tester"); $ query = "SELECT * FROM users"; $ result = mysqli_query ($ link, $ query); if (! $ result) echo "An error occured:". mysqli_error (); else echo "Data received."; $ count = mysqli_fetch_row ($ result); echo "Total rows in the table: $ count."; mysqli_close ($ link);

Please note that here we have used a new PHP function mysqli_fetch_row () to get data. This function returns a row of the query result in the form of a simple array, in our case there is one field in the row and it has index 0.

Viewing the result of a query in a loop

After executing an SQL query with a SELECT command and obtaining the identifier of the query result, PHP creates an internal pointer in the result recordset. This pointer automatically moves to the next record after referring to the current record. This mechanism makes it very easy to loop through the result set of a SELECT query.

PHP has several functions that can be used to get an array consisting of its fields for each line of the resulting query. Let's take the mysqli_fetch_row () function as an example. This function is passed the request ID and returns an array. So in the loop the entire query result is scanned; upon reaching the end of the query result, the function will return false.

So, we query all the data from the users table (SELECT * FROM users).

"; while ($ row = mysqli_fetch_row ($ result)) (echo" Login: $ row. Password: $ row.
";) mysqli_close ($ link);

Mysqli_fetch_row () returns a simple array. In each iteration of the loop, we will receive an array with a row from the table, the fields of which we can access by specifying a numeric index.

The same can be done using the mysql_fetch_assoc () function, it returns an associative array.

$ link = mysqli_connect ("localhost", "root", ""); if (! $ link) die ("Error"); mysqli_select_db ("tester"); $ result = mysqli_query ($ link, "SELECT * FROM users"); if (! $ result) echo "An error occured:". mysqli_error (); else echo "Data received.
"; while ($ row = mysqli_fetch_assoc ($ result)) (echo" Login: $ row. Password: $ row.
";) mysqli_close ($ link);

There are also functions mysqli_fetch_array () - returns any type of array, and mysqli_fetch_object () - returns an object.

SELECT DISTINCT Query - Unique Field Values

Let's create a new table:

$ link = mysqli_connect ("localhost", "root", ""); if (! $ link) die ("Error"); mysqli_select_db ("tester"); // drop the existing table mysqli_query ($ link, "DROP TABLE users"); // create a new table $ query = "CREATE TABLE users (name VARCHAR (20), surname VARCHAR (20), age TINYINT UNSIGNED)"; if (mysqli_query ($ link, $ query)) echo "The table has been created.
"; else echo" Table not created: ". mysqli_error (); // function for adding records to the table function add_new_line ($ link, $ query) (if (! mysqli_query ($ link, $ query)) echo" User not added : ". mysqli_error ();) // add records add_new_line ($ link," INSERT INTO users (name, surname, age) VALUE ("Max", "Jayson", "33") "); add_new_line ($ link, "INSERT INTO users (name, surname, age) VALUE (" Bob "," Freeman "," 26 ")"); add_new_line ($ link, "INSERT INTO users (name, surname, age) VALUE (" Sara ", "Lopes", "65") "); add_new_line ($ link," INSERT INTO users (name, surname, age) VALUE ("Serg", "Pupin", "29") "); add_new_line ($ link," INSERT INTO users (name, surname, age) VALUE ("Serg", "Borman", "43") "); add_new_line ($ link," INSERT INTO users (name, surname, age) VALUE ("Max", " Lopes "," 21 ")"); // display the contents of the table in the browser $ result = mysqli_query ($ link, "SELECT * FROM users"); if (! $ Result) echo "An error occured:". Mysqli_error (); else echo "Data received.
"; while ($ row = mysqli_fetch_assoc ($ result)) (echo" First name: $ row. Last name: $ row. Age: $ row.
";) mysqli_close ($ link);

So, we have a new, more complex table with unique records. Now let's see how many names we have in the database.

$ link = mysqli_connect ("localhost", "root", ""); if (! $ link) die ("Error"); mysqli_select_db ("tester"); $ result = mysqli_query ($ link, "SELECT DISTINCT name FROM users"); echo "Total names:". mysqli_num_rows ($ result). "
"; echo" List of names:
"; while ($ name = mysqli_fetch_row ($ result)) (echo" $ name
";) mysqli_close ($ link);

The SQL query "SELECT DISTINCT name FROM users" returned a result with all unique names in our table. Each unique name on a new line in the query result.

Sorting the result - ORDER BY

By adding the ORDER BY command to the SQL query, we sort the query result in ascending order (numbers and letters alphabetically). Here's an example where you can compare a regular query and sorted by age (age field).

";) echo" Sort by age:
"; $ result = mysqli_query ($ link," SELECT * FROM users ORDER BY age "); while ($ line = mysqli_fetch_row ($ result)) (echo" First name: $ line. Last name: $ line. Age: $ line.
";) mysqli_close ($ link);

You can replace the age field in the ORDER BY command with the name field and see the result.

To sort the query result in reverse order, use the ORDER BY age DESC command.

Matching condition - WHERE

By adding the WHERE command to the SQL query, we will query only those records that match the condition. For example, let's make a request for people under 30 years old.

To do this, use the SQL query "SELECT * FROM users WHERE age

$ link = mysqli_connect ("localhost", "root", ""); if (! $ link) die ("Error"); mysqli_select_db ("tester"); echo "People under 30:
"; $ result = mysqli_query ($ link," SELECT * FROM users WHERE age<30"); while ($line = mysqli_fetch_row($result)) { echo "Имя: $line. Фамилия: $line. Возраст: $line.
";) mysqli_close ($ link);

We can also immediately sort the result in ascending order by age:
"SELECT * FROM users WHERE age<30 ORDER BY age ".

If we make the query "SELECT name FROM users WHERE age<30 ORDER BY age ", то в результате нам вернут только значения поля "name", но они также будут отсортированы по age.

We can query the values ​​of two fields: "SELECT name, age FROM users WHERE age

Now ask for all users named "Max".

$ link = mysqli_connect ("localhost", "root", ""); if (! $ link) die ("Error"); mysqli_select_db ("tester"); echo "All Max:
"; $ result = mysqli_query ($ link," SELECT * FROM users WHERE name = "Max" "); while ($ line = mysqli_fetch_row ($ result)) (echo" First name: $ line. Last name: $ line. Age: $ line.
";) mysqli_close ($ link);

And another example query - will only select names from the users table, everything except Max.

SELECT name FROM users WHERE name! = "Max"

That's all with the WHERE clause.

Limiting records - LIMIT

By adding the LIMIT command to the SQL query, we will limit the size of the result.

A query that prints out the first three records: "SELECT * FROM users LIMIT 3". Let's see how it works:

$ link = mysqli_connect ("localhost", "root", ""); if (! $ link) die ("Error"); mysqli_select_db ("tester"); echo "Table contents:
"; $ result = mysqli_query ($ link," SELECT * FROM users "); while ($ line = mysqli_fetch_row ($ result)) (echo" First name: $ line. Last name: $ line. Age: $ line.
";) echo"

The first three entries:
"; $ result = mysqli_query ($ link," SELECT * FROM users LIMIT 3 "); while ($ line = mysqli_fetch_row ($ result)) (echo" First name: $ line. Last name: $ line. Age: $ line.
";) echo"

Second three entries:
"; $ result = mysqli_query ($ link," SELECT * FROM users LIMIT 3, 3 "); while ($ line = mysqli_fetch_row ($ result)) (echo" First name: $ line. Last name: $ line. Age: $ line ...
";) mysqli_close ($ link);

Also here we used the query: "SELECT * FROM users LIMIT 3, 3". The second triplet indicates the offset in the query result.

Pattern Match - LIKE

The SQL language supports simple patterns. For this, the LIKE command is used and the pattern is specified using the% character.

Here is an example query that will return all records with names starting with the letter S.

SELECT * FROM users WHERE name LIKE "S%"

Testing the request:

$ link = mysqli_connect ("localhost", "root", ""); if (! $ link) die ("Error"); mysqli_select_db ("tester"); echo "Table contents:
"; $ result = mysqli_query ($ link," SELECT * FROM users "); while ($ line = mysqli_fetch_row ($ result)) (echo" First name: $ line. Last name: $ line. Age: $ line.
";) echo"

Names starting with S:
"; $ result = mysqli_query ($ link," SELECT * FROM users WHERE name LIKE "S%" "); while ($ line = mysqli_fetch_row ($ result)) (echo" First name: $ line. Last name: $ line. Age : $ line.
";) mysqli_close ($ link);

Here is an example query that will return all records with last names ending with the letter s.

SELECT * FROM users WHERE name LIKE "% s"

Matching the condition - IN

This query using the IN command will return only those rows that strictly match the condition.

For example, we are interested in people aged 21, 26 and 33.

SELECT * FROM users WHERE age IN (21,26,33)

Testing the request:

$ link = mysqli_connect ("localhost", "root", ""); if (! $ link) die ("Error"); mysqli_select_db ("tester"); echo "Table contents:
"; $ result = mysqli_query ($ link," SELECT * FROM users "); while ($ line = mysqli_fetch_row ($ result)) (echo" First name: $ line. Last name: $ line. Age: $ line.
";) echo"

People with the required ages (21, 26, 33):
"; $ result = mysqli_query ($ link," SELECT * FROM users WHERE age IN (21, 26, 33) "); while ($ line = mysqli_fetch_row ($ result)) (echo" First name: $ line. Last name: $ line. Age: $ line.
";) mysqli_close ($ link);

Maximum and minimum value in a column

Selects the maximum value of age in the users table.

SELECT max (age) FROM users

The following query selects data from the users table using the name and age fields, where age is the minimum value.

SELECT name, min (age) FROM users

Updating a record - UPDATE

Let Max Lopes set the age to 15 years. This is done with a MySQL query:

UPDATE users SET age = "15" WHERE name = "Max" AND surname = "Lopes"

Notice the new AND command (and - in English means "and") in the query. If we do not specify the surname, then the age of 15 years will be set for all Max in the table.

One query can update two or more fields in one line. This is done as follows:

UPDATE users SET age = "18", surname = "Coocker" WHERE id = "3"

Our table does not have an id field, so this query will not work on it. But we will definitely learn this field containing unique line numbers.

Delete record - DELETE

MySQL database query to delete record:

DELETE FROM users WHERE id = "10"

Again, there is no id field in our table. But we can remove from it all people under the age of 18.

DELETE FROM users WHERE age< "18"

DROP TABLE

MySQL database query that deletes the entire users table:

DROP TABLE users

Dropping a Column - ALTER TABLE ... DROP ...

Sometimes you may need to delete a column from a table, let's for example remove the age column from users:

ALTER TABLE users DROP age

This MySQL query deleted the column permanently and irrevocably.

Add Column - ALTER TABLE ... ADD ...

Sometimes you might want to add a column to an existing table, let's for example add the age column to the users table again:

ALTER TABLE users ADD age TINYINT UNSIGNED

Column rename - ALTER TABLE ... CHANGE ...

Sometimes it may be necessary to rename a column, for example rename the age column to vozrast. We do it like this:

ALTER TABLE users CHANGE age vozrast TINYINT UNSIGNED

This MySQL query renamed the age column to vozrast with the TINYINT UNSIGNED datatype.

Renaming a table - RENAME TABLE ... TO ...

Sometimes it may be necessary to rename a table:

RENAME TABLE users TO peoples

Dropping a Database - DROP DATABASE

This query can drop the database with the name tester:

DROP DATABASE tester

Creating a database - CREATE DATABASE

This query creates a database named tester:

CREATE DATABASE tester

This request works for me on Denver, but on the hosting it may not work if the database user does not have permission to perform the deletion.

Outcomes

So, in this part we got acquainted with queries to MySQL. Many of the requests we have considered are not often useful to us in the process of work, but you need to know them, since they will definitely come in handy in the process of developing scripts.

Some requests are usually made only from phpMyAdmin (creating and deleting databases for example).

In the work of sites, you usually need to add a record to a table, edit a record, or delete a record from a table.

The next step is to explore MySQL data types.

After installing and configuring the MySQL database, you can start writing PHP scripts to interact with the database. This article describes all the basic functions that allow you to transfer data back and forth from a website to a database.

It doesn't matter how simple or complex your scripts are, as long as they talk to the database, they start with the same few things:

1. Connecting to an installed MySQL database.
2. Using the USE command against the desired MySQL database.
3. Submitting SQL to the database.
4. Getting results.
5. Processing of results.

Steps 3, 4 and 5 will vary depending on the nature of the work being performed. The script that creates tables is slightly different from the script that searches existing tables. But the first two steps — connecting to MySQL and using the correct database — are always the same, regardless of the purpose of the script.

Connecting to MySQL Database

First, you need to tell your PHP script how to connect to the database. This process essentially tells PHP to do exactly what you did when you started with your MySQL command line client. To connect to the database, PHP will need to pass the following information: your database hostname, username, password, and database name.

To connect to the database, we will use PDO - PHP Data Objects. When using it, you can not be afraid, this is possible thanks to the prepared parameters, but more on that later.

It is important! If you find a lesson on the Internet where mysqli_connect or mysql_connect will be used - feel free to close it, as they wrote 10 years ago.

To create a connection, you need to create a new object of the PDO class. As arguments to the constructor, you need to pass a DSN - this is a string indicating the driver (in our case, mysql), host and database name. The second argument is the username (in our case, root). The third is the password (in our case, empty).

Before the lesson, I created a users database, and in it a data plate with the following structure:

CREATE TABLE `data` (` id` int (11) NOT NULL AUTO_INCREMENT, `name` varchar (32) DEFAULT NULL,` year` char (4) DEFAULT NULL, PRIMARY KEY (id)) ENGINE = InnoDB DEFAULT CHARSET = utf8 ; $ dbh = new \ PDO ("mysql: host = localhost; dbname = users;", "root", "");

The first step after connecting is to set the encoding:

$ dbh-> exec ("SET NAMES UTF8");

After that, we can execute requests. It looks like this:

$ stm = $ dbh-> prepare ("INSERT INTO data (` name`, `year`) VALUES (: name,: year)"); $ stm-> bindValue ("name", "Name"); $ stm-> bindValue ("year", "1703"); $ stm-> execute ();

First, we create a prepared request - it is not executed yet. Note that instead of values, we specified: name and: year - these are the parameters into which the values ​​specified in the next two lines will be substituted. At the end, we call execute () - in fact, execute the resulting request.

Let's run this script and take a look at what has appeared in the database.

Let's update the script a few more times and look at the database again.

Fetching from a database using PHP

Let's now read the data that we have written. The scheme is the same, only we prepare a SELECT query.

$ stm = $ dbh-> prepare ("SELECT * FROM` data` "); $ stm-> execute ();

The request has been fulfilled, but that's not all. Now you need to get the result. This is done like this:

$ allUsers = $ stm-> fetchAll (); var_dump ($ allUsers);

As a result, we get an array of these records:

Let's display them more beautifully, add some HTML.

$ stm = $ dbh-> prepare ("SELECT * FROM` data` "); $ stm-> execute (); $ allUsers = $ stm-> fetchAll (); ?>

id	Name	Year

Well, that's a completely different matter!

If you need to add some parameters in a SELECT query, then this is done in the same way:

$ stm = $ dbh-> prepare ("SELECT * FROM` data` WHERE id =: id "); $ stm-> bindValue ("id", 1); $ stm-> execute (); $ users = $ stm-> fetchAll (); var_dump ($ users);

Now only one user will return, matching the conditions of the request.

In this article, we will use examples to analyze such very important points in working with MySQL databases (DB), such as fetching from the database, writing to the database, updating information in the database, as well as deleting it from the database. All this will be done using four - SELECT, INSERT, UPDATE and DELETE statements, which will be discussed in this article.

So, if you need to pull out all the fields from the database, use the following code.

$ result = mysql_query ("SELECT * FROM first_table", $ db);

An asterisk means that you need to pull all the fields from the table.

If you need to pull out only some of the fields, for example, first and last name.

$ result = mysql_query ("SELECT name, last_name FROM first_table", $ db);

name, last_name - fields with names and surnames of users.

If you need to get exact data, for example, the last name of all users in the database with a specific name (the name will be entered into the $ name variable).

name = '$ name' - the name field is equal to the $ name variable.

In addition to one condition, we can also list several, for example, you need to get the identifier of all users with a specific first and last name (first and last name will be entered into the variables $ name and $ last_name, respectively). For this we can use the following code.

If we need to get records where one of several conditions is met, for example, to get the identifiers of all users whose first or last name matches those specified in the conditions.

If you need to sort the result by some parameters, for example, by name.

$ result = mysql_query ("SELECT * FROM first_table ORDER BY name", $ db);

ORDER BY name - sort by name.

If you need to sort in reverse order.

$ result = mysql_query ("SELECT * FROM first_table ORDER BY name DESC", $ db);

DESC - in reverse order.

If you need to pull only a certain number of fields from the database. For example, you need to pull out the first five fields.

$ result = mysql_query ("SELECT * FROM first_table ORDER BY id LIMIT 5", $ db);

LIMIT 5 - pull out only the first five results from the database.

These were small examples of a sample from the database. Now let's look at how to convert the resulting result into an array for further use, for example, to display the result on the screen. For this, PHP has a special mysql_fetch_array ().

We can put the result of the function execution into a variable, for example, the $ myrow variable, which will be stored in itself. As a function parameter mysql_fetch_array () the result of the function execution will be passed mysql_query ()... It will all look like this.

$ myrow = mysql_fetch_array ($ result);

We can now access the elements of the $ myrow associative array. As an example, let's look at code that displays the username with id = 1. The db_first database from the previous article will be used as the database.

/ * Connect to the database * / $ db = mysql_connect ("MySQL server", "Database user", "Password for accessing the database"); mysql_select_db ("db_name", $ db); / * We make a query to the database * / $ result = mysql_query ("SELECT name FROM first_table WHERE id =" $ id "", $ db); / * Convert the result to an array * / $ myrow = mysql_fetch_array ($ result); / * Print the result to the screen * / echo $ myrow ["name"];

As you can see, everything is very simple and straightforward.

Now let's move on to the next INSERT statement, which is responsible for adding information to the database.

Adding information to the database. INSERT statement

The INSERT statement is used to add information to the database. The code responsible for adding has the following syntax.

$ result = mysql_query ("INSERT INTO table (field 1, field 2, field N) VALUES (" value 1 "," value 2 "," value N ")");

For example, we need to add the first and last name of the new user to the first_table. You can use the following code for this.

$ result = mysql_query ("INSERT INTO first_table (name, last_name) VALUES (" $ name "," $ last_name ")");

Where, $ name and $ last_name are variables with the first and last name of the new user.

You can use to check the result.

$ result = mysql_query ("INSERT INTO first_table (name, last_name) VALUES (" $ name "," $ last_name ")"); if ($ result == "true") (echo "Record added successfully!";) else (echo "Record not added!";)

In other words, if the variable $ result gets true, then a message will be displayed that the record has been added. Otherwise, it will appear that the record has not been added to the database.

Updating information in the database. UPDATE statement

The UPDATE statement is used to update existing information in the database. The syntax for the mysql_query function in this case is as follows.

Now let's move on to an example. Let's say we need to change the first and last name for the user with the identifier $ id in the db_name table. You can use the following code for this.

Now let's move on to the final part of the article and take a look at the last DELETE statement, which is responsible for deleting information from the database.

Removing information from the database. DELETE statement

The DELETE statement is used to delete fields from the database. The syntax for the mysql_query () function in this case is as follows.

Now, as usual, let's move on to an example. Let's say we need to delete a user with $ id from the db_name table. You can use the following code for this.

This concludes this article. The material is not difficult, but it is rather difficult to explain it through the text. Still, I think you get the whole point of the above material. If you still have any questions about this article, you can always ask them in the comments.

That's all. Good luck and success in learning PHP and MySQL.