PHP: mysqli::execute_query - Manual (original) (raw)

mysqli_execute_query

(PHP 8 >= 8.2.0)

mysqli::execute_query -- mysqli_execute_query — Prepares, binds parameters, and executes SQL statement

Description

Object-oriented style

The statement template can contain zero or more question mark (?) parameter markers⁠—also called placeholders. The parameter values must be provided as an array usingparams parameter.

A prepared statement is created under the hood but it's never exposed outside of the function. It's impossible to access properties of the statement as one would do with the mysqli_stmt object. Due to this limitation, the status information is copied to themysqli object and is available using its methods, e.g.mysqli_affected_rows() or mysqli_error().

Note:

In the case where a statement is passed tomysqli_execute_query() that is longer thanmax_allowed_packet of the server, the returned error codes are different depending on the operating system. The behavior is as follows:

On Linux returns an error code of 1153. The error message means got a packet bigger thanmax_allowed_packet bytes.

On Windows returns an error code 2006. This error message means server has gone away.

Parameters

mysql

Procedural style only: A mysqli object returned by mysqli_connect() or mysqli_init()

query

The query, as a string. It must consist of a single SQL statement.

The SQL statement may contain zero or more parameter markers represented by question mark (?) characters at the appropriate positions.

Note:

The markers are legal only in certain places in SQL statements. For example, they are permitted in the VALUES() list of an INSERT statement (to specify column values for a row), or in a comparison with a column in aWHERE clause to specify a comparison value. However, they are not permitted for identifiers (such as table or column names).

params

An optional list array with as many elements as there are bound parameters in the SQL statement being executed. Each value is treated as a string.

Return Values

