SELECT single record

[gandalf458]

I'm using SQLite and I have the following to get a single record:

$query = 'SELECT * FROM talks WHERE id=:id;';
$stmt = $db->prepare($query);
$stmt->bindValue(':id', $id);
$stmt->execute();
$result = $stmt->fetchAll();
foreach ( $result as $row ) {
  $address = $row['address'];
  $num_views = $row['num_views'];
}

I'm not sure this is right but I'm not sure what I should have. Can anyone please help?

I'm not a number, I'm a free man

 

[gandalf458]

Okay. Dimwit that I am, this seems to do the job:

$query = 'SELECT * FROM talks WHERE id=:id;';
$stmt  = $db->prepare($query);
$stmt->bindValue(':id', $id);
$stmt->execute();
$result = $stmt->fetch();
$address = $result['address'];
$num_views = $result['num_views'];

I'm not a number, I'm a free man

 

[spamjim]

Your original code is best if you limited the query.

http://www.tutorialspoint.com/sqlite/sqlite_limit_clause.htm

SELECT * FROM talks WHERE id=$id LIMIT 1;

Your "answer" still has the potential to select a lot of extra data but you're making PHP only process the first part of it. It would be more efficient to query only the data you need and then run your original PHP.

 

[gandalf458]

Thanks for your reply. Is using fethcAll() and a foreach loop really more efficient than fetch() and no foreach loop? It sounds counter-intuitive.

I'm not a number, I'm a free man

 

[spamjim]

My wording was not ideal. I think the "efficiency" I was thinking was your own...in not having to bother with writing alternative PHP to handle only one record.

Once you limit your query, you can process with either method in PHP. The performance difference between these two PHP options should be negligible with just one record.

 

[gandalf458]

Many thanks for explaining.

I'm not a number, I'm a free man