Php connection to mysql and data output. Creating a connection to a MySQL database in different ways
WORKING WITH A MySQL DATABASE WITH PHP TOOLS
Lecture. Prepared by Prokhorov V.S.
1. CONNECTING PHP SCENARIOS with MySQL tables
Let's look at the most commonly used functions that allow you to work with a MySQL database using PHP.
When PHP and MySQL interact, the program interacts with the DBMS through a set of functions.
1.1 Connection to the server. Functionmysql_connect
Before working with the database, you must establish a network connection with it, as well as authorize the user. The mysql_connect() function is used for this.
resource mysql_connect(]])
This function establishes a network connection to the MySQL database located on the host $server (by default this is localhost, i.e. the current computer) and returns the ID of the open connection. All further work is carried out with this identifier. All other functions that take this identifier (handle) as an argument will uniquely identify the selected database. When registering, specify the username $username and password $password (by default, the username from which the current process is running - when debugging scripts: root, and an empty password):
$dbpasswd = ""; //Password
//Display a warning
echo("
");
The $dblocation, $dbuser and $dbpasswd variables store the server name, username and password.
1.2 Disconnection from the server. Functionmysql_close
The connection to the MySQL server will be automatically closed when the script completes, or when the mysql_close function is called
bool mysql_close()
This function closes the connection to the MySQL server, and returns true if the operation is successful and false otherwise. The function takes as an argument the database connection handle returned by the mysql_connect function.
$dblocation = "localhost"; //Server name
$dbuser = "root"; //Username
$dbpasswd = ""; //Password
//Connect to the database server
//Suppress error output with the @ symbol before calling the function
$dbcnx = @ mysql_connect($dblocation, $dbuser, $dbpasswd);
if (!$dbcnx) //If the descriptor is 0, the connection is not established
//Display a warning
echo("
The database server is currently unavailable, so the page cannot be displayed correctly.");
if (mysql_close($dbcnx)) //close the connection
echo("Database connection terminated");
echo("We were unable to complete the connection");
1.3 Creating a database. CREATE DATABASE function
The command - create a database is available only to the server administrator, and on most hosting sites it cannot be executed:
CREATE DATABASEDatabaseName
Creates a new database named DatabaseName.
An example of working with this function:
@mysql_query("CREATE DATABASE $dbname");
It is recommended to use apostrophes (“SQL – command”) everywhere as delimiters for lines containing SQL – commands. This ensures that no $ variable is accidentally interpolated (i.e. replaced by its value), and increases script security.
The CREATE DATABASE database creation command is available only to the superuser, and on most hosting sites it is impossible for a simple user to execute it. It is available only to the server administrator.
For experiments, let's create a testbase database by executing an SQL query from the command line. To do this, you need to log into MySQL and enter in the MySQL command line:
mysql> create database testbase;
After this you should type:
mysql>use testbase;
Database created:
1.4 Selecting a database. Functionmysql_select_db
Before sending the first request to the MySQL server, we must indicate which database we are going to work with. The mysql_select_db function is designed for this:
bool mysql_select_db(string $database_name [,resource $link_identifier])
It notifies PHP that future operations on the $link_identifier connection will use the $database_name database.
Using this function is equivalent to calling the use command in an SQL query, i.e. the mysql_select_db function selects a database for further work, and all subsequent SQL queries are applied to the selected database. The function takes as arguments the name of the selected database database_name and the connection descriptor resource. The function returns true if the operation was successful and false otherwise:
//Database connection code
if (! @mysql_select_db($dbname, $dbcnx))
//Display a warning
echo("
The database is currently unavailable, so the page cannot be displayed correctly.");
1.5 Error handling
If errors occur while working with MySQL (for example, the parentheses in the query are not balanced or there are not enough parameters), then the error message and its number can be obtained using the two functions described below.
It is important to use these functions carefully and promptly, because otherwise debugging scripts can become more difficult.
● Function:
int mysql_errno()
returns the number of the last reported error. The connection identifier $link_identifier can be omitted if only one connection was established during the script's execution.
● Function:
string mysql_error()
returns not a number, but a string containing the text of the error message. It is convenient to use for debugging purposes. Usually mysql_error is used together with the or die() construct, for example:
@mysql_connect("localhost", "user", "password")
or die("Error connecting to the database: ".mysql_error());
The @ operator, as usual, serves to suppress the standard warning that might occur in the event of an error.
In recent versions of PHP, warnings in MySQL functions are not logged by default.
1.6 Automation of connection to MySQL. File (config.php)
Typically, there are several scripts on a site that need access to the same database.
It is recommended to separate the code responsible for connecting to MySQL into a separate file, and then connect it to the necessary scripts using the include function.
It makes sense to place the functions for connecting, selecting and creating a database in the same file (config.php) where the variables with server name $dblocation, username $dbuser, password $dbpasswd and database name $dbname are declared:
config.php listing:
//config.php code of the file containing parameters for connecting to the server and selecting a database
//outputs connection error messages to the browser
$dblocation = "localhost"; //Server name
$dbname = "insert database name" //Database name: being created or existing
$dbuser = "root"; //Database username
$dbpasswd = ""; //Password
//Connect to the database server
//Suppress error output with the @ symbol before calling the function
$dbcnx=@mysql_connect($dblocation,$dbuser,$dbpasswd);
if (!$dbcnx) //If the descriptor is 0, the connection to the database server is not established
//Display a warning
echo("
The database server is currently unavailable, so the page cannot be displayed correctly.
");//Create the database $dbname - only a superuser can do this
//If the database already exists, there will be a non-fatal error
@mysql_query("CREATE DATABASE if not exists $dbname’);
//Connection code to the database: we make an unambiguous selection of a newly created database or an already existing database
//Suppress error output with the @ symbol before calling the function
if(!@mysql_select_db($dbname, $dbcnx)) //If the descriptor is 0, the connection to the database is not established
//Display a warning
echo("
The database is currently unavailable, so the page cannot be displayed correctly.
");//Small helper function that prints a message
//error message in case of database query error
function puterror($message)
echo("");
2. EXECUTION OF DATABASE QUERIES
2.1 Creating a table. FunctionCREATE TABLE:
CREATE TABLE TableName (FieldName type, FieldName type,)
This command creates a new table in the database with columns (fields) defined by their names (FieldName) and specified types. After creating the table, you can add records to it consisting of the fields listed in this command.
Listing test_11.php. A program that creates a new table in a database:
include "config.php";//Connect to the server and select a database
mysql_query("CREATE TABLE if not exists people
id INT AUTO_INCREMENT PRIMARY KEY,
or die("MySQL error: ".mysql_error());
This script creates a new table people with two fields. The first field has type INT (integer) and name id. The second is the TEXT type (text string) and the name name.
If the table exists, the or die() construct will work.
The optional if not exists clause, when specified, tells the MySQL server that it should not generate an error message if a table with the specified name already exists in the database.
The text uses a translation of official documentation made by the All-Russian Club of Webmasters.
All work with databases comes down to connecting to the server, selecting a database, sending a request, processing the request and disconnecting from the databases. So, let's look at this whole thing point by point. For clarity of the entire text, let’s imagine that a MySQL server is installed on the local machine. The port for working with it is standard. To connect we will use the username “root” and password “no_one”.
Connection
Connection to the database is made using the “mysql_connect()” function. It is passed three parameters: server name (or server name:connection port), username and password. If a second call to "mysql_connect()" is made with the same arguments, no new connection will be established - instead, the connection ID of the already open connection will be returned (ie, work will continue with the same database). If you are working with only one database, then you do not need to create a connection identifier. Once the script has finished executing, the connection to the server will be closed, unless it was explicitly closed by an earlier call to "mysql_close()".
Example: $connect = mysql_connect('localhost', 'root', 'no_one'); In this case, the $connect variable is the connection identifier. If you work with only one database, then the code is written without an identifier: mysql_connect('localhost', 'root', 'no_one');
Selecting a Database
"mysql_select_db" - selects a MySQL database. This means that the server may contain not one database, but several. With this command we will select the one we need (to which we have the rights). The parameter of this function is the name of the database. The database selected in this way becomes active and is associated with a specific identifier. If the connection ID is not defined, then the last connection to the database is used.
Example: mysql_select_bd('test', $connect); - where test is the database name and $connect is the connection identifier. If you work with only one database, then the code is written without an identifier: mysql_select_bd('test');
Sending a request
mysql_query() sends a query to the database currently active on the server that is associated with a specific link ID. If no ID is specified, the last open connection is used. The parameter of this function is a string with an sql query.
Example: $tmp=mysql_query("slect * from table", $connect); - this command will return the entire contents of the table table from the active database pointed to by the $connect identifier. If you work with only one database, then the code is written without an identifier: $tmp=mysql_query(“slect * from table”);
Processing request
There are several functions for processing requests. The choice of one or another method for processing requests depends on the programming style and the task at hand. It should also be taken into account that different options “load” the server in different ways (some very much, some not so much). Let's look at a few of them.
mysql_fetch_object - returns a php object as a processing result. This method is good for those who are used to object programming
Example: while($result= mysql_fetch_object($tmp)) echo($result->name);
mysql_fetch_array - Fetch the result as an associative array. This method is good for beginners (although it depends on who will like it more).
Closing a connection
The connection is closed using the mysql_close() function. Its parameter is the database connection identifier. If this parameter is not specified, the last called connection is closed.
Errors are the bane of any program. The larger the project, the more difficult it is to fix and find errors. But the most important thing in the process of working with a program is the qualifications of the programmer and his desire to write correct and accurate code containing a minimum amount...
Using php...
Creating a connection in different ways:
1) the old-fashioned way of connecting 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:
- 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, greater security and greatly increases performance.
config file from the previous method! - 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, it’s much more complicated here and you need to study PDO - this is a separate topic.
In this article we will look at ways to access MySQL database tables using the SQL query language. SQL is an acronym that is called "structured query language".
In the PHP language, there are a number of functions with the prefix "mysql" for this purpose. We won't need many of them to process requests. A function without which executing SQL queries in PHP would simply be impossible:
Resource mysql_query(query)
This function sends a request to the database and returns the resource identifier if the request is successful.
In order to connect to the MySQL database, you must perform the following sequence:
$host="localhost"; // host name (to be confirmed with the provider) $database="db_name"; // name of the database you should create $user="user_name"; // username specified by you, or defined by the provider $pswd="your_pass"; // the password you specified $dbh = mysql_connect($host, $user, $pswd) or die("Can't connect to MySQL."); mysql_select_db($database) or die("I can't connect to the database.");
So mysql_connect()- a function for connecting to a MySQL server on your hosting.
A mysql_select_db() selects a database on the server to connect to.
In other words, we connect to the server, select a database and start working.
The die() function is called if an error occurs and displays the message you specified in the browser window.
To finish working with databases, use the function:
Mysql_close($dbh);
Here $dbh- descriptor that was returned by the function upon connection mysql_connect.
Having finished the initial review, let's begin looking at the actual SQL queries.
To do this, first of all you need to create a database with a specific name. And create a table in it, also with a specific name. In our examples we will refer to the table my_sql_table. To create this table, let's run the following query in phpmyadmin of our localhost:
CREATE TABLE `my_sql_table` (`id` INT NOT NULL , // identifier of future table records `firstname` VARCHAR(50) NOT NULL , // text field VARCHAR `surname` VARCHAR(50) NOT NULL , // max length 50 characters PRIMARY KEY (`id`) // primary key - identifier id);
So the table has been created. Let's execute the first request, which we will immediately format in the form of PHP code:
\n"; echo "Name: ".$row["firstname"]."
\n"; echo "Last name: ".$row["surname"]."
\n"; ) ?>
Let's analyze the PHP code of the file firstsql.php. Let's start with the actual query to the database tables.
$query = "SELECT * FROM `my_sql_table`";
This query can be deciphered as follows: select from table my_sql_table DB all records from all fields. This way sign * after the word SELECT means “select absolutely everything.” So, the request has been created. Now you need to execute it:
$res = mysql_query($query);
If the request is successful, the function mysql_query() will return us the resource ID $res.
We must pass it as a parameter to the function mysql_fetch_array(). The name of this function speaks for itself. Those. it forms and outputs an array based on a sample from the database table. In the case of our table, the array will consist of a number of elements equal to the number of records (rows) in the table and contain values id, firstname, surname for each row of the table. Hence the following code:
While($row = mysql_fetch_array($res)) ( echo "Number: ".$row["id"]."
\n"; echo "Name:".$row["firstname"]."
\n"; echo "Last name:".$row["surname"]."
\n"; )
can be commented like this: while the $row variable we introduced receives non-zero results from the function mysql_fetch_row you should output the field values to the browser $row["id"], $row["firstname"], $row["surname"] by using echo.
If the request is executed like this:
$query = "SELECT firstname FROM `my_sql_table`";
then this will mean that from all rows only the values of the firstname field are selected.
Therefore the previous code should be rewritten as:
$res = mysql_query($query); while($row = mysql_fetch_array($res)) ( echo "Name:".$row["firstname"]."
\n"; )
If you want to select table rows with a specific value id where the surname will be Petrov, then the request will be rewritten as follows:
$query = "SELECT id FROM `my_sql_table` where surname="Petrov"";
But if you need to find out the last name of the person who is numbered, for example, 5, then the request will be like this:
$query = "SELECT surname FROM `my_sql_table` where id=5";
In this case, you know that the result of the query will be only one row from the table. Those. there is no point in organizing a loop using while. And the request processing will be as follows
$res = mysql_query($query); $row = mysql_fetch_row($res); echo "The last name of the fifth person on the list is ".$row."\n";
Here, instead of mysql_fetch_array() we used mysql_fetch_row(). Those. get the value of a field (or fields) of a specific row. Since we had one field - surname - we can refer to the only element of the $row array as $row;.
So, let's look at the most typical examples of MySQL queries. We will conduct the review based on the table my_sql_table:
1. Add a middle_name (middle name) field to the my_sql_table table after surname:
$query = "ALTER TABLE `my_sql_table` ADD `middle_name`
VARCHAR(50) NOT NULL AFTER `surname`";
2. Now let’s remove the surname field from the my_sql_table table:
$query = "ALTER TABLE `my_sql_table` DROP `surname`";
3. Delete records from the my_sql_table table with the surname Sidorov:
$query = "DELETE FROM `my_sql_table` where surname="Sidorov"";
4. In addition to equal signs, also “greater than” or “less than”, in the MySQL query language there is the concept “ similar to". Let's select records from the table my_sql_table where the last name contains " dor" :
$query = "SELECT * FROM `my_sql_table` where surname like "%dor%"";
Here the presence " %
" at the beginning and end of "dor" and means that the query will look for exactly "dor", and it does not matter whether it is at the beginning, end, or middle of the surname. Consider the following example
5. Select records from the my_sql_table table with a last name that begins with P. Please note the location " %
":
$query = "SELECT * FROM `my_sql_table` where surname like "P%"";
6. Calculate the maximum value id:
$query = "SELECT MAX(id) FROM `my_sql_table`";
7. Calculate the number of fields in my_sql_table with a last name that begins with P.
$query = "SELECT COUNT(*) FROM `my_sql_table` where surname like "P%"";
8. Deleting the my_sql_table table:
$query = "DROP TABLE `my_sql_table`";
For queries 1-3 in PHP, simply run the query:
Mysql_query($query);
We looked at the most typical examples of requests. I believe that with their help, following elementary logic, you will be able to perform more complex queries against the MySQL database tables you have created.
| If you have any other questions or something is not clear - welcome to our | |
|
|
We learned how to connect to the MySQL server, select 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.
Now we will dive 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 first create a table. We already know how to do this from the first part.
Here is the script code that will create the sign 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);
Our table has only two fields: login and password. For now we don’t need any more, 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 SQL insert 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 "User not added: " . mysqli_error(); mysqli_close($link);
An 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 values to be added in parentheses. Values are enclosed in quotation marks.
The request syntax looks like this:
INSERT INTO table_name (column1, column2) VALUE ("x1", "x2")
Quotes in the second parentheses are required.
In place of values there can be variables. 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 "User not 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 and then, after verification, written to the database.
There is a quick way to insert multiple rows with one INSERT statement:
INSERT INTO users (login, password) VALUE ("bob", "eee333"), ("Rooki", "12345"), ("magy", "olol88e8")
As you can see, the listed data is simply separated by commas.
So, using the INSERT command, we learned how to add records to a table. Go ahead.
View Table: SELECT Command
Now we have a users table that has rows. The previous script can be run several 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 SELECT SQL 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 obtain 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 occurred: " . mysqli_error(); else echo "Data received"; mysqli_close($link);
The mysqli_query() function returned the query result identifier to us - we put it in a variable and will later work with it using other PHP functions.
Number of records in request
Let's determine how many lines are in our query? I ran the script for adding a record to the table, I don’t remember how many times and now I don’t know how many rows are in my table.
To determine the number of rows in the result of a query, use the mysqli_num_rows() function. This function is passed the identifier of the query result, and it returns 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 occurred: " . 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 the table, then the above method is not the most suitable. 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 command SELECT COUNT(*) FROM table_name.
$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 occurred: " . 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 used the new PHP function mysqli_fetch_row() to fetch the 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 query result ID, PHP creates an internal pointer in the result recordset. This pointer automatically moves to the next record after accessing the current record. This mechanism makes it very convenient to loop through the result set of a SELECT query.
PHP has several functions with which you can get an array consisting of its fields for each line of the resulting query. For example, let's take the mysqli_fetch_row() function. This function is passed the request identifier and returns an array. So, in a loop, the entire query result is viewed, and when the end of the query result is reached, 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);
The mysqli_fetch_row() function 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 occurred: " . 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"); // delete 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 to add 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 occurred: " . 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 the unique names in our table. Each unique name in a new row of 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 in alphabetical order). Here is an example in which you can compare a regular query and one 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 meet the condition. For example, let's make a request for people under 30 years old.
To do this, we 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 age order:
" SELECT * FROM users WHERE age<30 ORDER BY age ".
If we make a 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 let's request all users with the name "Max".
$link = mysqli_connect("localhost", "root", ""); if (!$link) die("Error"); mysqli_select_db("tester"); echo "All Maxes:
"; $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 of a query - it will select only names from the users table, everything except Max.
SELECT name FROM users WHERE name!="Max"
That's all for the WHERE query.
Limiting entries - LIMIT
By adding the LIMIT command to the SQL query, we will limit the size of the result.
The query that returns the first three entries is: " 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 "
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 triple indicates the offset in the query result.
Match pattern - LIKE
The SQL language supports simple templates. To do this, use the LIKE command and specify the pattern using the % symbol.
Here is an example query that will return all records with names starting with the letter S.
SELECT * FROM users WHERE name LIKE "S%"
I'm 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"
Condition met - 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 years.
SELECT * FROM users WHERE age IN (21,26,33)
I'm 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 age value 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 takes the minimum value.
SELECT name, min(age) FROM users
Updating a record - UPDATE
Let's set Max Lopes age to 15 years. This is done with a MySQL query:
UPDATE users SET age="15" WHERE name="Max" AND surname="Lopes"
Please note the new AND command (and means “and” in English) in the query. If we do not specify the last name, then the age of 15 years will be set for all Maxes in the table.
You can update two or more fields in one row with one query. This is done as follows:
UPDATE users SET age = "18", surname = "Coocker" WHERE id = "3"
Our table doesn't have an id field, so this query won't work on it. But we will definitely learn this field containing unique line numbers.
Delete an entry - DELETE
MySQL database query to delete a record:
DELETE FROM users WHERE id = "10"
Again, our table does not have an id field. But we can remove from it all people under 18 years of age.
DELETE FROM users WHERE age< "18"
Delete a table - DROP TABLE
MySQL database query that deletes the entire users table:
DROP TABLE users
Delete a column - ALTER TABLE ... DROP ...
Sometimes you may need to remove 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 permanently.
Add a column - ALTER TABLE ... ADD ...
Sometimes you may need to add a column to an existing table, let's for example add the age column back to the users table:
ALTER TABLE users ADD age TINYINT UNSIGNED
Renaming a column - ALTER TABLE ... CHANGE ...
Sometimes you may need to rename a column, for example, rename the age column to vozrast. We do it like this:
ALTER TABLE users CHANGE age age TINYINT UNSIGNED
This MySQL query renamed the column age to vozrast with data type TINYINT UNSIGNED.
Renaming a table - RENAME TABLE ... TO ...
Sometimes you may need to rename the table:
RENAME TABLE users TO peoples
Removing a database - DROP DATABASE
This query can delete the database named tester:
DROP DATABASE tester
Creating a database - CREATE DATABASE
This query creates a database named tester:
CREATE DATABASE tester
This request works for me in Denver, but on hosting it may not work if the database user does not have rights to perform deletion.
Results
So, in this part we got acquainted with queries to MySQL. Many of the queries we examined are not often useful to us in the process of work, but we 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).
When working on websites, 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 learn about MySQL data types.