Mysql get, where, order by and limit in one.

[eluser]R. Oerlemans[/eluser]
Hello Everyone,

I am working on a new application, this is the first week I use CI so this is my first (and hope last) problem.

When I use the code like:

controllers/artists.php

Code:

$songsQuery = $this->db->get('songs'); // Songs query
$songsQuery = $this->db->where('artist', $getMysql->id); // Where
$songsQuery = $this->db->order_by("hits"); // Order by
$songsQuery = $this->db->limit(15); // Limit, 15 entries

views/blocks/songs

Code:

<h1>&lt;?= $Heading; ?&gt;</h1>

<ul>
    &lt;? foreach ( $songs->result_array() as $Row ) : ?&gt;
    
    <li>&lt;?= anchor('videos-lyrics/view/'.$Row->id.'/'.$artistRow->name.'/'.$Row->title, 'My News'); ?&gt; <small>&lt;?= $Row->hits;?&gt; x bekeken</small></li>
    
    &lt;? endforeach; ?&gt;
</ul>

I get this error:

Fatal error: Call to undefined method CI_DB_mysql_driver::result_array() in ****/songscript/application/views/blocks/songs.php on line 4

I've searching with Google but I couldn't find the solution.

Can anybody help me?

Thanks a lot!

Kind Regards,
Ronny0

Edit: I see I'm in the wrong category/forum, sorry for that.

[eluser]BnoL[/eluser]
Hi R. Oerlemans,

Firstly, I think you should not fetch your data in views; do it in your models and return the result as an array, and then assign the array to your views.

[eluser]R. Oerlemans[/eluser]
[quote author="BnoL" date="1246160258"]Hi R. Oerlemans,

Firstly, I think you should not fetch your data in views; do it in your models and return the result as an array, and then assign the array to your views. [/quote]

Thanks for the tip, i've solved that.

Code:

/* Data for song block */            
    $songsQuery = $this->db->get('songs'); // Songs query
    $songsQuery = $this->db->where('artist', $getMysql->id); // Where
    $songsQuery = $this->db->order_by('hits'); // Order by
    $songsQuery = $this->db->limit(15); // Limit, 15 entries
    $songsQuery = $this->db->result_array(); // Fetch All

Fatal error: Call to undefined method CI_DB_mysql_driver::result_array() in *****/application/controllers/artists.php on line 50

[eluser]BnoL[/eluser]
Have you loaded the database library?

[eluser]BnoL[/eluser]
I think your code should look like this:

Code:

$this->db->get('songs'); // Songs query
$this->db->where('artist', $getMysql->id); // Where
$this->db->order_by('hits'); // Order by
$this->db->limit(15); // Limit, 15 entries
$songsQuery = $this->db->query();
$songsQuery->result_array();

[eluser]R. Oerlemans[/eluser]
[quote author="BnoL" date="1246161151"]I think your code should look like this:

Code:

$this->db->get('songs'); // Songs query
$this->db->where('artist', $getMysql->id); // Where
$this->db->order_by('hits'); // Order by
$this->db->limit(15); // Limit, 15 entries
$songsQuery = $this->db->query();
$songsQuery->result_array();

[/quote]

Thanks again, now I get an CI error in place of a fatal error.

A PHP Error was encountered

Severity: Warning

Message: Missing argument 1 for CI_DB_driver::query(), called in /home/vision043/domains/wpcoder.nl/public_html/songscript/application/controllers/artists.php on line 50 and defined

Filename: database/DB_driver.php

Line Number: 244
A PHP Error was encountered

Severity: Notice

Message: Undefined variable: sql

Filename: database/DB_driver.php

Line Number: 246
A PHP Error was encountered

Severity: Notice

Message: Undefined variable: sql

Filename: database/DB_driver.php

Line Number: 250

De query die u hebt ingegeven is niet correct.

[eluser]BnoL[/eluser]
Try this,

Code:

$this->db->where('artist', $getMysql->id);
$this->db->order_by('hits');
$this->db->limit(15);
$songsQuery = $this->db->get('songs');
$songsQuery->result_array();

[eluser]R. Oerlemans[/eluser]
Thanks, this works! Thanks a lot!

[eluser]BnoL[/eluser]
No problem. I'm a newbie too . Just read the manual more carefully

[eluser]R. Oerlemans[/eluser]
English isn't my first language. So it's a little bit hard to me.