Simple way to read single record from MySQL

$id = mysql_result(mysql_query("SELECT id FROM games LIMIT 1"),0);

$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']

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 ...');
$id = $stmt->fetchColumn(0);
if ($id !== false) {
    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)

Assuming you are using an auto-incrementing primary key, which is the normal way to do things, then you can access the key value of the last row you put into the database with:

$userID = mysqli_insert_id($link);

otherwise, you'll have to know more specifics about the row you are trying to find, such as email address. Without knowing your table structure, we can't be more specific.

Either way, to limit your SELECT query, use a WHERE statement like this: (Generic Example)

$getID = mysqli_fetch_assoc(mysqli_query($link, "SELECT userID FROM users WHERE something = 'unique'"));
$userID = $getID['userID'];

(Specific example) Or a more specific example:

$getID = mysqli_fetch_assoc(mysqli_query($link, "SELECT userID FROM users WHERE userID = 1"));
$userID = $getID['userID'];

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.

Simple way to read single record from MySQL

Related

Recent Posts