Could you check why this Codeigniter order by isn't recognised when filling a combobox ?

Can you modify like this and check?  Include "ASC"

//        $sql = "SELECT idorgao, nome FROM tb_orgao WHERE idprograma =".$idprograma. " ORDER BY nome ASC";

Select all Open in new window

$this->db->order_by('nome ');
                         ^   

Select all Open in new window

There is a space in your order by field Try this

$this->db->order_by('nome');

Select all Open in new window

What does it return?

What do you get if you run that query directly against the database?

i.e. at line 2

echo $sql 

Select all Open in new window

Copy result and paste into SQL query tool - do you get more than one result?

For the last question:

$sql = "SELECT idorgao, nome FROM tb_orgao WHERE idprograma =".$idprograma. " ORDER BY nome ASC";
// It doesn't return an array
$result = $this->db->query($sql);
return $result->result_array(); //this is the array

Select all Open in new window

For the main question; I had the same problem some day ago: the problem is not in sql query or in php but in the way jquery manages the data returned by php script. When you iterate using

$.each(result, function(i, item)
{

Select all Open in new window

jquery re-sorts result on i basis so the order set by 'order_by' clause is missing. I solved the problem changing the way I returned the array from php and then adjusting the jquery code:

    public function get_orgao_items(){
        $idprograma = $this->input->post('idprograma');
        $orgao_items = $this->edital_model->get_orgao($idprograma);
				foreach ($orgao_items[0] as $k=>$v)
				{
					$result[$k] = $v;
				}
        echo json_encode($result); 
    }

Select all Open in new window

jquery:

	$(document).ready(function () {
		$('#programa').on('change', function ()
		{
			var idprograma = $(this).val();
			//alert(idprograma);
			$.ajax({
				type: 'post',
				//url: baseurl + "edital/get_orgao_items",
				url: baseurl + "acesso_usuarios/get_orgao_items",
				data: {idprograma: idprograma},
				dataType: 'json',
				success: function (result)
				{
					$('#orgao').children().remove();
					var $orgao = [];
					$.each(result, function (i, item)
					{
						$orgao.push({ nome: item[0],id: item[1] });
					});
					$.each($orgao, function (i, item) {
						$('#orgao').append("<option value='" + item.id + "'>" + item.nome + "</option>");
					});
				},
				error: function (result)
				{
					alert('Ocorreu algum erro.');
				}
			});
		});
	});

Select all Open in new window

Probably, it wont work immediately because I tried to convert my code integrating it in CI, but perhaps this give you a base to build on a working code

Good news.

Just post a link to your new question here and I'll try and take a look :)

@Chris,

Trying to replicate the problem where you say

jQuery doesn't maintain the order of a list of objects - they have no inherent order

I cannot replicate this - when I query results via AJAX they are always represented in the order they are sent - and are displayed as such.

Did I misunderstand your post or is there something I am missing? Interested to know more on this.

Hey @Julian

Poor choice of words on my part :)

Probably better to have said 'jQuery doesn't maintain the order of an objects properties' - I'd been working on C# lists all day !!

In Eduardo's code, he pulled an ID and Name from a database, sorted by Name, and then built his data something like this:

{
   "-SELECT-" : "-SELECT-",
   "3" : "A String",
   "1" : "B String",
   "2" : "C String"
}

When he looped through in jQuery, he was obviously expecting:

-SELECT-
A String
B String
C String

What he got was:

B String
C String
A String
-SELECT-

That's the point I was trying to make - poorly I guess :)

I am interested me too :-)
I don't know if my posted code would work in the Eduardo's implementation: I tried to convert to CI framework from a simple app I'm deploying in html5, jquery, mysql to use with Cordova.
In my code I was querying mysql this way:

$sql = "SELECT * FROM categories ORDER BY sort_order ASC";
$result = $db->query( $sql );

Select all Open in new window

When I returned the result array to jquery to print out categories; i got the correct order using console.log but after using each loop the order was broken and the only way to preserve it has been to rearrange result array in php:

while ( $row = $result->fetch_assoc( ) )
{
	$cats[$row[ 'sort_order' ]] = array($row[ 'category_id' ],$row[ 'category_name' ]);
}

Select all Open in new window

And then modifying jquery script this way:

		success: function (result)
		{
			console.log('success');
			var prodItem = [];
			$.each(result, function (order, values) {
				prodItem.push({id: values[0], name: values[1]});
			});
			$.each(prodItem, function (i, item) {
				$('.sidebar-nav').append('<li><a class="cat-menu-item" data-cat="' + item.id + '" href="#">' + item.name + '</a></li>');
			});
		},

Select all Open in new window

This is what I tried to adapt to the CI context where the Eduardo's code is running and I confess I didn't test it and I'm not sure if my code was correct. But I'm wondering if there be a simpler way to get the correct result.

Marco - In CodeIgniter, retrieve your DB records as a result_array and JSON encode that. This will automatically create an array that will maintain the order it was pulled from your DB:

$result = $db->query( $sql );
echo json_encode( $result->result_array() );

This will create an indexed array:

array(
   [0] => array( ['id'] => 30 ['name'] => 'A String' )
   [1] => array( ['id'] => 10 ['name'] => 'B String' )
   [2] => array( ['id'] => 20 ['name'] => 'C String' )
   [3] => array( ['id'] => 40 ['name'] => 'D String' )
)

The order that Jquery will loop through is now defined by the auto-generated array key 0-3, so you order will stay like that.

Thank you Chris.