Call to undefined function mysql_query
The following factors may be to responsible for the present undefined mysql query fatal error:
- PHP application upgraded from an old version (4 or 5) to PHP 7.
- Using an out-of-date plugin that is incompatible with the most recent WordPress version (with PHP 7 version).
Do any of the explanations given above apply to your situation?
Most likely YES:).
Question here: Why isn't mysql_query supported by the most recent version of PHP?
The key reasons are:
- Mysql_ functions do not support the latest MySQL features.
- Doesn’t support prepared statements and parameterized queries.
- Doesn't 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:
Table of contents
- Connect Database and perform Query using MySQLi
- Connect Database & perform Query using PDO
- The functions used were a mix of mysql and mysqli. Is that correct?
- To brush up on your knowledge on this subject, take the MCQ at the End of this Article.
- Feedback: Your input is valuable to us. Please provide feedback on this article.
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 )
PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide (Sponsored)
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.
Foundations of Algorithms (Sponsored)
3. The functions used were a mix of mysql and mysqli. Is that correct?
One of the possible causes of undefined mysql functions is this. Developers will sometimes copy and paste portions of code from an online tutorial into their own code.
As an example,
Using the Database Connections object with the mysql function, but querying with the mysql improved (mysqli) functions, or vice versa. As a result, rather than seeking assistance through an online portal, we strongly advise looking into your code to determine why issues occur.
Diagnosing issues and determining the source of problems is also a valuable learning experience for us.