mysql_affected_rows() in php

Question

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?

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. — Matthieu Napoli, Jun 08 '11 at 09:44

KingCrunch · Answer 1 · 2011-06-08T09:52:30.330

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.

edited Jun 08 '11 at 09:52

answered Jun 08 '11 at 09:43

KingCrunch

128,817
21
151
173

he uses mysql_affected_rows between connect and close – SergeS Jun 08 '11 at 09:45
@SergeS no he's not, he's using it after closing the connection. – Matthieu Napoli Jun 08 '11 at 09:46
@SergeS: No. As you can see he call `getDataFromDbByQuery()` _before_ he calls `mysql_affected_rows()` – KingCrunch Jun 08 '11 at 09:47
somehow i readed this badly ... sry – SergeS Jun 08 '11 at 09:48
@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. – Matthieu Napoli Jun 08 '11 at 09:53
@Matthieu: Hehe, already did it ;) – KingCrunch Jun 08 '11 at 09:53

score 2 · Answer 2 · answered Jun 08 '11 at 09:46

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.

answered Jun 08 '11 at 09:46

Khurram Ijaz

1,844
5
24
43

That is not the immediate problem of the OP, but will be after he fixes the `mysql_close()` problem. – Matthieu Napoli Jun 08 '11 at 09:49
Mattieu why not he will never get mysql_affected_rows($this->connection) == 1 using select statement . – Khurram Ijaz Jun 08 '11 at 09:49
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. – Matthieu Napoli Jun 08 '11 at 09:51

score 1 · Accepted Answer · answered Jun 08 '11 at 09:43

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

answered Jun 08 '11 at 09:43

SergeS

11,533
3
29
35

"second" is not required. It will use the last opened then. – KingCrunch Jun 08 '11 at 09:45
1

i know , but it is good practice - i know lot of people not doing this and doing two listings ( which produce an error ) – SergeS Jun 08 '11 at 09:47
1

Its cleaner, you are right, but especially when using multiple connections, I would suggest using `PDO` or `MySQLi` anyway ([/offtopic] ;)). – KingCrunch Jun 08 '11 at 09:49
Yes, it is better, but it is offtopic now :-) – SergeS Jun 08 '11 at 09:50

score 0 · Answer 4 · answered Jun 08 '11 at 09:48

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;
    }

mysql_affected_rows() in php

4 Answers4