MySQLi Function in PHP: Part 10

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

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

 

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


 

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

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