Brad Duncan Brad Duncan - 1 month ago 8
MySQL Question

Simple way to read single record from database using php and MYSQLI

How to change PHP to read a single record from a database using php and mysqli?
This is what I currently have:

$description = mysql_result(mysql_query("SELECT description FROM usermeta WHERE meta_key = 'description' AND user_id = 1 LIMIT 1"),0);


How should I change it to use mysqli

Answer

Here's a simple mysqli solution for you:

$db = new mysqli('localhost','user','password','database');
$resource = $db->query('SELECT field FROM table WHERE 1');
$row = $resource->fetch_assoc();
echo "{$row['field']}";
$resource->free();
$db->close();

If you're grabbing more than one row, I do it like this:

$db = new mysqli('localhost','user','password','database');
$resource = $db->query('SELECT field FROM table WHERE 1');
while ( $row = $resource->fetch_assoc() ) {
    echo "{$row['field']}";
}
$resource->free();
$db->close();

With Error Handling: If there is a fatal error the script will terminate with an error message.

// ini_set('display_errors',1); // Uncomment to show errors to the end user.
if ( $db->connect_errno ) die("Database Connection Failed: ".$db->connect_error);
$db = new mysqli('localhost','user','password','database');
$resource = $db->query('SELECT field FROM table WHERE 1');
if ( !$resource ) die('Database Error: '.$db->error);
while ( $row = $resource->fetch_assoc() ) {
    echo "{$row['field']}";
}
$resource->free();
$db->close();

Fetch a single record: This code does not require a loop.

$db = new mysqli('localhost','user','password','database');
$resource = $db->query('SELECT field FROM table');
$row = $resource->fetch_assoc();
echo "{$row['field']}";
$resource->free();
$db->close();

Using iterators: Support was added with PHP 5.4

$db = new mysqli('localhost','user','password','database');
foreach ( $db->query('SELECT * FROM table') as $row ) {
    print_r($rows);//echo "{$row['field']}";
}
$db->close();
Comments