MySQL select rows problem

[madcgimonk]

I have a problem with the snippet below resulting in the error " fetch() without execute() at login.cgi line 69. ".

I posted this on another forum and their response was:
"At first glance the logic seems OK to me.
I do have some trouble with the $sth->rows

To get the number of records returned of a select statement one should not rely on the rows function. For one it is not guaranteed to work on all databases and for second its use should be restricted to non-select statements.

From the DBI-docs:

rows

$rv = $sth->rows;
Returns the number of rows affected by the last row affecting command, or -1 if the number of rows is not known or not available.

Generally, you can only rely on a row count after a non-SELECT execute (for some specific operations like UPDATE and DELETE), or after fetching all the rows of a SELECT statement.

For SELECT statements, it is generally not possible to know how many rows will be returned except by fetching them all. Some drivers will return the number of rows the application has fetched so far, but others may return -1 until all rows have been fetched. So use of the rows method or $DBI::rows with SELECT statements is not recommended.

One alternative method to get a row count for a SELECT is to execute a ``SELECT COUNT(*) FROM ...'' SQL statement with the same ``...'' as your query and then fetch the row count from that.

Running a SELECT count(*) ... will work and is very fast.

Anyhow, if I understand your code correctly, you only expect one record anyhow (or none if the log-in failed). So why don't you check if the result of the SELECT count(*) ... is 1, in which case you retrieve the data to set the cookies, or in all other cases (no records or mulltiple records) you raise an error.
"


The snippet is below

	if ($sth->rows < 1)
		{
			print "Content-type: text/html\n\n";
			print "Login information incorrect.";
			$dbh->disconnect;
			print "<script>window.location = 'login.cgi';</script>\n";
			exit;
		}

Can someone correct my code so it's what this person was asking? I don't know MySQL and have no idea what they meant.

 

[arn0ld]

when you say "on another forum", was this the tek-tips MySql forum?

 

[madcgimonk]

No, it was on perlmonks.org and I asked the person who said that enough questions to drive someone nuts for a while, I'd hate to ask him for even more help.

 

[PaulTEG]

You're calling the function

$sth->rows

and the advice you were given was to execute a query along the lines of SELECT COUNT(*) FROM tablename WHERE user=$user which should return an accurate count, and you can get result of the query as you would any other, though don't forget to capture your values before calling another query.

For a simple quick query like this you shouldn't need to prepare, just build up the query string on a new handle to the database and execute
--Paul

cigless ...

 

[madcgimonk]

LOL. see, even that is above and beyond me. I have no MySQL background and I've been stuck trying to fix this old site that uses MySQL for the past three weeks and almost.

Thanks for your help!

 

[arn0ld]

in three weeks, you found no tutorial sites for Mysql?
at this point, learning the basics might save another 3 weeks