Returns [false](reserved.constants.php#constant.false) on failure. For successful queries which produce a result set, such as SELECT, SHOW, DESCRIBE orEXPLAIN, returns a mysqli_result object. For other successful queries, returns [true](reserved.constants.php#constant.true).

Examples

Example #1 mysqli::execute_query() example

Object-oriented style

`<?php

mysqli_report

(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); mysqli=newmysqli(′localhost′,′myuser′,′mypassword′,′world′);mysqli = new mysqli('localhost', 'my_user', 'my_password', 'world');mysqli=newmysqli(′localhost′,′myuser′,′mypassword′,′world′);query = 'SELECT Name, District FROM City WHERE CountryCode=? ORDER BY Name LIMIT 5'; result=result = result=mysqli->execute_query($query, ['DEU']);
foreach ($result as $row) {
printf("%s (%s)\n", row["Name"],row["Name"], row["Name"],row["District"]);
}`

Procedural style

`<?php

mysqli_report

(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); link=mysqliconnect("localhost","myuser","mypassword","world");link = mysqli_connect("localhost", "my_user", "my_password", "world");link=mysqliconnect("localhost","myuser","mypassword","world");query = 'SELECT Name, District FROM City WHERE CountryCode=? ORDER BY Name LIMIT 5'; result=mysqliexecutequery(result = mysqli_execute_query(result=mysqliexecutequery(link, $query, ['DEU']);
foreach ($result as $row) {
printf("%s (%s)\n", row["Name"],row["Name"], row["Name"],row["District"]);
}`

The above examples will output something similar to:

Aachen (Nordrhein-Westfalen) Augsburg (Baijeri) Bergisch Gladbach (Nordrhein-Westfalen) Berlin (Berliini) Bielefeld (Nordrhein-Westfalen)

Found A Problem?

theking2(at)king(dot).co. ¶

2 years ago

A function to call a stored procedure with an arbitrary number of IN parameters and one OUT parameter, for instance returning affected row count. The return value of yhe function is this value.

<?php

/**
 * call_sp Call the specified stored procedure with the given parameters.
 * The first parameter is the name of the stored procedure.
 * The remaining parameters are the (in) parameters to the stored procedure.
 * the last (out) parameter should be an int, like state or number of affected rows.
 *
 * @param  mixed $sp_name The name of the stored procedure to call.
 * @param  mixed $params The parameters to pass to the stored procedure.
 * @return int The number of affected rows.
 */
function call_sp( \mysqli <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>d</mi><mi>b</mi><mo separator="true">,</mo><mi>s</mi><mi>t</mi><mi>r</mi><mi>i</mi><mi>n</mi><mi>g</mi></mrow><annotation encoding="application/x-tex">db, string </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.8889em;vertical-align:-0.1944em;"></span><span class="mord mathnormal">d</span><span class="mord mathnormal">b</span><span class="mpunct">,</span><span class="mspace" style="margin-right:0.1667em;"></span><span class="mord mathnormal">s</span><span class="mord mathnormal">t</span><span class="mord mathnormal" style="margin-right:0.02778em;">r</span><span class="mord mathnormal">in</span><span class="mord mathnormal" style="margin-right:0.03588em;">g</span></span></span></span>sp_name, ...$params ): int
{
  <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>s</mi><mi>q</mi><mi>l</mi><mo>=</mo><mi mathvariant="normal">&quot;</mi><mi>C</mi><mi>A</mi><mi>L</mi><mi>L</mi></mrow><annotation encoding="application/x-tex">sql = &quot;CALL </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.8889em;vertical-align:-0.1944em;"></span><span class="mord mathnormal">s</span><span class="mord mathnormal" style="margin-right:0.01968em;">ql</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">=</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:0.6944em;"></span><span class="mord">&quot;</span><span class="mord mathnormal" style="margin-right:0.07153em;">C</span><span class="mord mathnormal">A</span><span class="mord mathnormal">LL</span></span></span></span>sp_name( ";
  <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>s</mi><mi>q</mi><mi>l</mi><mi mathvariant="normal">.</mi><mo>=</mo><mi>i</mi><mi>m</mi><mi>p</mi><mi>l</mi><mi>o</mi><mi>d</mi><mi>e</mi><mo stretchy="false">(</mo><mi mathvariant="normal">&quot;</mi><mo separator="true">,</mo><mi mathvariant="normal">&quot;</mi><mo separator="true">,</mo><mi>a</mi><mi>r</mi><mi>r</mi><mi>a</mi><msub><mi>y</mi><mi>f</mi></msub><mi>i</mi><mi>l</mi><mi>l</mi><mo stretchy="false">(</mo><mn>0</mn><mo separator="true">,</mo><mi>c</mi><mi>o</mi><mi>u</mi><mi>n</mi><mi>t</mi><mo stretchy="false">(</mo></mrow><annotation encoding="application/x-tex">sql .= implode( &quot;, &quot;, array_fill( 0, count( </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.8889em;vertical-align:-0.1944em;"></span><span class="mord mathnormal">s</span><span class="mord mathnormal" style="margin-right:0.01968em;">ql</span><span class="mord">.</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">=</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:1.0361em;vertical-align:-0.2861em;"></span><span class="mord mathnormal">im</span><span class="mord mathnormal" style="margin-right:0.01968em;">pl</span><span class="mord mathnormal">o</span><span class="mord mathnormal">d</span><span class="mord mathnormal">e</span><span class="mopen">(</span><span class="mord">&quot;</span><span class="mpunct">,</span><span class="mspace" style="margin-right:0.1667em;"></span><span class="mord">&quot;</span><span class="mpunct">,</span><span class="mspace" style="margin-right:0.1667em;"></span><span class="mord mathnormal">a</span><span class="mord mathnormal" style="margin-right:0.02778em;">rr</span><span class="mord mathnormal">a</span><span class="mord"><span class="mord mathnormal" style="margin-right:0.03588em;">y</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.3361em;"><span style="top:-2.55em;margin-left:-0.0359em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight" style="margin-right:0.10764em;">f</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.2861em;"><span></span></span></span></span></span></span><span class="mord mathnormal">i</span><span class="mord mathnormal" style="margin-right:0.01968em;">ll</span><span class="mopen">(</span><span class="mord">0</span><span class="mpunct">,</span><span class="mspace" style="margin-right:0.1667em;"></span><span class="mord mathnormal">co</span><span class="mord mathnormal">u</span><span class="mord mathnormal">n</span><span class="mord mathnormal">t</span><span class="mopen">(</span></span></span></span>params ), "?" ) );
  $sql .= ", @__affected );";

  <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>r</mi><mi>e</mi><mi>s</mi><mi>u</mi><mi>l</mi><mi>t</mi><mo>=</mo></mrow><annotation encoding="application/x-tex">result   = </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.6944em;"></span><span class="mord mathnormal">res</span><span class="mord mathnormal">u</span><span class="mord mathnormal">lt</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">=</span></span></span></span>db->execute_query( <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>s</mi><mi>q</mi><mi>l</mi><mo separator="true">,</mo></mrow><annotation encoding="application/x-tex">sql, </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.8889em;vertical-align:-0.1944em;"></span><span class="mord mathnormal">s</span><span class="mord mathnormal" style="margin-right:0.01968em;">ql</span><span class="mpunct">,</span></span></span></span>params );
  <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>r</mi><mi>e</mi><mi>s</mi><mi>u</mi><mi>l</mi><mi>t</mi><mo>=</mo></mrow><annotation encoding="application/x-tex">result   = </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.6944em;"></span><span class="mord mathnormal">res</span><span class="mord mathnormal">u</span><span class="mord mathnormal">lt</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">=</span></span></span></span>db->query( "select @__affected;" );
  <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>a</mi><mi>f</mi><mi>f</mi><mi>e</mi><mi>c</mi><mi>t</mi><mi>e</mi><mi>d</mi><mo>=</mo><mo stretchy="false">(</mo><mi>i</mi><mi>n</mi><mi>t</mi><mo stretchy="false">)</mo></mrow><annotation encoding="application/x-tex">affected = (int) </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.8889em;vertical-align:-0.1944em;"></span><span class="mord mathnormal">a</span><span class="mord mathnormal" style="margin-right:0.10764em;">ff</span><span class="mord mathnormal">ec</span><span class="mord mathnormal">t</span><span class="mord mathnormal">e</span><span class="mord mathnormal">d</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">=</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:1em;vertical-align:-0.25em;"></span><span class="mopen">(</span><span class="mord mathnormal">in</span><span class="mord mathnormal">t</span><span class="mclose">)</span></span></span></span>result->fetch_column( 0 );
  return $affected;
}