From: Ken Bloom <kbloom@...>
Date: 2007-03-28T05:40:08+09:00
Subject: Re: Ruby/MySQL SELECT results

On Mon, 26 Mar 2007 22:19:04 +0900, Paul Willis wrote:

> Hi
> 
> I am playing with Ruby/MySQL, I have everything installed ok. I can
> INSERT into my database but I am having trouble with SELECT.
> 
> I want to see if the text in the field 'title' has been used before and
> if it has return the id of that row. If I run...
> 
> require "mysql"
> my = Mysql::new("hostname", "username", "password", "db")
> hasid =  my.query("SELECT id FROM table WHERE 
>    title = 'Here is a title'")
> puts hasid
> 
> I get a result like...
> 
> #<Mysql::Result:0x55c74c>
> 
> I am expecting just the number in the id field. Any ideas?
> 
> Paul
>
hasid,=my.query("SELECT id FROM table WHERE title = 'Here is a 
title'").fetch_row

fetch_row gives you an array with the row in it.

by putting several variables with commas between them, you can unpack the 
array into those variables -- if there's only one element, you can 
achieve the same behavior by specifying one variable to assign to, 
followed by a comma.

This approach leaves a MySQL::Result object lying around waiting to be 
garbage collected before it is closed. This may be wasteful -- 
personally, I prefer to use DBI instead of MySQL directly, and DBI has a 
good way to take care of this cleanup automatically:

require 'dbi'
dbh=DBI.connect('DBI:mysql:db:hostname','username','password')
hasid,=dbh.select_one("SELECT id FROM table WHERE title = 'Here is a 
title'")
puts hasid

-- 
Ken Bloom. PhD candidate. Linguistic Cognition Laboratory.
Department of Computer Science. Illinois Institute of Technology.
http://www.iit.edu/~kbloom1/