Execute sql queries php

Statements can be executed with the mysqli::query() , mysqli::real_query() and mysqli::multi_query() . The mysqli::query() function is the most common, and combines the executing statement with a buffered fetch of its result set, if any, in one call. Calling mysqli::query() is identical to calling mysqli::real_query() followed by mysqli::store_result() .

Example #1 Executing queries

mysqli_report ( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
$mysqli = new mysqli ( «example.com» , «user» , «password» , «database» );

$mysqli -> query ( «DROP TABLE IF EXISTS test» );
$mysqli -> query ( «CREATE TABLE test(id INT)» );

After statement execution, results can be either retrieved all at once or read row by row from the server. Client-side result set buffering allows the server to free resources associated with the statement’s results as early as possible. Generally speaking, clients are slow consuming result sets. Therefore, it is recommended to use buffered result sets. mysqli::query() combines statement execution and result set buffering.

PHP applications can navigate freely through buffered results. Navigation is fast because the result sets are held in client memory. Please, keep in mind that it is often easier to scale by client than it is to scale the server.

Example #2 Navigation through buffered results

mysqli_report ( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
$mysqli = new mysqli ( «example.com» , «user» , «password» , «database» );

$mysqli -> query ( «DROP TABLE IF EXISTS test» );
$mysqli -> query ( «CREATE TABLE test(id INT)» );
$mysqli -> query ( «INSERT INTO test(id) VALUES (1), (2), (3)» );

$result = $mysqli -> query ( «SELECT id FROM test ORDER BY id ASC» );

echo «Reverse order. \n» ;
for ( $row_no = $result -> num_rows — 1 ; $row_no >= 0 ; $row_no —) $result -> data_seek ( $row_no );
$row = $result -> fetch_assoc ();
echo » id color: #007700″>. $row [ ‘id’ ] . «\n» ;
>

echo «Result set order. \n» ;
foreach ( $result as $row ) echo » id color: #007700″>. $row [ ‘id’ ] . «\n» ;
>

The above example will output:

If client memory is a short resource and freeing server resources as early as possible to keep server load low is not needed, unbuffered results can be used. Scrolling through unbuffered results is not possible before all rows have been read.

Example #3 Navigation through unbuffered results

$mysqli -> real_query ( «SELECT id FROM test ORDER BY id ASC» );
$result = $mysqli -> use_result ();

echo «Result set order. \n» ;
foreach ( $result as $row ) echo » id color: #007700″>. $row [ ‘id’ ] . «\n» ;
>

Result set values data types

The mysqli::query() , mysqli::real_query() and mysqli::multi_query() functions are used to execute non-prepared statements. At the level of the MySQL Client Server Protocol, the command COM_QUERY and the text protocol are used for statement execution. With the text protocol, the MySQL server converts all data of a result sets into strings before sending. This conversion is done regardless of the SQL result set column data type. The mysql client libraries receive all column values as strings. No further client-side casting is done to convert columns back to their native types. Instead, all values are provided as PHP strings.

Example #4 Text protocol returns strings by default

mysqli_report ( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
$mysqli = new mysqli ( «example.com» , «user» , «password» , «database» );

$mysqli -> query ( «DROP TABLE IF EXISTS test» );
$mysqli -> query ( «CREATE TABLE test(id INT, label CHAR(1))» );
$mysqli -> query ( «INSERT INTO test(id, label) VALUES (1, ‘a’)» );

$result = $mysqli -> query ( «SELECT id, label FROM test WHERE > );
$row = $result -> fetch_assoc ();

printf ( «id = %s (%s)\n» , $row [ ‘id’ ], gettype ( $row [ ‘id’ ]));
printf ( «label = %s (%s)\n» , $row [ ‘label’ ], gettype ( $row [ ‘label’ ]));

The above example will output:

id = 1 (string) label = a (string)

It is possible to convert integer and float columns back to PHP numbers by setting the MYSQLI_OPT_INT_AND_FLOAT_NATIVE connection option, if using the mysqlnd library. If set, the mysqlnd library will check the result set meta data column types and convert numeric SQL columns to PHP numbers, if the PHP data type value range allows for it. This way, for example, SQL INT columns are returned as integers.

Example #5 Native data types with mysqlnd and connection option

mysqli_report ( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );

$mysqli = new mysqli ();
$mysqli -> options ( MYSQLI_OPT_INT_AND_FLOAT_NATIVE , 1 );
$mysqli -> real_connect ( «example.com» , «user» , «password» , «database» );

$mysqli -> query ( «DROP TABLE IF EXISTS test» );
$mysqli -> query ( «CREATE TABLE test(id INT, label CHAR(1))» );
$mysqli -> query ( «INSERT INTO test(id, label) VALUES (1, ‘a’)» );

$result = $mysqli -> query ( «SELECT id, label FROM test WHERE > );
$row = $result -> fetch_assoc ();

printf ( «id = %s (%s)\n» , $row [ ‘id’ ], gettype ( $row [ ‘id’ ]));
printf ( «label = %s (%s)\n» , $row [ ‘label’ ], gettype ( $row [ ‘label’ ]));

The above example will output:

id = 1 (integer) label = a (string)

Источник

mysqli_execute_query

Prepares the SQL query, binds parameters, and executes it. The mysqli::execute_query() method is a shortcut for mysqli::prepare() , mysqli_stmt::bind_param() , mysqli_stmt::execute() , and mysqli_stmt::get_result() .

The statement template can contain zero or more question mark ( ? ) parameter markers⁠—also called placeholders. The parameter values must be provided as an array using params 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 the mysqli object and is available using its methods, e.g. mysqli_affected_rows() or mysqli_error() .

On Linux returns an error code of 1153. The error message means got a packet bigger than max_allowed_packet bytes .
On Windows returns an error code 2006. This error message means server has gone away .

Parameters

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

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 a WHERE clause to specify a comparison value. However, they are not permitted for identifiers (such as table or column names).

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 on failure. For successful queries which produce a result set, such as SELECT, SHOW, DESCRIBE or EXPLAIN , returns a mysqli_result object. For other successful queries, returns true .

Examples

Example #1 mysqli::execute_query() example

mysqli_report ( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
$mysqli = new mysqli ( ‘localhost’ , ‘my_user’ , ‘my_password’ , ‘world’ );

$query = ‘SELECT Name, District FROM City WHERE CountryCode=? ORDER BY Name LIMIT 5’ ;
$result = $mysqli -> execute_query ( $query , [ ‘DEU’ ]);
foreach ( $result as $row ) printf ( «%s (%s)\n» , $row [ «Name» ], $row [ «District» ]);
>

mysqli_report ( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
$link = mysqli_connect ( «localhost» , «my_user» , «my_password» , «world» );

$query = ‘SELECT Name, District FROM City WHERE CountryCode=? ORDER BY Name LIMIT 5’ ;
$result = mysqli_execute_query ( $link , $query , [ ‘DEU’ ]);
foreach ( $result as $row ) printf ( «%s (%s)\n» , $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)

User Contributed Notes

Источник

How to Execute MySQL Query in PHP

After you have successfully made a new database connection in PHP, you can execute MySQL queries from the PHP code itself. Store the query in a variable as a string. Then, you can use mysqli_query() to perform queries against the database.

1. Create Database

// Create database
$sql = "CREATE DATABASE myDB";
if ($conn->query($sql) === TRUE) echo "Database created successfully";
> else echo "Error creating database: " . $conn->error;
>

mysqli_error() returns the last error description for the most recent function call.

2. Insert Data

$sql ;

if (mysqli_query($conn, $sql)) echo "New record created successfully";
> else echo "Error: " . $sql . "
" . mysqli_error($conn);
>

3. Get ID of Last Inserted Record

If you perform an INSERT or UPDATE on a table with an AUTO_INCREMENT field, you can get the ID of the last inserted or updated record immediately.

$sql ;

if (mysqli_query($conn, $sql)) $last_id = mysqli_insert_id($conn); 
echo "New record created successfully. Last inserted ID is: " . $last_id;
> else echo "Error: " . $sql . "
" . mysqli_error($conn);
>

4. Select Data

First, you make an SQL query that selects the id, firstname and lastname columns from the users table. The next line of code runs the query and puts the resulting data into a variable called $result.

Then, the function num_rows() checks if there are more than zero rows returned. If there are more than zero rows returned, the function fetch_assoc() puts all the results into an associative array that you can loop through. The while() loop loops through the result set and outputs the data from the id, firstname and lastname columns.

$sql = "SELECT id, firstname, lastname FROM users";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) // output data of each row 
while($row = mysqli_fetch_assoc($result)) echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "
"; 
>
> else echo "0 results";
>

5. Delete Data

The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted.

// sql to delete a record
$sql = "DELETE FROM users WHERE />
if (mysqli_query($conn, $sql)) echo "Record deleted successfully";
> else echo "Error deleting record: " . mysqli_error($conn);
>

6. Update Data

The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated.

$sql = "UPDATE users SET lastname='Doe' WHERE />
if (mysqli_query($conn, $sql)) echo "Record updated successfully";
> else echo "Error updating record: " . mysqli_error($conn);
>

7. Limit Data Selections

MySQL provides a LIMIT clause that is used to specify the number of records to return. The LIMIT clause makes it easy to code multi page results or pagination with SQL, and is very useful on large tables.

Join for Tips and Updates

Источник

Execute sql queries php