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

Hi Experts

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

img001

jquery code
<script type="text/javascript">
var baseurl = "http://www.codeagro.sp.gov.br/";

$( 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();
	           $.each(result, function(i, item)
	           {
		          $('#orgao').append("<option value='" + i + "'>" + item + "</option>");
	           });
            },
			error: function (result)
			{
				alert('Ocorreu algum erro.');
			}
		});
	}); 
 });
</script>

Open in new window


Controler Code

  //EF 2015 Dez01 atualização do combo
    public function get_orgao_items(){
        $idprograma = $this->input->post('idprograma');
        $orgao_items = $this->edital_model->get_orgao($idprograma);
        echo json_encode($orgao_items); 
    }

Open in new window





Model code
  function get_orgao($idprograma = 0)   
    { 
        
        $this->db->select('idorgao');
        $this->db->select('nome');
        $this->db->from('tb_orgao');
        $this->db->where('idprograma', $idprograma);
 
      // ------ Relevant line -------------------------
      $this->db->order_by('nome ');
     //----------------------------------------------------


       $query = $this->db->get();
        $result = $query->result();

//        $sql = "SELECT idorgao, nome FROM tb_orgao WHERE idprograma =".$idprograma. " ORDER BY nome";
//        $result = $this->db->query($sql);


        //array to store idorgao & nome
        $idorgao = array('-SELECT-');
        $nome = array('-SELECT-');


     for ($i = 0; $i < count($result); $i++)
        {
            array_push($idorgao, $result[$i]->idorgao);
            array_push($nome, $result[$i]->nome);
        }
        return $department_result = array_combine($idorgao, $nome);

    }
 

Open in new window


Thanks in advance!
Eduardo FuerteDeveloper and AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Loganathan NatarajanLAMP DeveloperCommented:
Can you modify like this and check?  Include "ASC"
//        $sql = "SELECT idorgao, nome FROM tb_orgao WHERE idprograma =".$idprograma. " ORDER BY nome ASC";

Open in new window

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

Open in new window

There is a space in your order by field Try this
$this->db->order_by('nome');

Open in new window

Eduardo FuerteDeveloper and AnalystAuthor Commented:
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);

Open in new window


Having an array as result ?
(since an array is needed to the subsequent the code)
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Julian HansenCommented:
What does it return?

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

i.e. at line 2
echo $sql 

Open in new window

Copy result and paste into SQL query tool - do you get more than one result?
Marco GasiFreelancerCommented:
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

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)
{

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

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.');
				}
			});
		});
	});

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
Eduardo FuerteDeveloper and AnalystAuthor Commented:
Thank you for the replies!

I'm out of office now and will check asap.
Chris StanyonWebDevCommented:
jQuery doesn't maintain the order of a list of objects - they have no inherent order, which is why you get that issue. In your model, retrieve an array, prepend the SELECT item. json_encode() that and pass it back to your AJAX success handler:

// Model
function get_orgao($idprograma = 0) {
	// Select your records
	$this->db
		->select('idorgao, nome')
		->from('tb_orgao')
		->order_by('nome')
		->where('idprograma', $idprograma);
		
	// Get the results 
	$results = $this->db->get()->result_array();

	// Add the SELECT item to the start of the array
	array_unshift($results, array('id' => '0', 'name' => 'SELECT'));

	return $results;
}

Open in new window


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

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eduardo FuerteDeveloper and AnalystAuthor Commented:
Exactly!

Thank you very much!

(I have just another doubt to be posted in another question.)
Chris StanyonWebDevCommented:
Good news.

Just post a link to your new question here and I'll try and take a look :)
Julian HansenCommented:
@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.
Chris StanyonWebDevCommented:
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 :)
Marco GasiFreelancerCommented:
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 );

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' ]);
}

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>');
			});
		},

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.
Chris StanyonWebDevCommented:
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.
Marco GasiFreelancerCommented:
Thank you Chris.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.