Php classes mysqli connection

The mysqli class

Represents a connection between PHP and a MySQL database.

Class synopsis

public __construct (
? string $hostname = null ,
? string $username = null ,
? string $password = null ,
? string $database = null ,
? int $port = null ,
? string $socket = null
)

public connect (
? string $hostname = null ,
? string $username = null ,
? string $password = null ,
? string $database = null ,
? int $port = null ,
? string $socket = null
): bool

public static poll (
? array &$read ,
? array &$error ,
array &$reject ,
int $seconds ,
int $microseconds = 0
): int | false

public real_connect (
? string $hostname = null ,
? string $username = null ,
? string $password = null ,
? string $database = null ,
? int $port = null ,
? string $socket = null ,
int $flags = 0
): bool

public ssl_set (
? string $key ,
? string $certificate ,
? string $ca_certificate ,
? string $ca_path ,
? string $cipher_algos
): true

Table of Contents

• mysqli::$affected_rows — Gets the number of affected rows in a previous MySQL operation
• mysqli::autocommit — Turns on or off auto-committing database modifications
• mysqli::begin_transaction — Starts a transaction
• mysqli::change_user — Changes the user of the specified database connection
• mysqli::character_set_name — Returns the current character set of the database connection
• mysqli::close — Closes a previously opened database connection
• mysqli::commit — Commits the current transaction
• mysqli::$connect_errno — Returns the error code from last connect call
• mysqli::$connect_error — Returns a description of the last connection error
• mysqli::__construct — Open a new connection to the MySQL server
• mysqli::debug — Performs debugging operations
• mysqli::dump_debug_info — Dump debugging information into the log
• mysqli::$errno — Returns the error code for the most recent function call
• mysqli::$error_list — Returns a list of errors from the last command executed
• mysqli::$error — Returns a string description of the last error
• mysqli::execute_query — Prepares, binds parameters, and executes SQL statement
• mysqli::$field_count — Returns the number of columns for the most recent query
• mysqli::get_charset — Returns a character set object
• mysqli::$client_info — Get MySQL client info
• mysqli::$client_version — Returns the MySQL client version as an integer
• mysqli::get_connection_stats — Returns statistics about the client connection
• mysqli::$host_info — Returns a string representing the type of connection used
• mysqli::$protocol_version — Returns the version of the MySQL protocol used
• mysqli::$server_info — Returns the version of the MySQL server
• mysqli::$server_version — Returns the version of the MySQL server as an integer
• mysqli::get_warnings — Get result of SHOW WARNINGS
• mysqli::$info — Retrieves information about the most recently executed query
• mysqli::init — Initializes MySQLi and returns an object for use with mysqli_real_connect()
• mysqli::$insert_id — Returns the value generated for an AUTO_INCREMENT column by the last query
• mysqli::kill — Asks the server to kill a MySQL thread
• mysqli::more_results — Check if there are any more query results from a multi query
• mysqli::multi_query — Performs one or more queries on the database
• mysqli::next_result — Prepare next result from multi_query
• mysqli::options — Set options
• mysqli::ping — Pings a server connection, or tries to reconnect if the connection has gone down
• mysqli::poll — Poll connections
• mysqli::prepare — Prepares an SQL statement for execution
• mysqli::query — Performs a query on the database
• mysqli::real_connect — Opens a connection to a mysql server
• mysqli::real_escape_string — Escapes special characters in a string for use in an SQL statement, taking into account the current charset of the connection
• mysqli::real_query — Execute an SQL query
• mysqli::reap_async_query — Get result from async query
• mysqli::refresh — Refreshes
• mysqli::release_savepoint — Removes the named savepoint from the set of savepoints of the current transaction
• mysqli::rollback — Rolls back current transaction
• mysqli::savepoint — Set a named transaction savepoint
• mysqli::select_db — Selects the default database for database queries
• mysqli::set_charset — Sets the client character set
• mysqli::$sqlstate — Returns the SQLSTATE error from previous MySQL operation
• mysqli::ssl_set — Used for establishing secure connections using SSL
• mysqli::stat — Gets the current system status
• mysqli::stmt_init — Initializes a statement and returns an object for use with mysqli_stmt_prepare
• mysqli::store_result — Transfers a result set from the last query
• mysqli::$thread_id — Returns the thread ID for the current connection
• mysqli::thread_safe — Returns whether thread safety is given or not
• mysqli::use_result — Initiate a result set retrieval
• mysqli::$warning_count — Returns the number of warnings from the last query for the given link

User Contributed Notes

