PHP - Simple way to read single record from MySQL

Question

What's the best way with PHP to read single record from Mysql please?

SELECT id FROM games

I was trying to find in old questions, but had no luck Thank you

Answer 1

First you connect to your database. Then you build the query string. Then you launch the query and store the result, and finally you fetch what rows you want from the result by using one of the fetch methods.

$link = mysql_connect('localhost','root','yourPassword')
mysql_select_db('database',$link);
$sql = 'SELECT id FROM games'
$result = mysql_query($sql,$link);

$singleRow = mysql_fetch_array($result) 
echo $singleRow;

Edit: So sorry, forgot the database connection. Added it now

Answer 2

$link = mysql_connect('localhost','root','yourPassword')
mysql_select_db('database_name', $link);
$sql = 'SELECT id FROM games LIMIT 1';
$result = mysql_query($sql, $link) or die(mysql_error());
$row = mysql_fetch_assoc($result);
print_r($row);

There were few things missing in ChrisAD answer. After connecting to mysql it's crucial to select database and also die() statement allows you to see errors if they occur.

Be carefull it works only if you have 1 record in the database, because otherwise you need to add WHERE id=xx or something similar to get only one row and not more. Also you can access your id like $row['id']

Answer 3

'Best way' aside some usual ways of retrieving a single record from the database with PHP go like that:

with mysqli

$sql = "SELECT id, name, producer FROM games WHERE user_id = 1";
$result = $db->query($sql);
$row = $result->fetch_row();

with Zend Framework

//Inside the table class

$select = $this->select()->where('user_id = ?', 1);  
$row = $this->fetchRow($select);

Answer 4

Using PDO you could do something like this:

$db = new PDO('mysql:host=hostname;dbname=dbname', 'username', 'password');

$stmt = $db->query('select id from games where ...');
while ($id = $stmt->fetchColumn(0)) {
    echo $id;
}

You obviously should also check whether PDO::query() executes the query OK (either by checking the result or telling PDO to throw exceptions instead)

Answer 5

Warning! Your SQL isn't a good idea, because it will select all rows (no WHERE clause assumes "WHERE 1"!) and clog your application if you have a large number of rows. (What's the point of selecting 1,000 rows when 1 will do?) So instead, when selecting only one row, make sure you specify the LIMIT clause:

$sql = "SELECT id FROM games LIMIT 1";  // Select ONLY one, instead of all
$result = $db->query($sql);
$row = $result->fetch_assoc();
echo 'Game ID: '.$row['id'];

This difference requires MySQL to select only the first matching record, so ordering the table is important or you ought to use a WHERE clause. However, it's a whole lot less memory and time to find that one record, than to get every record and output row number one.

Answer 6

The easiest way is to use mysql_result. I copied some of the code below from other answers to save time.

$link = mysql_connect('localhost','root','yourPassword')
mysql_select_db('database',$link);
$sql = 'SELECT id FROM games'
$result = mysql_query($sql,$link);

$num_rows = mysql_num_rows($result);

// i is the row number and will be 0 through $num_rows-1
for ($i = 0; $i < $num_rows; $i++) {
    $value = mysql_result($result, i, 'id');
    echo 'Row ', i, ': ', $value, "\n";
}

Answer 7

I really like the philosophy of the ezSQL database library, which wraps the native SQL methods in an easier-to-use interface.

Fetching a single value from the database is trivial:

  $id = $db->get_var("SELECT id FROM games WHERE ...");

It also makes it easy to fetch a single row, column, or set of rows.