Unit testing functions that take data from database

[McLaren]

Hello, today I am experimenting with unit testing functions, on codeigniter, but that does not matter, the same question is for everything in PHP, or maybe not only in PHP, but general with functions that connect to database.

So lets say I have such function:

public function turnview_by_franchise_id($franchises_id, $clients_id, $date_from, $date_to, $shops_id) {
        $shops_param = "";
        if (!empty($shops_id)) {
            $shops_param = " AND shops.id = '" . $shops_id . "' ";
        }

        $sql = "blah blah blah sql";

        $result = $this->db->query($sql, array($franchises_id, $clients_id, $date_from, $date_to));
        return $result->result_array();
    }

Ok this function returns two dimentional array, or return empty array if there is no resutls. How can I test it? I could find parameters in my local database which would return something, the array would not be empty. But database data changes, so it easily can become empty. So I will not be able to test, unless I will update testing function everytime data in database changes. Also this will not work on another computer, for example my colegues, because he will have different data in database.

Or I should just test if returned data is_array and thats it?

Also another question - here as you can see the $sql itself is nonsene, so it would throw sql errors right to the screen, and I would not get any report - like passed or failed. I will know it has failed, but are unit tests made that way? Aren't they meant to return - fail pass, instead of actuall errros?

[pritaeas]

My personal opinion would be to test against a second database. Before testing restore it, test your code, check the changes.

[McLaren]

I googled and found things like this:

http://tutorials.jenkov.com/java-unit-testing/database-testing-crud.html

makes sense. Just insert, update, read and then I will now what is inserted, so it has also to return the right array.

[McLaren]

After writing mcuh testing functions, I see that it takes ton of time to make insert, test the values, rollback, etc. So now I just test for return if its true or false after transactions. I am not sure is it worth to test so in detail.

Are there some people who have much more experience with unit testing?

Or here are other function for example:

/**
     * @author 
     * @since version 1.2
     */
    protected function test_get_match_data() {

        $function_res = $this->Events_model->get_match_data(3201);

        $test_result = $this->unit->array_has_indexes(array('tournaments_id', 'br_match_id', 'home_team', 'away_team', 'score'), $function_res);

        $expected_result = TRUE;
        $test_name = __FUNCTION__ ;
        $this->unit->run($test_result, $expected_result, $test_name);

    }

and as you can see I have made a function to test fi array indices are correct after getting data. But there will be such array only if function get_match_data(3201) will return that array. But most often it will return empty array.

So for me it makes not too much sense to use such testing funciton, since mathc with id 3201 will rarely exist (only once in the runtime, unless testing database will be deleted and data collected from scrach)

Maybe I should jusst test if the result is_array and thats it? Because I think I am wasting time testing those indices.