• MySQLi
  • Introduction
  • Overview
  • Quick start guide
  • Installing/Configuring
  • The mysqli Extension and Persistent Connections
  • Predefined Constants
  • Notes
  • The MySQLi Extension Function Summary
  • mysqli
  • mysqli_​stmt
  • mysqli_​result
  • mysqli_​driver
  • mysqli_​warning
  • mysqli_​sql_​exception
  • Aliases and deprecated Mysqli Functions
  • Changelog

  Источник

  PHP mysqli class to connect with MySQL database

  PHP mysqli class is one of the multiple ways to connect with MySQL database using PHP. In this post we will learn about PHP mysqli class, it’s useful methods and properties.

  Following logical steps will show you how to use mysqli class to access MySQL database in PHP:

  1. Connecting with MySQL database using PHP
  2. Creating a Table in MySQL Database using PHP mysqli
  3. Inserting data in MySQL database using mysqli class
  4. Update data using mysqli class in PHP
  5. Fetch Associated array from MySQL table
  6. Fetch table row as object using mysqli class

  Connecting with MySQL database using PHP mysqli

  To establish connection, we need to create an object of PHP mysqli class. Once the object is created, we will check it’s connect_errno property. If connection is successful, connect_errno property will hold 0 otherwise it will hold the MySQL error code as described here.

  $mysqli = new mysqli('127.0.0.1','root','','weblearningblog'); /*connecting with mysql*/ if(!$mysqli->connect_errno) < echo "Connection Successful"; >else < echo $mysqli->connect_error; > /*connecting end*/

  Notice the order of the parameters of mysqli constructor, it is host,username,password and database name respectively.

  Creating a Table in MySQL Database using PHP mysqli

  To create a new table in database, we use query() method of mysqli class. This method returns FALSE if query fails. In queries, where a result set is expected, query() method will return result set and for other successful queries query() method returns TRUE.

  $mysqli = new mysqli('127.0.0.1','root','','weblearningblog'); /*creating a table starts*/ if($mysqli->connect_errno)< echo $mysqli->connect_error; > else < $sql = 'CREATE TABLE students( id INT NOT NULL AUTO_INCREMENT , name varchar(30) NOT NULL, study_program varchar(20) NOT NULL, department varchar(20), date_added TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) )'; $result = $mysqli->query($sql); if(!$result)< echo "Query Failed. ".$mysqli->error; > else < echo "Query Successfull"; >$mysqli->close();//close the connection >*/ /*creating a table ends*/

  CREATE TABLE is SQL command therefore, discussing it is not in the scope of this article. We store SQL query in a string, later this string is passed as first and only required parameter for mysqli query() method.

  Inserting data in MySQL database using mysqli class

  Inserting records in MySQL database using mysqli class is similar to the previous section as the process is same but we will use a different SQL Command INSERT INTO for this purpose.

  $mysqli = new mysqli('127.0.0.1','root','','weblearningblog'); if($mysqli->connect_errno)< echo $mysqli->connect_error; > else < $sql = 'INSERT INTO students (name,study_program,department) VALUES("John","BS","Computer Science")'; $result = $mysqli->query($sql); if(!$result)< echo "Query Failed. ".$mysqli->error; > else < echo "Successfully Inserted. ".$mysqli->affected_rows." Records"; > >

  The mysqli class affected_rows property is useful in insert and update operations as it hold the number of rows, which query has affected .i.e. in this case, it will hold 1 as this query is inserting one row in table.

  For inserting multiple records at once, we just need to change the SQL command INSERT INTO as follows:

  $mysqli = new mysqli('127.0.0.1','root','','weblearningblog'); if($mysqli->connect_errno)< echo $mysqli->connect_errno; > else < $sql = 'INSERT INTO students (name,study_program,department) VALUES ("John","BS","Computer Science"), ("Kane","MS","Computer Science"), ("Bob","PhD.","Physics"), ("Stewart","MBA","Computer Science") '; $result = $mysqli->query($sql); if(!$result)< echo "Query Failed. ".$mysqli->error; > else < echo "Successfully Inserted. ".$mysqli->affected_rows." Records"; > >

  Update data using mysqli class in PHP

  Updating the record in MySQL table using mysqli class is pretty straight forward.

  $mysqli = new mysqli('127.0.0.1','root','','weblearningblog'); if($mysqli->connect_errno)< echo $mysqli->connect_errno; > else < $sql = 'UPDATE students SET name="Kane" WHERE = $mysqli->query($sql); if(!$result)< echo "Query Failed. ".$mysqli->error; > else < echo "Successfully Updated. ".$mysqli->affected_rows." Records"; > >

  Fetch associated array from MySQL table using mysqli

  Reading data from MySQL table using PHP mysqli extension involves retrieving a result set from table and then iterate through it by fetching record as associative array one by one.

  $mysqli = new mysqli('127.0.0.1','root','','weblearningblog'); if($mysqli->connect_errno)< echo $mysqli->connect_errno; > else < $sql = 'SELECT * FROM students'; $result = $mysqli->query($sql); if(!$result)< echo "Query Failed. ".$mysqli->error; > else < if($result->num_rows > 0)< $html ; $html . ;//header row while($current_row = $result->fetch_assoc())< $html .= ''; $html .= ''.$current_row['id'].''; $html .= ''.$current_row['name'].''; $html .= ''.$current_row['study_program'].''; $html .= ''.$current_row['department'].''; $html .= ''; > $html . ; $result->free();//free the resultset memory echo $html; > else < echo "No Record Exists"; >> >

  As shown in code snippet above, the query() method is now returning a result set instead of TRUE or FALSE as in insert and update operations discussed in previous sections. Here we are getting the students records from MySQL table and creating a HTML table to view it in a browser.

  num_rows property of result set object is having the number of rows or records, this result set is consisting of, therefore it is a good idea to evaluate num_rows property before using the result set to read data.

  While reading data from database table, it is very important to learn how fetch_assoc() method of result set object works. Whenever we call fetch_assoc() method on result set object, it gives us one row and move internal data pointer to next row, which means if we call fetch_assoc() again, it will return next row and so on. Ultimately if we use fetch_assoc() in a while loop, it will execute on each iteration and returns a new row. If there is no record left fetch_assoc() will return NULL which will cause while loop to exit.

  As fetch_assoc() returns associative array, The key of the array is column name of the table and value of the array is the column value for current row. Building up of HTML table is straight forward and don’t need any explanation.

  Fetch table row as object using mysqli class

  To fetch record as object instead of associative array, we just have to replace fetch_assoc() method with fetch_object().

  while($current_row = $result->fetch_object())< $html .= ''; $html .= ''.$current_row->id.''; $html .= ''.$current_row->name.''; $html .= ''.$current_row->study_program.''; $html .= ''.$current_row->department.''; $html .= ''; >

  Conclusion

  In this tutorial, I tried to focus on practical and code oriented approach to teach this topic. Please share your valuable feedback by commenting.

  

  Follow us

  Источник