Examples of php mysql applications. Communication with MySQL databases. Database support in PHP
Using php...
Creating a database connection in PHP in different ways:
1) the old-fashioned way to connect to MySQL:
$conn=mysql_connect($db_hostname, $db_username, $db_password) or die ("No connection to the server");
mysql_select_db($db_database,$conn) or die ("No, it was not possible to connect to the database");
Explanations of the variables below.
The following functions are used:
- mysql_connect()- to connect to the server;
- mysql_select_db()- to connect to the database;
At the same time, we constantly check for errors in this way: or die (“The error is such and such”); - translated as or die with such and such an error - to immediately find where the error is.
config.php
// variables for connecting to the database
$host = "localhost"; /host
$username = "root"; // password for connecting to the database
$password = ""; // password for connecting to the database - on the local computer it can be empty.
$database_name = "my-dolgi"; // database name
// old way of connecting to the database
mysql_connect($host, $username, $password) or die("Can't connect create connection");
// select the database. If there is an error, output
mysql_select_db($database_name) or die(mysql_error());
index.php
require_once "config.php";
$result = mysql_query("SELECT Name, Money FROM Dolg ORDER BY Money DESC LIMIT 5") or die(mysql_error());
";
while ($row = mysql_fetch_assoc($result)) (
";
}
mysql_free_result($result);
// Close the connection
mysql_close();
2) A more progressive procedural style - connecting to the database using mysqli:
This method:
- more convenient;
- up to 40 times faster;
- increased security;
- there are new features and functions;
An example of connecting to a database in PHP with a selection from a table
config.php
// connections to the database
$link = mysqli_connect("localhost", "username", "password", "name-database"); // here we enter your data directly: user name, password and database name, the first field is usually localhost
// output connection error
if (!$link) (
echo "Error connecting to the database. Error code: " . mysqli_connect_error();
exit;
}
Please note - mysqli is used everywhere, not mysql!!!
index.php
require_once "config.php";
// Execute the request. If there is an error, we display it
if ($result = mysqli_query($link,"SELECT Name, Money FROM Debt ORDER BY Money DESC LIMIT 5")) (
Echo "To whom do I owe in descending order:
";
// Fetching query results
while ($row = mysqli_fetch_assoc($result)) (
echo $row["Name"] . "with debt". $row["Money"] . " rubles.
";
}
// freeing used memory
mysqli_free_result($result);
// Close the connection
mysqli_close($link);
}
As you can see, some points have changed (in italics).
3) Object-oriented method of connecting to a MySQL database - using methods and classes:
Cons: More complex and less susceptible to errors.
Pros: brevity and convenience for experienced programmers.
$conn = new mysqli($db_hostname, $db_username, $db_password, $db_database);
if($conn->connect_errno)(
die($conn->connect_error);
) else (echo "The connection to the database was successfully established";)
here, in principle, everything is intuitive:
- $db_hostname is host(mostly localhost),
- $db_database - db name;
- $db_username and $db_password - username and password respectively!
An example of connecting to a database in php OOP style with sampling from a table
config.php
// connections to the database
$mysqli = new mysqli("localhost", "username", "password", "name-database"); // here we enter your data directly: user name, password and database name, the first field is usually localhost
// output connection error
if ($mysqli->connect_error) (
die ("DB connection error: (" . $mysqli->connect_errno . ") " . mysqli_connect_error) ;
}
Please note - mysqli is used everywhere, not mysql!!! and unlike the previous method, arrows “->” appear, which indicate that this is an OOP style.
index.php
require_once "config.php";
// Execute the request. If there is an error, we display it
if ($result = $ mysqli->query("SELECT Name, Money FROM Debt ORDER BY Money DESC LIMIT 5")) (
Echo "To whom do I owe in descending order:
";
// Fetching query results
while ($row = $result-> fetch_assoc()) {
echo $row["Name"] . "with debt". $row["Money"] . " rubles.
";
}
// freeing used memory
$result->close();
// Close the connection
$mysqli->close();
}
Your task is to find the differences.
4) Communication with the database using PDO:
When connecting to a MySQL database, prepared expressions are used (using the prepare method) and as a result there is greater security and greatly increases performance.
config file from the previous method! - the same
index.php
// PDO style for communication with MySQL
if ($stmt = $mysqli->prepare("SELECT Name, Voney FROM Dolg ORDER BY Money< ? LIMIT 5")) {
$stmt->bind_param("i", $summa);
$summa = 100000;
//start execution
$stmt->execute();
// Declaring variables for prepared values
$stmt->bind_result($col1, $col2);
Echo "To whom do I owe in descending order:
";
// Fetching query results
while ($stmt->fetch()) (
echo $col1 . "with debt". $col2 . " rubles.
";
}
// freeing used memory
$stmt->close();
// Close the connection
$mysqli->close();
As you can see, this is much more complicated and you need to study PDO - this is a separate topic.
All I can say as a recommendation for using databases is that life without them is simply death! The database is a ray of exe light in the dark kingdom of data processing by an interpreted program. The database brings a little headache, but relieves much more.
The MySQL database server was taken as an example (I believe that once you master it, you will easily master others. Someday I will write about them, if I master it myself :).
The first conversation will be about PHP functions used to work with MySQL. So let's begin.
1. Database administration
Methods of administering a database in descending order of convenience:
- phpMyAdmin (highly recommend!)
- Write a script that would distort the database (see example)
- mysql.exe in the mysql package
- mysql_manager.exe (it seems to be somehow possible there, only on the verge of shamanism)
I especially recommend the first method. With it you don’t have to study ALTER TABLE, ADD COLUMN, etc. queries. I still don't know them. Moreover, “such questions, Comrade Ambassador, cannot be resolved at the drop of a hat” - when do you need to automatically change the structure of a database or table? A few words about the second method. This is a workaround technology, so to speak, that I used without knowing about phpMyAdmin and the mysqldump utility. The script contains commands that delete the database and create it again. It once helped, but in general, I’ll say it again, this is a bypass technology, a “support.”
For the future: if you have several sites that use a database, then at least create several databases within your home server. This will make the server's work easier and eliminate the possibility of table confusion. In general, the rules for working with a database are the same as with a website - keep it in a separate directory from others.
2. Connection to the database server
This is done using the mysql_connect function: $connect = mysql_connect(<хост>, <логин>, <пароль>); By default, on the mysql server in the user table there is a root user who can only have access from localhost, that is, from the same computer where the mysql server is installed. ATTENTION! "Have access from localhost" means that your PHP script has access, and you can access it from any other computer.
What happens when we call the mysql_connect function? When your script starts executing, php allocates space in its memory for information about it and its variables. Information about the executed script also stores information about connections to databases. The $connect variable is roughly a pointer to the place where this information is stored. This variable is exactly the same as the others - if you use functions, then you need to declare global variables to access it.
Why is the variable used at all? This is in case you need to use several database servers for work (or, for example, to ensure greater security you use different logins, which may have different privileges). In such cases, each request requires certainty about which channel, so to speak, the command is going through. But if you are using only one connection, you do not need to specify it in the request function parameters (more about them below) - php finds the first (and in this case the only) established connection and uses it.
The mechanism of operation of the database query functions is the same as that of the connection function: the parameters of the request and (if necessary) connection are passed to the function, and the result is written to a variable:
$result = mysql_db_query(string database, string query [, connection variable]);
$result = mysql_query(string query [, connection variable]);
ATTENTION! To use the mysql_query function, in which the database is not specified, you must first select the database to use:
mysql_select_db(string database);
Now we have a $result variable. This is a pointer to the result of the query. There are a number of table rows there. You can get these rows using the mysql_fetch_row and mysql_fetch_array functions:
Echo"
| ", $row["field1"], " | ", $row["field2"], " |
The mysql_fetch_array function outputs an array into the specified variable (in this case $row), the indexes of which are the names of the fields (and if you write table.field in the list of query fields, then the array index will be field). mysql_fetch_row produces an array whose indices are numbers starting from 0.
Which function is better to use? If you ask for an asterisk, i.e. all the fields of the table, and the fields need to be displayed in a certain sequence (when, for example, a table header is drawn), it is better to use mysql_fetch_array. If you request one, two or three fields, clearly knowing their sequence, you can do mysql_fetch_row - this will reduce the amount of program code.
These are DELETE and UPDATE commands. Such queries have the same “rights” as SELECT, so the command is sent to the server in the same way - mysql_query (mysql_db_query). But in this case the function does not return a result:
$result = mysql_query("SELECT * FROM sometable"); but mysql_query("DELETE FROM sometable WHERE id=...");
Accordingly, if we execute a select query and do not write the result to a variable, the data will not be stored anywhere.
5. Handling request errors
The latest error message can be obtained using the mysql_error function:
echo "Database error. MySQL writes:", mysql_error();
If the result of a function is written to a variable, you can check it:
$result = mysql_query($request); if (!$result) echo "Database error. MySQL writes:", mysql_error(); else(echo"
| ", $row["field1"], " | ", $row["field2"], " |
If we don’t write to a variable, then so be it.
I named my database "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 may have noticed, I put the number 3 in the number of fields, why? After all, we need to create two fields, one for the title and one for the text. The point is that a table in the database must have one additional field. For what? This field is a sequential identification number (that is, we are counting our rows in the table), so we get each row of the table its own unique number. This will be useful for finding the string we need in the database. This field is usually called id and set to it AUTO_INCREMENT(I’ll show you below where it is exhibited). AUTO_INCREMENT allows you to assign each line its own unique number, thus you will not find a record with the same number in the database table!
After filling in the name and number of fields, click “OK” and you will be taken to the field creation page. I confess to you that I don’t really understand what I’m doing when creating fields, but that doesn’t stop me from working with them. Let’s fill it in like this:
And fill in the two fields we need:
Why only two? Because the first id field will be filled in automatically due to AUTO_INCREMENT. Click "OK". Now in the “Overview” tab you can see the line we created in the table:
Well, we have learned how to create tables and rows through the phpmyadmin panel, this is already progress. By the way, I would like to note that the interface of this panel is intuitive, you can easily poke the buttons there and figure out what’s what
It's time to learn how to create, delete and update rows in created tables using PHP. In order for you to better perceive what I am telling you, we will write a small admin panel for the site, and as things go I will tell you what and how.
Outputting records from the database
Let's start with this, since when creating any project you need to clearly 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 in the code to this file). How will we withdraw? Well, first we need to connect to the database, after using the do while loop (Learning PHP - Loops) we will retrieve records from the database. Let's get started
Connecting to the database
How is the connection made? Attention to syntax:
mysql_select_db(Database name, mysql_connect(Server,DB username,DB user password));
The database name, server user and password are created by you or provided by your hoster. Using a local server as an example, you create some data yourself, or use already created ones. This is what the connections to the database I created will look like:
//CONNECT TO 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. Pay attention to the output syntax:
$result = mysql_query("SELECT What we pull out FROM table name in the database");
The first line allows us to specify which columns we need to extract and from which database.
The second line puts everything found into a variable...
ATTENTION There are a couple of points that I would like to clarify. Let's solve a few problems:
- We need to extract all fields from the table.
How to extract 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. The asterisk means I need to extract all fields from the table
- We need to extract only one field from the table
How to extract only the text field from a table? like this:
$result = mysql_query("SELECT text FROM page");
$myrow = mysql_fetch_array($result);
In order to extract not all fields, but only some, you need to list the required fields after SELECT, separated by commas.
- We need to extract fields not from the entire table, but only from one line
How to extract all fields from the first line? We know that the first line has an id equal to one, let's use this knowledge:
After SELECT, I explained, separated by commas, which fields needed to be extracted, then I added a new line WHERE (which means “where”) id is equal to 1. Thus, I extract the fields I need from the line where id is equal to one
Well, let's start creating the output of our articles? We will display only the headers, let's get started:
$myrow = mysql_fetch_array($result);
do
{
echo "".$myrow."
";
}
What have we done? We pulled two fields from the table, id and title. Next, we launched a do while loop (Learning PHP - Cycles) and created links, using the data that were extracted from 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:
Headers are output, now let's organize the output of full messages when the mouse clicks on a link with a title. In order to do this in one file, add if()() conditions:
//CONNECT TO DATABASE (DB)
$nameDB = "phptest";//Database name
$nameSERVER = "localhost";//Server
$nameUSER = "root";//Database user name
$passUSER = "";//DB user password
mysql_select_db($nameDB, mysql_connect($nameSERVER,$nameUSER,$passUSER));
//CONNECT TO DATABASE (DB)
//OUTPUT 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));
}
//OUTPUT HEADERS
//OUTPUT FULL TEXT
if(isset($_GET["id"]))
{
$myrow = mysql_fetch_array($result);
echo $myrow;
}
//OUTPUT FULL TEXT
?>
I added two conditions. We will see a list with headings 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 ADDING ENTRIES
if(isset($_GET["add"]))
{
echo "
}
//FORM ADDING ENTRIES
This form will appear only if the global variable $_GET["add"] exists, so somewhere at the bottom you need to insert a link to add a new article. This is best done in the header output code; you can also edit the condition for outputting our headers like this:
//OUTPUT 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";
}
//OUTPUT HEADERS
I edited the condition so that the list of headers would not appear when the form is displayed, this is what happened:
$result = mysql_query("INSERT INTO table name in the database (field 1 of the database, field 2 of the database) VALUES ("data 1","data 2")");
Now let's 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:
//ADDING ENTRIES
{
header("location: test.php");
exit;
}
//ADDING ENTRIES
The condition is the global variable $_POST, that is, if we filled out the form and clicked on the “Add post” button, our condition will work. The title field will contain data from the global variable $_POST, and the text field will contain $_POST. Next, the line header("location: test.php") will work. It allows you to redirect the user to another page, in this case this page will be test.php. 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 code for outputting the full text, let's edit it:
//OUTPUT 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";
}
//OUTPUT FULL TEXT
if(isset($_GET["edd"]))
{
$myrow = mysql_fetch_array($result);
Echo"
}
//FORM EDITING ENTRIES
I added this piece of code after the adding records form. This form is almost similar to the adding posts form. As you may have noticed, I redid the name attributes and added the value attribute. In this attribute I placed the data that I retrieved from the database. There is also an invisible field here. It is needed to send the record identifier to the file handler.
Now, in order to ensure that there is no list of headings on the screen when this form is displayed, let’s correct the condition:
//OUTPUT 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";
}
//OUTPUT HEADERS
It's time to learn how records in the database are edited. Here's the syntax:
Now let's write a handler. Here is a piece of code that I added immediately after the handler for adding records:
//EDITING ENTRIES
{
header("location: test.php");
exit;
}
//EDITING ENTRIES
The condition shows that the handler will only run if we clicked on the “edit post” button. Next comes updating the database. In the title field we will place 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. We then redirect the user back to the list of headers:
Removing records from the database
Well, the last thing left for us to study is deletion.. It is simpler than all the others, so pay attention to the syntax:
In order to delete some posts, you need to put a link somewhere with which the user can delete some post... let's place this link in the output of the full text of the post:
//OUTPUT 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";
}
//OUTPUT FULL TEXT
Result of work
Here's the full code for today's post:
//CONNECT TO DATABASE (DB)
$nameDB = "phptest";//Database name
$nameSERVER = "localhost";//Server
$nameUSER = "root";//Database user name
$passUSER = "";//DB user password
mysql_select_db($nameDB, mysql_connect($nameSERVER,$nameUSER,$passUSER));
//CONNECT TO DATABASE (DB)
//ADDING ENTRIES
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;
}
//ADDING ENTRIES
//EDITING ENTRIES
if(isset($_POST["title_post_edd"]) AND isset($_POST["text_post_edd"]))
{
$result = mysql_query("UPDATE page SET title="$_POST", text="$_POST" WHERE id="$_POST"");!}
header("location: test.php");
exit;
}
//EDITING ENTRIES
//DELETE ENTRIES
if(isset($_GET["del"]))
{
$result = mysql_query("DELETE FROM page WHERE id="$_GET"");
header("location: test.php");
exit;
}
//DELETE ENTRIES
//OUTPUT 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";
}
//OUTPUT HEADERS
//OUTPUT 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";
}
//OUTPUT FULL TEXT
//FORM ADDING ENTRIES
if(isset($_GET["add"]))
{
echo "
}
//FORM ADDING ENTRIES
//FORM EDITING ENTRIES
if(isset($_GET["edd"]))
{
$result = mysql_query("SELECT * FROM page WHERE id="$_GET"");
$myrow = mysql_fetch_array($result);
Echo"
}
//FORM EDITING ENTRIES
?>
Conclusion
The material turned out to be quite complex. But! You may have noticed that I only used the information that I gave earlier. This means that with this knowledge, you can create simple projects in PHP! If you have mastered all this, then you can proudly say that you are a novice programmer! Well, now you can start creating your first cms. If you have any questions, and most likely you do, ask, I will help in any way I can! Good luck to you, that's all for today!
P.S.: What is an mdf file? How to open it? How to open it? These and many questions can be answered at voprosi4ek.ru
MySQL is a type of relational database. MySQL is a server to which various users can connect.
When you connect to the Internet, do you enter your username and password, as well as the name of the server you are connecting to? When working with MySQL, the same system is used.
One more thing: what is a relational database? Relational means based on tables. Microsoft's famous spreadsheet editor, Excel, is actually a relational database editor.
Connecting to MySQL server
To connect to a MySQL server in PHP, use the mysqli_connect() function. This function takes three arguments: server name, username and password.
The mysqli_connect() function returns the connection identifier, it is stored in a variable and later used to work with databases.
MySQL server connection code:
$link = mysqli_connect("localhost", "root", "");
In this case, I'm working on a local machine on Denwere, so the hostname is localhost, the username is root, and there is no password.
The connection also needs to be closed after finishing working with MySQL. The mysqli_close() function is used to close the connection. Let's expand the example:
$link = mysqli_connect("localhost", "root", ""); if (!$link) die("Error"); mysqli_close($link);
Here we checked the connection identifier for truth; if there is something 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. If there are no errors, returns zero.
- mysqli_connect_error() - returns a description of the last connection error to the MySQL server.
The mysqli_get_host_info() function returns a string containing the type of connection being used.
Also note that using the define command, I saved all connection parameters as constants. When you write large projects and there are many files connecting 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.
Selecting a Database
A MySQL server can have multiple databases. First of all, we need to select the base we need to work with. In PHP, there is another parameter for this in the mysqli_connect() function - the database name.
I created it on my computer via phpMyAdmin with the name tester. Let's 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, without tables. Tables must be added to it separately. Now let's add a table to it using PHP.
Create a table
In the name of the MySQL databases, the SQL part stands for Structured Query Language, which translates as a structured query language. We will write queries in SQL and send them to the MySQL server from the PHP program.
To create a table we just need to issue the CREATE TABLE command. Let's create a table named users whose columns will store logins (login column) and passwords (password column) of users.
$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 called users that contains two columns login and password, both of VARCHAR(20) data type. We'll talk about data types later, for now I'll 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 request syntax 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 into a variable; it can be written directly as an argument to the mysql_query() function. It just makes the code more readable.
This script has one drawback - it does not 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 "Table not created."; mysqli_close($link);
If we run this script again, we will see a message in the browser: “The table has not been created.” The fact is that the table was created the first time it was launched, but it is impossible to create a table with the same name again. We are faced with an error situation, so it’s time to talk about error handling when working with MySQL.
Error Handling
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 line with its description. 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: “Table not created: Table “users” already exists.”
Deleting a table
So, now we have a table that we don’t need. It's time to learn how to drop tables from a 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 "Table deleted."; mysqli_close($link);
Results
So, we have mastered the basics of MySQL. What we learned to do:
- Connect to a MySQL database using the mysqli_connect() function.
- Close the connection to the MySQL server using the mysqli_close() function.
- Send SQL queries 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 for deleting 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.
Read the next lesson:
In this article, we will use examples to analyze such very important points in working with MySQL databases (DBs), such as fetching from a database, writing to a database, updating information in a database, and also deleting it from a database. All this will be done using four operators: SELECT, INSERT, UPDATE and DELETE, which will be discussed in this article.
So, if we need to extract all fields from the database, we use the following code.
$result = mysql_query("SELECT * FROM first_table",$db);
The asterisk means that you need to extract all fields from the table.
If you need to extract only some fields, for example, first and last name.
$result = mysql_query("SELECT name, last_name FROM first_table",$db);
name, last_name – fields with first and last names of users.
If you need to get exact data, for example, the last name of all users in the database with a certain 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, we need to get the identifier of all users with a certain first and last name (the first and last names will be entered in the $name and $last_name variables, respectively). For this we can use the following code.
If we need to get records where one of several conditions is met, for example, get the identifiers of all users whose first or last name meets 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 – extract only the first five results from the database.
These were small examples of samples 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. There is a special tool for this in PHP mysql_fetch_array().
We can place the result of the function in a variable, for example, the $myrow variable, which will store . As a function parameter mysql_fetch_array() the result of the function execution will be transmitted mysql_query(). All this will look like this.
$myrow = mysql_fetch_array($result);
Now we can access the elements of the associative array $myrow. 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","DB user","Password for accessing the database"); mysql_select_db("db_name", $db); /*Make a query to the database*/ $result = mysql_query("SELECT name FROM first_table WHERE id="$id"",$db); /*Convert the result into an array*/ $myrow = mysql_fetch_array($result); /*Display the result on the screen*/ echo $myrow["name"];
As you can see, everything is very simple and clear.
Well, 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 a 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 a 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.
To check the result you can use .
$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 $result variable is true, a message will be displayed that the record has been added. Otherwise, it will be displayed 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 a database. The syntax of 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 a 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 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 remove fields from the database. The syntax of 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 identifier $id from the db_name table. You can use the following code for this.
This concludes this article. The material is not complicated, but explaining it through text is quite difficult. Still, I think you understand the whole essence 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.