In Focus

MySQLi Function in PHP: Part 10

In this article I describe the PHP MySQLi functions mysqli_num_rows, mysqli_options, mysqli_ping, mysqli_query and mysqli_real_connect.

Introduction

In this article I describe the PHP MySQLi functions mysqli_num_rows, mysqli_options, mysqli_ping, mysqli_query and mysqli_real_connect. To learn some other MySQLi functions, go to:

  1. MySQLi Function in PHP: Part 1
  2. MySQLi Function in PHP: Part 2
  3. MySQLi Function in PHP: Part 3
  4. MySQLi Function in PHP: Part 4
  5. MySQLi Function in PHP: Part 5
  6. MySQLi Function in PHP: Part 6
  7. MySQLi Function in PHP: Part 7
  8. MySQLi Function in PHP: Part 8
  9. MySQLi Function in PHP: Part 9
PHP mysqli_num_rows() Function

The PHP MySQLi "mysqli_num_rows" function gets the number of rows in a result and this function returns the number of rows in the result set.

Syntax

 
mysqli_num_rows(result);

Parameter in mysqli_num_rows function

The parameter of the function is:
 
Parameter Description
result It specifies the result set identifier returned by mysqli_query(), mtsqli_stored_result or mysqli_use_result().

Example

An example of the function is:

 

<?php

$con=mysqli_connect("localhost","root","","mysql");

if(mysqli_connect_errno($con))

{

echo "Falied to connect to MySQL: ".mysqli_connect_error();

}

$qry="SELECT id,name FROM emp";

if($result=mysqli_query($con,$qry))

{

//returns the number of rows in the result set

$rowCount=mysqli_num_rows($result);

printf("Result set has %d rows.",$rowCount);

// For freeinf the result set

mysqli_free_result($result);

print "<h2>MySQL: Data of Emp table</h2>";

$result =mysqli_query($con,"SELECT * FROM emp");

echo "<table border='1'>

<tr>

<th>Id</th>

<th>Name</th>

<th>Salary</th>

</tr>";

while($rowval = mysqli_fetch_array($result))

  {

  echo "<tr>";

  echo "<td>" . $rowval['id'] . "</td>";

  echo "<td>" . $rowval['name'] . "</td>";

  echo "<td>" . $rowval['salary'] . "</td>";

  echo "</tr>";

  }

 

echo "</table>";

}

print "<h2>The above table contains only 8 rows.</h2>";

mysqli_close($con);

?>

Output

mysqli-num-rows-function-in-php.jpg

PHP mysqli_options() Function

The PHP MySQLi "mysqli_options" function sets extra connection options and behavior for a connection and this function returns true on success or false on failure.

Syntax

mysqli_options(connection,option,value);

Parameter in mysqli_options function

The parameters of the function are:
 
Parameter Description
connection It specifies the MySQL connection to use.
options It specifies the option to set.
value It specifies the value for the option.

Example

An example of the function is:


<?php

$mysqli = mysqli_init();

//set connection options

$mysqli->options(MYSQLI_INIT_COMMAND, "SET AUTOCOMMIT=0");

$mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 5);

//connect to server */

$mysqli->real_connect('localhost', 'root', '', 'mysql');

if (mysqli_connect_errno())

{

    printf("Connect failed: %s\n", mysqli_connect_error());

    exit();

}

printf ("Connection: %s\n.", $mysqli->host_info);

$mysqli->close();

?>


Output

mysqli-options-function-in-php.jpg 

PHP mysqli_ping() Function

The PHP MySQLi "mysqli_ping" function pings a server connection or attempts reconnection if the connection has gone down and this function returns true on success or false on failure.

Syntax
 

mysqli_ping(connection);

Parameter in mysqli_ping function

The parameter of the function is:
 
Parameter Description
connection It specifies the MySQL connection to use.

Example

An example of the function is:

 

<?php

$con=mysqli_connect("localhost","root","","mysql");

if(mysqli_connect_errno($con))

{

echo "Connection Error: ".mysqli_connect_error();

}

if(mysqli_ping($con))

{

echo "Connection has been established successfully.";

}

else

{

echo "Error: ".mysqli_error($con);

}

mysqli_close($con);

?>

Output

mysqli-ping-function-in-php.jpg
 

PHP mysqli_query() Function

The PHP MySQLi "mysqli_query" function performs a query on the database and this function return a mysqli_result object on the SELECT, SHOW, DESCRIBE or EXPLAIN queries and for the other successful queries it will return true otherwise it returns false.

Syntax

mysqli_query(connection,query,resultMode);

Parameter in mysqli_query function

The parameters of the function are:
 
Parameter Description
connection It specifies the MySQL connection to use.
query It specifies a query string.
resultMode It specifies a constant.

Example

An example of the function is:


<?php

$con=mysqli_connect("localhost","root","","mysql");

if(mysqli_connect_errno($con))

{

echo "Connection Error: ".mysqli_connect_error();

}

$qry="SELECT * FROM emp";

$result =mysqli_query($con,$qry);

print "<h2>MySQL: Emp table Data</h2>";

echo "<table border='1'>

<tr>

<th>Id</th>

<th>Name</th>

<th>Salary</th>

</tr>";

while($rowval = mysqli_fetch_array($result))

  {

  echo "<tr>";

  echo "<td>" . $rowval['id'] . "</td>";

  echo "<td>" . $rowval['name'] . "</td>";

  echo "<td>" . $rowval['salary'] . "</td>";

  echo "</tr>";

  }

echo "</table>";

mysqli_close($con);

?>

Output

mysqli-query-function-in-php.jpg

PHP mysqli_real_connect() Function

The PHP MySQLi "mysqli_real_connect" function opens a connection to a MySQL server and this function returns true on success or false on failure.

Syntax

mysqli_real_connect(connection,host,username,password,dbname,port,socket,flag);

Parameters of the mysqli_real_connect function

The parameters of the function are:
 
Parameter Description
connection It specifies the MySQL connection to use.
host It specifies a host name or IP address.
username It specifies the MySQL user name.
password It specifies the MySQL password.
dbname It specifies the default database to be used.
port It specifies the port number to attempt to connect to the MySQL server.
socket It specifies the socket to be used.
flag It specifies various connection options.

Example

An example of the function is:


<?php

$con=mysqli_init();

if(!$con)

{

die("Failed");

}

if(mysqli_real_connect($con,"localhost","root","","mysql"))

{

echo "Connection has been established successfully.";

}

else

{

echo "Error: ".mysqli_error($con);

}

mysqli_close($con);

?>

Output

mysqli-real-connect-function-in-php.jpg