Call to undefined function mysql_query
The possible cause behind the current undefined mysql_query fatal error arises due to the following reasons:
- PHP application upgraded from an old version (4 or 5) to PHP 7.
- Using an outdated plugin that does not support the latest WordPress version (with PHP 7 version).
Do any of the above-described reasons match your case?
Probably YES :).
Question here: why mysql_query is not supported in the latest PHP version?
The key reasons are:
- Mysql_ functions do not support the latest MySQL features.
- Doesn’t support prepared statements and parameterized queries.
- Does provide an object-oriented approach
- Stored procedures are not supported
- Does not have better support for Transactions
- Debugging is not so easy
The error call to undefined function will also be reported for below functions once upgraded to PHP 7
- mysql_connect()
- mysql_fetch_array()
- mysql_select_db()
And other MySQL functions.
To overcome fatal error call to undefined function mysql_query we have the following solution:
- Use MySQL improved extension (MySQLi)
- Use PHP Data Objects (PDO)
Let’s understand how can we use MySQLi & PDO as a mysql_ function replacement with the following steps with a code example:
1. Connect Database and perform Query using MySQLi
Find here for installation guide on php.net
Assuming we have already installed/Configured MySQLi. Next, Let’s see how can we perform the following in MySQLi
- Connect Mysql Database
- Query to Database tables
Below program demonstrates MySQLi with a prepared statement. Let’s dive into code:
$host = "localhost"; // Specify your server Host/IP address
$username = "username"; // Replace your Database username here
$password = "password"; // Replace your Database password here
$db= "demo"; //Replace your database name here
$con = new mysqli($host, $username, $password, $db);
// Ensure if MySQLi connection works
if ($con->connect_error) {
echo "Failed to connect Database: " . $con->connect_error;
exit;
}
$id = filter_var( $_POST['id'], FILTER_SANITIZE_NUMBER_INT);
$sql = "SELECT email, name FROM tbl_employee WHERE id =?"; // SQL with parameters
$stmt = $con->prepare($sql);
$stmt->bind_param("i", $id);
$stmt->execute();
$result = $stmt->get_result(); // get the mysqli result
print_r($result ); // Added For demonstration purpose
$user = $result->fetch_assoc(); // fetch data
print_r($user);
Explanation:
new mysqli($servername, $username, $password, $db)Open database connection by passing required parameters to mysqli constructor.- It's good practice to check whether a connection is successful before processing further query execution. Therefore using here $conn->connect_error to check if there is an error in the database connection.
- Assuming here that we received input data as id through the post method. Sanitizing received data (especially if it's user input) to filter out non-number values to avoid any malicious code.
- Prepare the desired query using the Mysqli prepare method. For demonstration purposes we are executing a query to fetch email & name from tbl_employee table having id matched with the receive post request. ? mark in the Prepared query is added that will be bound with mapping data.
- Mysqli execute method is further used to perform prepared query execution with a marker (?) replaced with a data value.
- Mysqli get_result method used to get prepared query execution results. If you print returned result it will constraints some information like field_count, num_rows, etc mysqli_result Object ( [current_field] => 0 [field_count] => 2 [lengths] => [num_rows] => 1 [type] => 0 )
- Next Mysqli method fetch_assoc is used here to get the result in associative array format. Example Array ( [email] => test@gmail.com [name] => test )
2. Connect Database & perform Query using PDO
Refer here for installing/configuring PDO
Let’s go through the following code demonstrating
- Database connection using PDO
- Query to Database tables
Here is the simple PHP program to give us an overview.
$host = "localhost"; // Specify your server Host/IP address
$uname = "username"; // Replace your Database username here
$pwd = "password"; // Replace your Database password here
$db= "demo"; //Replace your database name here
try {
$id = filter_var($_POST['id'], FILTER_SANITIZE_NUMBER_INT);
$conn = new PDO("mysql:host=$host; dbname=$db", $uname, $pwd);
$sql = "select name, email from tbl_employee where id = ?";
$stmt = $conn->prepare($sql);
$stmt->execute(array($id));
$user = $stmt->fetch(PDO::FETCH_ASSOC);
print_r($user);
} catch (PDOException $e) {
echo $e->getMessage();
}
Explanation:
- Sanitized user input. Pls refer detail in previous example
-
new PDO("mysql:host=$host; dbname=$db", $uname, $pwd)used to create an PDO instance by specifying required constructor arguments to make database connections. - PDO method prepare as the name explains is used to prepare for a specifying query statement. Marker ? in prepared query is added and bound by data value once the execute method performs query execution.
-
The fetch method is used to retrieve the next row from the executed query result set.
Param
PDO::FETCH_ASSOCis mentioned here to expect data return in associative array format with key as a table column name. - Print row results for demonstration purposes. You may try this code by replacing appropriate values.
- Added try & catch block to catch any error raised by PDO like database connection error and others.