PHP MySQL Query- Creating Queries to Access Database

A PHP MySQL query is  database query given as a MySQL query command. The MySQL queries can be created within the PHP code and executed on the Connection created as discussed in last topic. The various activities and related queries are

  • CREATE DATABASE/TABLE- to create a new database or a new table in previously created MySQL Database
  • DROP TABLE- This query is written to permanently delete a table from database. It removes data and the table structure. This query must be used with care as once deleted table cannot be retrieved back.
  • SELECT- This SQL query is used to fetch data to display or perform some data manipulation.
  • INSERT- This query is used to insert one row or record in a table.
  • DELETE- This query is used to deleted some or all data rows from a table
  • UPDATE- This SQL query is used to update one of more field of one or more rows or records in a table.

Note-It is always advisable to take a backup of your MySQL database using the Export Option before deleting data from tables or dropping tables from database.

Making PHP MySQL Query

The mysqli_query() function executes the user defined or created query on database returning a Boolean value.

Syntax of mysqli_query function

mysqli_query(connection,query,resultmode);

  • Connection is a handler or a link identifier returned by mysqli_connect() function that is used for establishing a connection with the database server.
  • Query is a SQL query in string data type. If you do not escape the data inside query properly query will fail to run while generating an error.
  • Resultmode- the result of query after execution return either of two constants MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT. By default, MYSQLI_STORE_RESULT is taken.

The mysqli_query function return FALSE on failure. If you wish to execute queries like SELECT, SHOW, DESCRIBE or EXPLAIN, mysqli_query() will return a mysqli_result object. Other successfully executed queries mysqli_query() will return TRUE.

Example mysqli_query function

 <?php
$conn = mysqli_connect("localhost", "root ", "", "myUserDB");

/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}

if ($result = mysqli_query($conn, "SELECT usernam, password FROM userdata ORDER BY usernam")) {

/* determine number of rows result set */
$row_cnt = mysqli_num_rows($result);

printf("Result set has %d rows.\n", $row_cnt);

/* close result set */
mysqli_free_result($result);
}

/* close connection */
mysqli_close($conn);
?>

PHP Functions associated with PHP MySQL Query

  • mysqli_affected_rows ()— returns the number of rows affected by last successful query operation. It works after execution of INSERT, UPDATE, REPLACE or DELETE queries.
  • mysqli_num_rows()-returns the number of rows fetched after execution of a SELECT query. The behavior of mysqli_num_rows() depends on whether buffered or unbuffered result sets are being used. For unbuffered result sets, mysqli_num_rows() will not return the correct number of rows until all the rows in the result have been retrieved.