Assign MySQL database value to PHP variable

Assign MySQL database value to PHP variable

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Assign MySQL Database Value to PHP Variable

The code snippet you are looking at demonstrates a fundamental misunderstanding of how database query functions return data in older PHP environments. The line $price = mysql_query("SELECT price FROM products WHERE product = '$product'"); does not assign the actual price value to the variable. Instead, it executes the SQL command and returns a resource handle—specifically Resource id #5—which represents the result set of the query. This behavior is consistent with the legacy mysql_* extension, which treats queries as procedural operations rather than returning structured data objects directly.

To extract the actual value from the database, you must fetch it from the result resource using a specific function. The choice between mysqli_fetch_assoc, mysqli_fetch_array, and other methods depends entirely on how your PHP version handles database connections. The legacy mysql_* functions are deprecated as of PHP 5.5 and removed entirely in PHP 7.0. If you are maintaining legacy code, you can use mysql_result($result, 0) to grab the first column of the first row, but this is no longer a recommended practice.

Fetching Data with MySQLi

The standard modern approach involves switching to the mysqli extension or PDO. With mysqli, if you are using object-oriented syntax (which is also available in procedural style), the workflow changes slightly. The query function returns a result set, not the data itself. You must call a fetch method on that result set to retrieve the row as an associative array.

Here is how you would implement this correctly using mysqli:

$price = null;

try {
    $conn = new mysqli($host, $username, $password, $database);
    
    // Prepare the statement to prevent SQL injection
    $stmt = $conn->prepare("SELECT price FROM products WHERE product = ?");
    $stmt->bind_param("s", $product);
    $stmt->execute();
    
    $result = $stmt->get_result();
    
    if ($row = $result->fetch_assoc()) {
        $price = $row['price'];
    } else {
        // Handle case where product was not found
        $price = 0; 
    }
    
    $stmt->close();
} catch (mysqli_sql_exception $e) {
    // Handle database errors gracefully
    echo "Database error: " . $e->getMessage();
}

In this example, fetch_assoc() returns an associative array where keys correspond to column names. Accessing $row['price'] yields the actual numeric or string value stored in the database. If you use mysqli_fetch_array, the variable will contain both indexed and named columns, but fetch_assoc is generally preferred for readability and safety.

The Laravel Context

If your project architecture allows it, moving away from raw SQL queries entirely can simplify data retrieval logic significantly. Frameworks like Laravel abstract these details away by providing Eloquent ORM models. Instead of writing manual query strings and handling result sets manually, you define a Product model where the price attribute maps directly to the database column.

use App\Models\Product;

// Assuming $product variable contains the product name (slug or name)
$price = Product::where('product', $product)->first()->price ?? 0;

This approach ensures that the application handles connection management, query optimization, and error handling internally. It also mitigates the risk of SQL injection, as the framework automatically binds parameters safely. If you are starting a new project or refactoring legacy code, leveraging such patterns is often more robust than managing raw mysqli resources manually.

Security Considerations

A critical pitfall in your original example is the direct interpolation of the $product variable into the SQL string: WHERE product = '$product'. This is vulnerable to SQL injection attacks if the input contains malicious characters. Even though the immediate goal was simply fetching a value, any code path that accepts user input must sanitize it or use prepared statements. The legacy mysql_query function offers no protection against this without manual escaping, which is error-prone. Switching to PDO or mysqli with prepared statements, as shown above, is the mandatory solution for secure data access.

Summary of Implementation Paths

  1. Legacy Code: If you must stay on PHP < 7.0 and use the mysql_* extension, use mysql_result($result, 0) to get the first value, though this is obsolete.
  2. Modern Procedural: Use mysqli_query, store the result in a variable, then call mysqli_fetch_assoc($result) to get an array, and access the key.
  3. Best Practice: Switch to PDO or use an ORM like Laravel. This removes the boilerplate of fetching rows manually and enforces secure query execution patterns by default.

Our network