1

Possible Duplicate:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result

I have next class: 

<?php
    class DbHelper{
        private $databaseURL;
        private $databaseUName;
        private $databasePWord;
        private $databaseName;
        private $nameOfDbWithWorkers;
        private $connection;

        function __construct($dbURL, $dbUserName, $dbPword, $dbName, $nameOfDbWithWorkers){
            $this->databaseURL = $dbURL;
            $this->databaseUName = $dbUserName;
            $this->databasePWord = $dbPword;
            $this->databaseName = $dbName;
            $this->nameOfDbWithWorkers = $nameOfDbWithWorkers;
        }

        function setConnectionToDb(){
            $this->connection = mysql_connect($this->databaseURL,$this->databaseUName,$this->databasePWord) OR DIE("can't connect to DB"); 
            mysql_select_db($this->databaseName, $this->connection)or die ("Error while connecting to database");
        }

        function getUser($login, $pass){
            echo "$login, $pass";
            $query = "SELECT type FROM $this->nameOfDbWithWorkers WHERE login = '$login' and password = '$pass';"; 
            $queryResult = $this->getDataFromDbByQuery($query);
            if ((mysql_affected_rows($this->connection) == 1)){
                $meta = mysql_fetch_assoc($queryResult);
                if ($meta['type']=='admin'){
                    return 'admin';
                }
                if ($meta['type']=='user'){
                    return 'user';
                }
                else{
                    return 'nomatch';
                }
            }
            else{               
                 return 'nomatch';
            }           
        }

        function getDataFromDbByQuery($query){
            $this->setConnectionToDb();
            $result = mysql_query($query);
            mysql_close($this->connection);
            return $result;
        }
}
?>

and invoke this by

$dbHelp = new DbHelper($dbURL, $dbUName, $dbPword, $dbName, $nameOfDbWithWorkers);
$userType = $dbHelp->getUser($login, $pass);

And have a next error:

Warning: mysql_affected_rows(): 5 is not a valid MySQL-Link resource in Z:\home\ecl.ru\www\classes\dbhelper.php on line 27

What's wrong?

Improve this question
1
  • 1
    You shouldn't open and close the database connection for each query to execute. You can execute mysql_close() at the end of your script instead. Commented Jun 8, 2011 at 9:44

4 Answers 4

Reset to default
9

You shutdown the connection

    function getDataFromDbByQuery($query){
        $this->setConnectionToDb();
        $result = mysql_query($query);
        mysql_close($this->connection); // <----
        return $result;
    }

If you want to use it later in your script you shouldn't do this ;) However, because PHP closes all open itself at the scripts end you don't need to close it yourself at all.

Update (Just to avoid the next question ;))

You probably want to use mysql_num_rows() instead of mysql_affected_rows(). First one returns the number of rows returned by a SELECT-query, the other one returns the number of rows affected (;)) by a DELETE, INSERT, or UPDATE query. Oh, and REPLACE I forgot.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

he uses mysql_affected_rows between connect and close
@SergeS no he's not, he's using it after closing the connection.
@SergeS: No. As you can see he call getDataFromDbByQuery() before he calls mysql_affected_rows()
somehow i readed this badly ... sry
@KingCrunch to improve your answer, you should add an explanation regarding how it is useless to use mysql_affected_rows() for a SELECT query, because it will still not work after fixing the mysql_close() problem.
|
2

mysql_affected_rows

Get the number of affected rows by the last INSERT, UPDATE, REPLACE or DELETE

and you are using SELECT..

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

hope that solves your problem. you do need to change bit of code to handle this.

Improve this answer

3 Comments

That is not the immediate problem of the OP, but will be after he fixes the mysql_close() problem.
Mattieu why not he will never get mysql_affected_rows($this->connection) == 1 using select statement .
I agree with you, I was pointing out that even if he did what you are suggesting, that wouldn't fix the problem he's having now.
1

first , use mysql_num_rows for row count , mysql_affected_rows returns number of changed rows , as is written in manual on php.net

Get the number of affected rows by the last INSERT, UPDATE, REPLACE or DELETE query associated with link_identifier.

second - evry time you are using mysql function , put there link identifier or result identifier

Improve this answer

4 Comments

"second" is not required. It will use the last opened then.
i know , but it is good practice - i know lot of people not doing this and doing two listings ( which produce an error )
Its cleaner, you are right, but especially when using multiple connections, I would suggest using PDO or MySQLi anyway ([/offtopic] ;)).
Yes, it is better, but it is offtopic now :-)
0

This is the signature for mysql_affected_rows():

int mysql_affected_rows ([ resource $link_identifier ] )

Get the number of affected rows by the last INSERT, UPDATE, REPLACE or DELETE query associated with link_identifier.

link_identifier The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect() is assumed. If no such link is found, it will try to create one as if mysql_connect() was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.

So it requires an open link to the DB. However, your getDataFromDbByQuery() method closes the connection before you call the function:

    function getDataFromDbByQuery($query){
        $this->setConnectionToDb();
        $result = mysql_query($query);
        mysql_close($this->connection);
        return $result;
    }
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.