Fictional member order guest php. Basics of working with PHP MySqli. Deleting old entries

Many of the more mature databases support the concept of prepared statements. What are they? They can be thought of as a kind of compiled template for the SQL that an application wants to run, that can be customized using variable parameters. Prepared statements offer two major benefits:

• The query only needs to be parsed (or prepared) once, but can be executed multiple times with the same or different parameters. When the query is prepared, the database will analyze, compile and optimize its plan for executing the query. For complex queries this process can take up enough time that it will noticeably slow down an application if there is a need to repeat the same query many times with different parameters. By using a prepared statement the application avoids repeating the analyze/compile/optimize cycle. This means that prepared statements use fewer resources and thus run faster.
• The parameters to prepared statements don"t need to be quoted; the driver automatically handles this. If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible).

Prepared statements are so useful that they are the only feature that PDO will emulate for drivers that don"t support them. This ensures that an application will be able to use the same data access paradigm regardless of the capabilities of the database.

Example #1 Repeated inserts using prepared statements

name and a value for the named placeholders.

$stmt = $dbh -> prepare ( "INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt -> bindParam (":name" , $name );
$stmt -> bindParam (":value" , ​​$value );

// insert one row
$name = "one" ;
$value = 1 ;
$stmt -> execute();

$name = "two" ;
$value = 2 ;
$stmt -> execute();
?>

Example #2 Repeated inserts using prepared statements

This example performs an INSERT query by substituting a name and a value for the positional ? placeholders.

$stmt = $dbh -> prepare ( "INSERT INTO REGISTRY (name, value) VALUES (?, ?)");
$stmt -> bindParam(1, $name);
$stmt -> bindParam (2 , $value );

// insert one row
$name = "one" ;
$value = 1 ;
$stmt -> execute();

// insert another row with different values
$name = "two" ;
$value = 2 ;
$stmt -> execute();
?>

Example #3 Fetching data using prepared statements

Example #4 Calling a stored procedure with an output parameter

If the database driver supports it, an application may also bind parameters for output as well as input. Output parameters are typically used to retrieve values ​​from stored procedures. Output parameters are slightly more complex to use than input parameters, in that a developer must know how large a given parameter might be when they bind it. If the value turns out to be larger than the size they suggested, an error is raised.

$stmt = $dbh -> prepare ("CALL sp_returns_string(?)" );
$stmt -> bindParam(1, $return_value, PDO::PARAM_STR, 4000);

// call the stored procedure
$stmt -> execute();

print "procedure returned $return_value \n" ;
?>

Example #5 Calling a stored procedure with an input/output parameter

Developers may also specify parameters that hold values ​​both input and output;

$stmt = $dbh -> prepare ( the syntax is similar to output parameters. In this next example, the string "hello" is passed into the stored procedure, and when it returns, hello is replaced with the return value of the procedure.);
"CALL sp_takes_string_returns_string(?)"" ;!}
$value = "hello

// call the stored procedure
$stmt -> execute();

$stmt -> bindParam (1 , $value , PDO :: PARAM_STR | PDO :: PARAM_INPUT_OUTPUT , 4000 );
?>

print "procedure returned $value \n" ; Due to discontinuation of support PHP MySQL in 2011, PDO or MySqli

. They have better functionality (than MySQL) and offer an OOP (Object Oriented Interface) API. Which one is better is a topic for another article, in this article we will try to understand the basics of working with MySqli. Therefore, without further ado, let's move on to consider connecting, selecting, inserting, updating and deleting records (data/documents/information) using PHP MySqli. I hope that this article will be useful in solving problems that may arise when working with PHP MySqli.

Installing MySqli Using PHP versions 5.3.0+, MySqli is available by default; for older versions, to make it available, you need to include the php_mysqli.dll DLL inside the file php.ini and edit php.ini by uncommenting the line extension=php_mysqli.dll. On Linux, MySQLIi will be installed automatically when you install the PHP5 mysql package. More detailed information about installation in windows systems

and linux can be found.

MySqli offers two ways to connect to a database: procedural and object-oriented. It is recommended to use object-oriented. Procedural is similar to (old) MySql, so it may be preferable for newbies, but it's worth remembering that it's not recommended.

PHP

The following shows how to open a connection to a database in an object-oriented manner. This method will be used in all the examples below.

PHP

Selecting (SELECT) the resulting series as an associative array

mysqli_fetch_assoc() : The code below fetches the result series as associative array. The returned array contains rows retrieved from the database, where the column names will be the key used to access the internal data. As shown below, the data is displayed as an HTML table.

PHP

Selecting (SELECT) the resulting series as an array (associative, regular, or both)

fetch_array() function: returns an array with the combined functionality of mysqli_fetch_row and mysqli_fetch assoc. This function is an extended version of the mysqli_fetch_row() function; You can use either a string or numbers to access data.

PHP

Selecting (SELECT) the result series as an object

fetch_object() : To get the result set as an object, you need to use MySqli fetch_object() . The object's attributes will display the names of the fields found within the result set.

PHP

Selecting (SELECT) a single value

A single value can be retrieved from the database using fetch_object (Cameron Spear method).

PHP

Retrieving (SELECT COUNT) the number of rows in the table

Sometimes you need to know the number of rows in a table, especially when numbering pages.

PHP

SELECT using prepared statements

prepared statements- a special DBMS tool that allows you to speed up the sequential execution of repeating queries built according to the same template.

One of the features of MySqli is the ability to use already written templates: that is, you only need to write a query once, after which it can be executed many times with different parameters. Using already written templates improves performance for large tables and complex queries. To prevent malicious code from entering, each request is analyzed separately by the server.

The code below uses a template (Prepared statement) to retrieve data from the database. Aggregate ? in an SQL query it acts as a marker and will be replaced by a parameter, which in turn can be a string, integer, double or blob. In our case, this is the string $search_product.

PHP

The same request with several parameters:

PHP

INSERT a record

The entry below inserts a new row into the table.

PHP

The excerpt below inserts the same meanings using Prepared Statements. As we've already said, patterns are extremely effective against SQL injection. For the example given, their use is the best option.

PHP

INSERT multiple records

Inserting multiple rows at once is done by including a row of column values, with each row of values ​​surrounded by parentheses and separated from the others by a comma. Sometimes you need to find out how many records were inserted, updated or deleted, you can use mysqli_affected_rows for this.

PHP

Update/Delete entries

The principle of updating and deleting records is the same. It is enough to replace the query string with MySql update or delete (I don’t understand, see for yourself).

PHP

Update using prepared statements

An example of updating a record using prepared statements is shown below.

PHP

Deleting old entries

All records that have been on the server for more than 1 day are deleted; You can set the number of days yourself.

PHP