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

Hello

I'm going to check. By the way  how to use this

$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);

Select all Open in new window

Having an array as result ?
(since an array is needed to the subsequent the code)

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

Thank you for the replies!

I'm out of office now and will check asap.

Exactly!

Thank you very much!

(I have just another doubt to be posted in another question.)

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.