CODEWITHSUNDEEP
mysqlsqlcodeigniter
user3002114
user3002114

Reputation:

return single value of first row when query return more than one row

Following table is my database tabel question.

enter image description here

when I pass $ID=2 using this function,

function getFirstID($ID) {

        $checkQ = "SELECT * FROM tabel WHERE quiz_id = '$ID";
        $query = $this->db->query($checkQ);

        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return NULL;
        }            
}

the result will be,

enter image description here

I want to return ID of first row in result. so the output should be 4.

What is the modification that I should do in my function to get the expected output?

Upvotes: 2

Views: 3482

Answers (3)

Kasun Gamage
Kasun Gamage

Reputation: 346

You can use ROWNUM,LIMIT,TOP,PERCENT with this case,

1.) Deal with row numbers:

SELECT columnNames FROM Table_name
WHERE ROWNUM <= number;

2.) Can limit rows:

SELECT columnNames FROM Table_name LIMIT number;

3.) can select number of rows that you want from TOP

SELECT TOP 5 * FROM Table_name;

4.) can select percentage from all number of result from this:

SELECT  TOP 25 PERCENT * FROM Table_name;

4th one is not better when compare with others,but we can use it.

Regarding to your case,we can assume these four reasons like this :

SELECT * FROM tabel WHERE quiz_id = '$ID AND ROWNUM <= 1;

or

SELECT * FROM tabel WHERE quiz_id = '$ID AND LIMIT 1;

or

SELECT TOP 1 * FROM tabel WHERE quiz_id = '$ID;

or

SELECT 50 PERCENT * FROM tabel WHERE quiz_id = '$ID;

Upvotes: 3

Arvind Jaiswal
Arvind Jaiswal

Reputation: 442

In CodeIgniter

 $this->db->select('ID');
 $this->db->from('tabel');
 $this->db->where('quiz_id',$ID);
 $this->db->order_by('ID');
 $q=$this->db->get();
 if ($q->num_rows() > 0) {
        return $q->row();   # Returns single row
    } else {
        return NULL;
    }

Try this query

Upvotes: 2

Subin Chalil
Subin Chalil

Reputation: 3660

SELECT `ID` FROM tabel WHERE quiz_id = '$ID ORDER BY `ID` LIMIT 1

Please try this query.

Upvotes: 3

Related Questions