check in mysql DB

[PB0711]

Hello all ,

First THX. Second I have a program that looks through a file
extracting data and then puts it into a database. I thought I had
designed a nice check with the select * from table where name = $name
and number = $num; . Then I had an if statement to see if that
reported anything but of course it did cus sql gives the nothing found
stuff. I know this is pretty easy but I cannot think of a way to do.
So can someone suggest a way to check if the entry already exist.

Thank you,

PB

 

[Brian McCauley]

Hello all ,

First THX. Second I have a program that looks through a file
extracting data and then puts it into a database. I thought I had
designed a nice check with the select * from table where name = $name
and number = $num; . Then I had an if statement to see if that
reported anything but of course it did cus sql gives the nothing found
stuff.

Please produce a minimal but complete script illustrate what you mean
by "sql gives the nothing found stuff."

If you try to read one row and there isn't one then there wasn't one.

my $sth = $db->prepare('select * from table where name = ? and number
= ?');
$sth->execute($name,$num);
my $exists = !!$sth->fetchrow_arrayref;
$sth->finish;

unless ($exist) {
#....
}

You can simplify this using DBI's convenience method
selectrow_arrayref

my $exists = !!$db->selectrow_arrayref('select * from table where name
= ? and number = ?',{},$name,$num);

Alternatively count the rows:

my $rows = $db->selectrow_array('select count(*) from table where name
= ? and number = ?',{},$name,$num);

I know this is pretty easy but I cannot think of a way to do.

There are many more.

 

[DJ Stunks]

Hello all ,

First THX.

Did George Lucas pay you to say that?

Second I have a program that looks through a file
extracting data and then puts it into a database. I thought I had
designed a nice check with the select * from table where name = $name
and number = $num; . Then I had an if statement to see if that
reported anything but of course it did cus sql gives the nothing found
stuff. I know this is pretty easy but I cannot think of a way to do.
So can someone suggest a way to check if the entry already exist.

define a UNIQUE index and use INSERT IGNORE

-jp

 

[PB0711]

What I was meaning by the SQL found nothing stuff is
" mysql> select * from metabolite where molid > 50000;
Empty set (0.00 sec)"
I was trying to see if it reported something back and if it didn't
then I assumed that it was emtpy and I could add stuff. But it always
of course gives the above.
Thank you for the help I see where to go with the check now.

Cheers,

Paul

 

[J. Gleixner]

What I was meaning by the SQL found nothing stuff is
" mysql> select * from metabolite where molid > 50000;
Empty set (0.00 sec)"
I was trying to see if it reported something back and if it didn't
then I assumed that it was emtpy and I could add stuff. But it always
of course gives the above.

Of course, using the mysql client really has nothing to
do with this news group.

Thank you for the help I see where to go with the check now.

That's not really a good query to use to test if something
exists or not, because it'll return everything from that table
for the values in the where.

To see if something is found, have SQL do the work:

select count(*) from...
or
select 1 from ...

That will return 0, if nothing, 1 or more, if using count, when
there are results.

You could also use the rows method from DBI to do something
based on the results too.
#.. select column from table where...
if( $sth->rows > 0 ) { #found results.. do something with them }
else { #query returned no results.. error or insert }


Depending on what you want to do, there's also a 'replace'
command in MySQL, which might be useful.